欢迎光临
我的个人博客网站

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

赞(0) 打赏
未经允许不得转载:张拓的天空 » 两个半成品的ORM
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

专业的IT技术经验分享 更专业 更方便

联系我们本站主机

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏