- A+
所属分类：.NET技术
只要是有点结构化的思想，不可能项目里一个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之类的也可以看到人类一路走过来都在造这些玩意儿 ，回望过去这些半成品 也算是有一些影子在里面吧。
