兩個半成品的ORM-有解無憂

只要是有點結構化的思想，不可能專案里一個sqlHelper 滿天飛到處寫，最終你的c#代碼還是得回傳一個Class 才好操作，sqlhelper， datatable這種東西也只是臨時將就一下，稍微先進一點的思想會用一種結構化的思想把資料訪問用面向物件的方式包裝成一個層，比如普創都把各個表名字欄位名字專門用Columbus類定義了，普創的資料訪問層確實是個糟糕的設計通過Columns 反而增加了復雜度，不過好炊訓有那么點意識在好歹定義了列名不會陳述句寫亂了分不清東南西北，當然這個東西看你怎么權衡，比如我以前一直都是一個sqlHelper 滿天飛，容我做個悲傷的表情，

分享兩個以前專案刀耕火種的ORM半成品

一個是08年的時候記得是一個李遠志的朋友推的，不知他是哪里抄的還是自創的，當時心智沒這么成熟沒考慮到什么
面向物件設計和通用，現在看到現公司的資料庫訪問設計感覺好像天下思想殊途同歸，當時08年.net3.5都還剛推出好多都是以前那種晦澀的C++開發方式，EntityFramework也還沒推出好多都還沒有結構和面向物件這個概念在腦子里泛型都還少有人用，這在當時感徑訓是一種表面上蠻新進的一種結構設計方式，至少表面上充分的利用到了面向物件和繼承，以及泛型這些特性，這么多年我一直到今天才把翻出來看，

第一個（08年的）：

開始當然是物體的定義

 1 public class Clazz
 2 {
 3     private long classId;
 4 
 5     public long ClassId
 6     {
 7         get { return classId; }
 8         set { classId = value; }
 9     }
10 
11     private string className;
12 
13     public string ClassName
14     {
15         get { return className; }
16         set { className = value; }
17     }
18 }

接著自然是DAL層，巧妙的利用了繼承兩個介面的特性，一個介面封裝了sqlhelper實作另外一個介面定義了相關資料訪問有哪些通用方法

SQL helper封裝：

  1 internal abstract class AbstractDAL
  2 {
  3     private IDbConnection con;
  4 
  5     private IDbTransaction tran;
  6 
  7     #region 構造方法
  8 
  9     protected AbstractDAL()
 10     {
 11         this.con = ADOHlper.CreateIDbConnection();
 12     }
 13 
 14     protected AbstractDAL(IDbConnection con)
 15     {
 16         if ((this.con = con) == null)
 17             this.con = ADOHlper.CreateIDbConnection();
 18     }
 19 
 20     protected AbstractDAL(IDbTransaction tran)
 21     {
 22         if ((this.tran = tran) == null)
 23         {
 24             this.con = ADOHlper.CreateIDbConnection();
 25         }
 26         else
 27         {
 28             this.con = this.tran.Connection;
 29             if (this.con == null || this.con.State != ConnectionState.Open)
 30                 throw new ArgumentException("非法的事務引數，其連接必須存在且處于被打開狀態");
 31         }
 32     }
 33 
 34     #endregion
 35 
 36     #region 創建 SQL 命令
 37 
 38     protected IDbCommand CreateIDbCommand(string commandText, CommandType commandType)
 39     {
 40         IDbCommand cmd = this.con.CreateCommand();
 41         cmd.Transaction = this.tran;
 42         cmd.CommandText = commandText;
 43         cmd.CommandType = commandType;
 44         return cmd;
 45     }
 46 
 47     protected IDbCommand CreateIDbCommand(string commandText)
 48     { return CreateIDbCommand(commandText, CommandType.Text); }
 49 
 50     protected IDbCommand CreateIDbCommand(CommandType commandType)
 51     { return CreateIDbCommand(null, commandType); }
 52 
 53     protected IDbCommand CreateIDbCommand()
 54     { return CreateIDbCommand(null, CommandType.Text); }
 55 
 56     #endregion
 57 
 58     #region 執行委托
 59 
 60     protected T Execute<T>(ExecuteHandler<T> handler)
 61     {
 62         if (handler == null)
 63             throw new ArgumentNullException("handler<T>引數不能為空");
 64 
 65         if (this.tran != null && this.con.State != ConnectionState.Open)
 66             throw new InvalidOperationException("非法操作，當前存在事務，但其連接不處于被打開狀態");
 67         if (this.con.State == ConnectionState.Open)
 68         {
 69             return handler();
 70         }
 71         else
 72         {
 73             this.con.Open();
 74             try
 75             {
 76                 return handler();
 77             }
 78             finally
 79             {
 80                 this.con.Close();
 81             }
 82         }
 83     }
 84 
 85     protected void Execute(ExecuteHandler handler)
 86     {
 87         if (handler == null)
 88             throw new ArgumentNullException("handler引數不能為空");
 89 
 90         if (this.tran != null && this.con.State != ConnectionState.Open)
 91             throw new InvalidOperationException("非法操作，當前存在事務，但其連接不處于被打開狀態");
 92 
 93         if (this.con.State == ConnectionState.Open)
 94         {
 95             handler();
 96         }
 97         else
 98         {
 99             this.con.Open();
100             try
101             {
102                 handler();
103             }
104             finally
105             {
106                 this.con.Close();
107             }
108         }
109     }
110 
111     #endregion
112 }

sqlhelper：

 1 public static class ADOHlper
 2 {
 3     private const string CONFING_KEY = "DBconnection";
 4 
 5     private static string connectionString;
 6 
 7     static ADOHlper()
 8     {
 9         connectionString = WebConfigurationManager.ConnectionStrings[CONFING_KEY].ConnectionString;
10         if (connectionString == null)
11             throw new InvalidOperationException("從組態檔讀取連接字串例外");
12     }
13 
14     //創建連接
15     public static IDbConnection CreateIDbConnection()
16     { return new SqlConnection(connectionString); }
17 
18     //創建資料配接器
19     public static IDbDataAdapter CreateIDbDataAdapter()
20     { return new SqlDataAdapter(); }
21 
22     #region 添加引數方法
23 
24     public static void AddInPrameter(IDbCommand cmd, string prameterName, DbType dbType, int size, object value)
25     {
26         IDbDataParameter parameter = cmd.CreateParameter();
27         parameter.ParameterName = prameterName;
28         parameter.DbType = dbType;
29         parameter.Size = size;
30         parameter.Value = https://www.cnblogs.com/assassinx/archive/2021/01/04/value != null ? value : DBNull.Value;
31         cmd.Parameters.Add(parameter);
32     }
33 
34     public static void AddInPrameter(IDbCommand cmd, string prameterName, DbType dbType, object value)
35     {
36         AddInPrameter(cmd, prameterName, dbType, 0, value);
37     }
38 
39     #endregion
40 }

特定類的資料訪問定義：

1 public interface IClassDAL
2 {
3     DataSet GetClasses();
4     void SaveClass(Clazz clazz);
5     void UpdateClass(Clazz clazz);
6     void DeleteClass(long classId);
7 }

最后的主角通過介面泛化到最終的資料訪問實作，運用泛型委托讓底層去執行資料操作

 1 internal class ClassDALImpl : AbstractDAL, IClassDAL
 2 {
 3     public ClassDALImpl() { }
 4     public ClassDALImpl(IDbConnection con) : base(con) { }
 5     public ClassDALImpl(IDbTransaction tran) : base(tran) { }
 6     public DataSet GetClasses()
 7     {
 8         ExecuteHandler<DataSet> handler =
 9             delegate
10             {
11                 IDbCommand cmd = this.CreateIDbCommand("SELECT * FROM Class");
12                 IDbDataAdapter dapter = ADOHlper.CreateIDbDataAdapter();
13                 dapter.SelectCommand = cmd;
14                 dapter.TableMappings.Add("Table", "Class");
15                 DataSet dataSet = new DataSet();
16                 dapter.Fill(dataSet);
17                 return dataSet;
18             };
19         return this.Execute(handler);
20     }
21 
22     public void SaveClass(Clazz clazz)
23     {
24         ExecuteHandler handler =
25             delegate
26             {
27                 IDbCommand cmd = this.CreateIDbCommand("INSERT INTO Class VALUES(@ClassName)");
28                 ADOHlper.AddInPrameter(cmd, "@ClassName", DbType.AnsiString, 50, clazz.ClassName);
29                 cmd.ExecuteNonQuery();
30 
31                 IDbCommand ideCmd = this.CreateIDbCommand("SELECT @@IDENTITY");
32                 clazz.ClassId = (int)ideCmd.ExecuteScalar();
33             };
34         this.Execute(handler);
35     }
36 
37     public void UpdateClass(Clazz clazz)
38     {
39         ExecuteHandler handler =
40            delegate
41            {
42                IDbCommand cmd = this.CreateIDbCommand("UPDATE Class SET ClassName = @ClassName");
43                ADOHlper.AddInPrameter(cmd, "@ClassName", DbType.AnsiString, 50, clazz.ClassName);
44                cmd.ExecuteNonQuery();
45            };
46         this.Execute(handler);
47     }
48 
49     public void DeleteClass(long classId)
50     {
51         ExecuteHandler handler =
52            delegate
53            {
54                IDbCommand cmd = this.CreateIDbCommand("DELETE Class WHERE ClassId = @ClassId");
55                ADOHlper.AddInPrameter(cmd, "@ClassId", DbType.Int64, classId);
56                cmd.ExecuteNonQuery();
57            };
58         this.Execute(handler);
59     }
60 }

最終通過工廠模式統一給出實體

1 public static class FactoryDAL
2 {
3     public static IClassDAL CreateClassDAL()
4     { return new test.DAL.Impl.ClassDALImpl(); }
5 }

但是最侄訓是讓各種資料操作溢位到了最終實作，沒有良好的利用繼承實作高內聚，跟用SQLhelper差別不大，所以算不得一個好的實作，

第二個（應該是大約2017年的）：

這種才是稍微靠譜的方式：

首先是列定義也可理解為物體定義

 1 public class Ht_autoprint_Column
 2 {
 3     public static string HColName_ID = "ID";
 4     public static string HColName_CardNo = "CardNo";
 5     protected string _tableName = "t_autoprint";
 6     private string _id;
 7     private string _cardno;
 8     public string ID
 9     {
10         get
11         {
12             return _id;
13         }
14         set
15         {
16             _id = value;
17         }
18     }
19 
20     public string CardNo
21     {
22         get
23         {
24             return _cardno;
25         }
26         set
27         {
28             _cardno = value;
29         }
30     }
31 }

主要的機關是利用了 BaseTableDB的類，利用反射列屬性完成增刪改查，可以理解為一種靈活的sqlhelper：

  1 public class baseTableDB<T> where T : new()
  2 {
  3     private string _connString;
  4 
  5     private string _tableName;
  6 
  7     private Exception _errorInfo;
  8 
  9     public Exception ErrorInfo => _errorInfo;
 10 
 11     public bool Init(string connString, string tbleName)
 12     {
 13         _connString = connString;
 14         _tableName = tbleName;
 15         return true;
 16     }
 17 
 18     public bool Init(string ip, string port, string datebase, string user, string pwd)
 19     {
 20         try
 21         {
 22             _connString = $"Server={ip};Port={port};Database={datebase}; User={user};Password={pwd};";
 23             return true;
 24         }
 25         catch (Exception errorInfo)
 26         {
 27             Exception ex = _errorInfo = errorInfo;
 28             return false;
 29         }
 30     }
 31 
 32     private object GetValue(object o)
 33     {
 34         if (o.GetType() == typeof(char))
 35         {
 36             return Convert.ToChar(o);
 37         }
 38         if (o.GetType() == typeof(int))
 39         {
 40             return Convert.ToInt32(o);
 41         }
 42         if (o.GetType() == typeof(double))
 43         {
 44             return Convert.ToDouble(o);
 45         }
 46         if (o.GetType() == typeof(float))
 47         {
 48             return Convert.ToSingle(o);
 49         }
 50         if (o.GetType() == typeof(DateTime))
 51         {
 52             return Convert.ToDateTime(o);
 53         }
 54         if (o.GetType() == typeof(decimal))
 55         {
 56             return Convert.ToDecimal(o);
 57         }
 58         return o.ToString();
 59     }
 60 
 61     private string GetValue(Type type, object o)
 62     {
 63         try
 64         {
 65             if (type == typeof(int) || type == typeof(double) || type == typeof(float) || 
 66 type == typeof(decimal) || type == typeof(int?) || type == typeof(double?) || 
 67 type == typeof(float?) || type == typeof(decimal?))
 68             {
 69                 return o.ToString();
 70             }
 71             return "'" + o.ToString() + "'";
 72         }
 73         catch
 74         {
 75             return "null";
 76         }
 77     }
 78 
 79     public IList<T> baseSelect(string sql)
 80     {
 81         IList<T> htAutoprintColumnList = new List<T>();
 82         MySqlConnection conn = new MySqlConnection(_connString);
 83         try
 84         {
 85             conn.Open();
 86             MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(new MySqlCommand(sql, conn));
 87             DataTable dataTable = new DataTable();
 88             mySqlDataAdapter.Fill(dataTable);
 89             foreach (DataRow row in dataTable.Rows)
 90             {
 91                 T col = new T();
 92                 PropertyInfo[] properties = col.GetType().GetProperties();
 93                 foreach (PropertyInfo p in properties)
 94                 {
 95                     if (dataTable.Columns.Contains(p.Name) && row[p.Name] != DBNull.Value)
 96                     {
 97                         p.SetValue(col, GetValue(row[p.Name]), null);
 98                     }
 99                 }
100                 htAutoprintColumnList.Add(col);
101             }
102             return htAutoprintColumnList;
103         }
104         catch (Exception errorInfo)
105         {
106             Exception ex = _errorInfo = errorInfo;
107             return htAutoprintColumnList;
108         }
109         finally
110         {
111             conn.Close();
112         }
113     }
114 
115     public IList<T> Select()
116     {
117         IList<T> htAutoprintColumnList = new List<T>();
118         MySqlConnection conn = new MySqlConnection(_connString);
119         try
120         {
121             conn.Open();
122             MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(new MySqlCommand($"select * from {_tableName}", conn));
123             DataTable dataTable = new DataTable();
124             mySqlDataAdapter.Fill(dataTable);
125             foreach (DataRow row in dataTable.Rows)
126             {
127                 DataRow row2 = row;
128                 T col = new T();
129                 col.GetType().GetProperties().ToList()
130                     .ForEach(delegate(PropertyInfo u)
131                     {
132                         u.SetValue(col, (row2[u.Name] == DBNull.Value) ? null : GetValue(row2[u.Name]), null);
133                     });
134                 htAutoprintColumnList.Add(col);
135             }
136             return htAutoprintColumnList;
137         }
138         catch (Exception errorInfo)
139         {
140             Exception ex = _errorInfo = errorInfo;
141             return htAutoprintColumnList;
142         }
143         finally
144         {
145             conn.Close();
146         }
147     }
148 
149     public IList<T> Select(string where)
150     {
151         string sql = $"select * from {_tableName} where {where}";
152         return baseSelect(sql);
153     }
154 
155     public IList<T> Select(T where)
156     {
157         string sql = $"select * from {_tableName} where {GetWhere(where)}";
158         return baseSelect(sql);
159     }
160 
161     public bool InsertInto(T info)
162     {
163         MySqlConnection conn = new MySqlConnection(_connString);
164         try
165         {
166             conn.Open();
167             string sqlColName = "";
168             string sqlColValues = "";
169             int i = 0;
170             info.GetType().GetProperties().ToList()
171                 .ForEach(delegate(PropertyInfo u)
172                 {
173                     if (1 == i)
174                     {
175                         sqlColName += ", ";
176                         sqlColValues += ", ";
177                     }
178                     sqlColName += u.Name;
179                     sqlColValues += GetValue(u.PropertyType, u.GetValue(info, null));
180                     i = 1;
181                 });
182             new MySqlCommand($"insert into {_tableName}({sqlColName}) values({sqlColValues})", conn).ExecuteNonQuery();
183             return true;
184         }
185         catch (Exception errorInfo)
186         {
187             Exception ex = _errorInfo = errorInfo;
188             return false;
189         }
190         finally
191         {
192             conn.Close();
193         }
194     }
195 
196     public bool Update(T set, string where)
197     {
198         MySqlConnection conn = new MySqlConnection(_connString);
199         try
200         {
201             conn.Open();
202             string sqlSet = "";
203             int i = 0;
204             set.GetType().GetProperties().ToList()
205                 .ForEach(delegate(PropertyInfo u)
206                 {
207                     if (u.GetValue(set, null) != null)
208                     {
209                         if (1 == i)
210                         {
211                             sqlSet += ", ";
212                         }
213                         sqlSet = sqlSet + u.Name + "=" + GetValue(u.PropertyType, u.GetValue(set, null));
214                         i = 1;
215                     }
216                 });
217             return new MySqlCommand($"Update {_tableName} set {sqlSet} where {where}", conn).ExecuteNonQuery() != 0;
218         }
219         catch (Exception errorInfo)
220         {
221             Exception ex = _errorInfo = errorInfo;
222             return false;
223         }
224         finally
225         {
226             conn.Close();
227         }
228     }
229 
230     public bool Update(string set, string where)
231     {
232         MySqlConnection conn = new MySqlConnection(_connString);
233         try
234         {
235             conn.Open();
236             return new MySqlCommand($"Update {_tableName} set {set} where {where}", conn).ExecuteNonQuery() != 0;
237         }
238         catch (Exception errorInfo)
239         {
240             throw _errorInfo = errorInfo;
241         }
242         finally
243         {
244             conn.Close();
245         }
246     }
247 
248     public bool baseUpdate(string sql)
249     {
250         MySqlConnection conn = new MySqlConnection(_connString);
251         try
252         {
253             conn.Open();
254             return new MySqlCommand(sql, conn).ExecuteNonQuery() != 0;
255         }
256         catch (Exception errorInfo)
257         {
258             throw new Exception($"sql:{sql}, ex:{(_errorInfo = errorInfo).ToString()}");
259         }
260         finally
261         {
262             conn.Close();
263         }
264     }
265 
266     public bool Update(string set, T where)
267     {
268         string sql = $"Update {_tableName} set {set} where {GetWhere(where)}";
269         return baseUpdate(sql);
270     }
271 
272     public bool Update(T set, T where)
273     {
274         string sqlSet = "";
275         int i = 0;
276         set.GetType().GetProperties().ToList()
277             .ForEach(delegate(PropertyInfo u)
278             {
279                 if (u.GetValue(set, null) != null)
280                 {
281                     if (1 == i)
282                     {
283                         sqlSet += ", ";
284                     }
285                     sqlSet = sqlSet + u.Name + "=" + GetValue(u.PropertyType, u.GetValue(set, null));
286                     i = 1;
287                 }
288             });
289         string sql = $"Update {_tableName} set {sqlSet} where {GetWhere(where)}";
290         return baseUpdate(sql);
291     }
292 
293     public bool Delete(T where)
294     {
295         string sql = $"delete from {_tableName} where {GetWhere(where)}";
296         return baseUpdate(sql);
297     }
298 
299     public bool Delete(string where)
300     {
301         string sql = $"delete from {_tableName} where {where}";
302         return baseUpdate(sql);
303     }
304 
305     public DataTable ExecuteQuery(string sql)
306     {
307         new List<T>();
308         MySqlConnection conn = new MySqlConnection(_connString);
309         try
310         {
311             conn.Open();
312             MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(new MySqlCommand($"select * from {_tableName}", conn));
313             DataTable dataTable = new DataTable();
314             mySqlDataAdapter.Fill(dataTable);
315             return dataTable;
316         }
317         catch (Exception errorInfo)
318         {
319             Exception ex = _errorInfo = errorInfo;
320             return null;
321         }
322         finally
323         {
324             conn.Close();
325         }
326     }
327 
328     public bool ExecuteNonQuery(string sql)
329     {
330         return baseUpdate(sql);
331     }
332 
333     private string GetWhere(T where)
334     {
335         string sqlWhere = "";
336         int i = 0;
337         where.GetType().GetProperties().ToList()
338             .ForEach(delegate(PropertyInfo u)
339             {
340                 if (u.GetValue(where, null) != null)
341                 {
342                     if (1 == i)
343                     {
344                         sqlWhere += " and ";
345                     }
346                     sqlWhere = sqlWhere + u.Name + "=" + GetValue(u.PropertyType, u.GetValue(where, null));
347                     i = 1;
348                 }
349             });
350         return sqlWhere;
351     }
352 }

最后使用繼承物體屬性配合sqlhelper的方式完成增刪改查

  1 public class baseTableDB<T> where T : new()
  2 {
  3     private string _connString;
  4 
  5     private string _tableName;
  6 
  7     private Exception _errorInfo;
  8 
  9     public Exception ErrorInfo => _errorInfo;
 10 
 11     public bool Init(string connString, string tbleName)
 12     {
 13         _connString = connString;
 14         _tableName = tbleName;
 15         return true;
 16     }
 17 
 18     public bool Init(string ip, string port, string datebase, string user, string pwd)
 19     {
 20         try
 21         {
 22             _connString = $"Server={ip};Port={port};Database={datebase}; User={user};Password={pwd};";
 23             return true;
 24         }
 25         catch (Exception errorInfo)
 26         {
 27             Exception ex = _errorInfo = errorInfo;
 28             return false;
 29         }
 30     }
 31 
 32     private object GetValue(object o)
 33     {
 34         if (o.GetType() == typeof(char))
 35         {
 36             return Convert.ToChar(o);
 37         }
 38         if (o.GetType() == typeof(int))
 39         {
 40             return Convert.ToInt32(o);
 41         }
 42         if (o.GetType() == typeof(double))
 43         {
 44             return Convert.ToDouble(o);
 45         }
 46         if (o.GetType() == typeof(float))
 47         {
 48             return Convert.ToSingle(o);
 49         }
 50         if (o.GetType() == typeof(DateTime))
 51         {
 52             return Convert.ToDateTime(o);
 53         }
 54         if (o.GetType() == typeof(decimal))
 55         {
 56             return Convert.ToDecimal(o);
 57         }
 58         return o.ToString();
 59     }
 60 
 61     private string GetValue(Type type, object o)
 62     {
 63         try
 64         {
 65             if (type == typeof(int) || type == typeof(double) || type == typeof(float) ||
 66  type == typeof(decimal) || type == typeof(int?) || type == typeof(double?) || 
 67 type == typeof(float?) || type == typeof(decimal?))
 68             {
 69                 return o.ToString();
 70             }
 71             return "'" + o.ToString() + "'";
 72         }
 73         catch
 74         {
 75             return "null";
 76         }
 77     }
 78 
 79     public IList<T> baseSelect(string sql)
 80     {
 81         IList<T> htAutoprintColumnList = new List<T>();
 82         MySqlConnection conn = new MySqlConnection(_connString);
 83         try
 84         {
 85             conn.Open();
 86             MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(new MySqlCommand(sql, conn));
 87             DataTable dataTable = new DataTable();
 88             mySqlDataAdapter.Fill(dataTable);
 89             foreach (DataRow row in dataTable.Rows)
 90             {
 91                 T col = new T();
 92                 PropertyInfo[] properties = col.GetType().GetProperties();
 93                 foreach (PropertyInfo p in properties)
 94                 {
 95                     if (dataTable.Columns.Contains(p.Name) && row[p.Name] != DBNull.Value)
 96                     {
 97                         p.SetValue(col, GetValue(row[p.Name]), null);
 98                     }
 99                 }
100                 htAutoprintColumnList.Add(col);
101             }
102             return htAutoprintColumnList;
103         }
104         catch (Exception errorInfo)
105         {
106             Exception ex = _errorInfo = errorInfo;
107             return htAutoprintColumnList;
108         }
109         finally
110         {
111             conn.Close();
112         }
113     }
114 
115     public IList<T> Select()
116     {
117         IList<T> htAutoprintColumnList = new List<T>();
118         MySqlConnection conn = new MySqlConnection(_connString);
119         try
120         {
121             conn.Open();
122             MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(new MySqlCommand($"select * from {_tableName}", conn));
123             DataTable dataTable = new DataTable();
124             mySqlDataAdapter.Fill(dataTable);
125             foreach (DataRow row in dataTable.Rows)
126             {
127                 DataRow row2 = row;
128                 T col = new T();
129                 col.GetType().GetProperties().ToList()
130                     .ForEach(delegate(PropertyInfo u)
131                     {
132                         u.SetValue(col, (row2[u.Name] == DBNull.Value) ? null : GetValue(row2[u.Name]), null);
133                     });
134                 htAutoprintColumnList.Add(col);
135             }
136             return htAutoprintColumnList;
137         }
138         catch (Exception errorInfo)
139         {
140             Exception ex = _errorInfo = errorInfo;
141             return htAutoprintColumnList;
142         }
143         finally
144         {
145             conn.Close();
146         }
147     }
148 
149     public IList<T> Select(string where)
150     {
151         string sql = $"select * from {_tableName} where {where}";
152         return baseSelect(sql);
153     }
154 
155     public IList<T> Select(T where)
156     {
157         string sql = $"select * from {_tableName} where {GetWhere(where)}";
158         return baseSelect(sql);
159     }
160 
161     public bool InsertInto(T info)
162     {
163         MySqlConnection conn = new MySqlConnection(_connString);
164         try
165         {
166             conn.Open();
167             string sqlColName = "";
168             string sqlColValues = "";
169             int i = 0;
170             info.GetType().GetProperties().ToList()
171                 .ForEach(delegate(PropertyInfo u)
172                 {
173                     if (1 == i)
174                     {
175                         sqlColName += ", ";
176                         sqlColValues += ", ";
177                     }
178                     sqlColName += u.Name;
179                     sqlColValues += GetValue(u.PropertyType, u.GetValue(info, null));
180                     i = 1;
181                 });
182             new MySqlCommand($"insert into {_tableName}({sqlColName}) values({sqlColValues})", conn).ExecuteNonQuery();
183             return true;
184         }
185         catch (Exception errorInfo)
186         {
187             Exception ex = _errorInfo = errorInfo;
188             return false;
189         }
190         finally
191         {
192             conn.Close();
193         }
194     }
195 
196     public bool Update(T set, string where)
197     {
198         MySqlConnection conn = new MySqlConnection(_connString);
199         try
200         {
201             conn.Open();
202             string sqlSet = "";
203             int i = 0;
204             set.GetType().GetProperties().ToList()
205                 .ForEach(delegate(PropertyInfo u)
206                 {
207                     if (u.GetValue(set, null) != null)
208                     {
209                         if (1 == i)
210                         {
211                             sqlSet += ", ";
212                         }
213                         sqlSet = sqlSet + u.Name + "=" + GetValue(u.PropertyType, u.GetValue(set, null));
214                         i = 1;
215                     }
216                 });
217             return new MySqlCommand($"Update {_tableName} set {sqlSet} where {where}", conn).ExecuteNonQuery() != 0;
218         }
219         catch (Exception errorInfo)
220         {
221             Exception ex = _errorInfo = errorInfo;
222             return false;
223         }
224         finally
225         {
226             conn.Close();
227         }
228     }
229 
230     public bool Update(string set, string where)
231     {
232         MySqlConnection conn = new MySqlConnection(_connString);
233         try
234         {
235             conn.Open();
236             return new MySqlCommand($"Update {_tableName} set {set} where {where}", conn).ExecuteNonQuery() != 0;
237         }
238         catch (Exception errorInfo)
239         {
240             throw _errorInfo = errorInfo;
241         }
242         finally
243         {
244             conn.Close();
245         }
246     }
247 
248     public bool baseUpdate(string sql)
249     {
250         MySqlConnection conn = new MySqlConnection(_connString);
251         try
252         {
253             conn.Open();
254             return new MySqlCommand(sql, conn).ExecuteNonQuery() != 0;
255         }
256         catch (Exception errorInfo)
257         {
258             throw new Exception($"sql:{sql}, ex:{(_errorInfo = errorInfo).ToString()}");
259         }
260         finally
261         {
262             conn.Close();
263         }
264     }
265 
266     public bool Update(string set, T where)
267     {
268         string sql = $"Update {_tableName} set {set} where {GetWhere(where)}";
269         return baseUpdate(sql);
270     }
271 
272     public bool Update(T set, T where)
273     {
274         string sqlSet = "";
275         int i = 0;
276         set.GetType().GetProperties().ToList()
277             .ForEach(delegate(PropertyInfo u)
278             {
279                 if (u.GetValue(set, null) != null)
280                 {
281                     if (1 == i)
282                     {
283                         sqlSet += ", ";
284                     }
285                     sqlSet = sqlSet + u.Name + "=" + GetValue(u.PropertyType, u.GetValue(set, null));
286                     i = 1;
287                 }
288             });
289         string sql = $"Update {_tableName} set {sqlSet} where {GetWhere(where)}";
290         return baseUpdate(sql);
291     }
292 
293     public bool Delete(T where)
294     {
295         string sql = $"delete from {_tableName} where {GetWhere(where)}";
296         return baseUpdate(sql);
297     }
298 
299     public bool Delete(string where)
300     {
301         string sql = $"delete from {_tableName} where {where}";
302         return baseUpdate(sql);
303     }
304 
305     public DataTable ExecuteQuery(string sql)
306     {
307         new List<T>();
308         MySqlConnection conn = new MySqlConnection(_connString);
309         try
310         {
311             conn.Open();
312             MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(new MySqlCommand($"select * from {_tableName}", conn));
313             DataTable dataTable = new DataTable();
314             mySqlDataAdapter.Fill(dataTable);
315             return dataTable;
316         }
317         catch (Exception errorInfo)
318         {
319             Exception ex = _errorInfo = errorInfo;
320             return null;
321         }
322         finally
323         {
324             conn.Close();
325         }
326     }
327 
328     public bool ExecuteNonQuery(string sql)
329     {
330         return baseUpdate(sql);
331     }
332 
333     private string GetWhere(T where)
334     {
335         string sqlWhere = "";
336         int i = 0;
337         where.GetType().GetProperties().ToList()
338             .ForEach(delegate(PropertyInfo u)
339             {
340                 if (u.GetValue(where, null) != null)
341                 {
342                     if (1 == i)
343                     {
344                         sqlWhere += " and ";
345                     }
346                     sqlWhere = sqlWhere + u.Name + "=" + GetValue(u.PropertyType, u.GetValue(where, null));
347                     i = 1;
348                 }
349             });
350         return sqlWhere;
351     }
352 }

什么sugar啊各種ORM之類的也可以看到人類一路走過來都在造這些玩意兒，回望過去這些半成品也算是有一些影子在里面吧，

轉載請註明出處，本文鏈接：https://www.uj5u.com/net/244518.html

標籤：.NET技术

上一篇：c#關于做介面開發時，遇到的Form表單提交資料

下一篇：C#-WinForm跨執行緒修改UI界面