两个半成品的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 = 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之类的也可以看到人类一路走过来都在造这些玩意儿，回望过去这些半成品也算是有一些影子在里面吧。

posted @ 2021-01-04 11:12 assassinx 阅读(337) 评论(0) 编辑收藏举报

刷新页面返回顶部

assassinx's blog

一个大龄码农所作的一些研究小成果展示

两个半成品的ORM

公告