SqlHelper简单实现（通过Expression和反射）7.MySql数据处理类
MySql的数据处理类和Sql Server没有太大差别，从思路上来说没有区别，所以此处只是给出代码，不再多加解释了。
1 using System; 2 using System.Configuration; 3 using MySql.Data.MySqlClient; 4 using RA.DataAccess.Common; 5 6 namespace RA.DataAccess.MySqlDbUtility 7 { 8 public partial class DbUtility : IDbUtility 9 { 10 private MySqlConnection conn; 11 private MySqlDataAdapter da; 12 private readonly string connectionString; 13 14 private DbUtility() 15 { 16 connectionString = ConfigurationManager.AppSettings["MySql"]; 17 } 18 private static DbUtility _dbUtility; 19 20 public static DbUtility GetInstance() 21 { 22 return _dbUtility ?? (_dbUtility = new DbUtility()); 23 } 24 25 /// <summary> 26 /// 为通过反射生成的实例赋值 27 /// </summary> 28 /// <typeparam name="T">实例的类型</typeparam> 29 /// <param name="obj">实例</param> 30 /// <param name="value">值</param> 31 /// <param name="key">成员名称</param> 32 private void SetValue<T>(ref T obj, Object value, String key) where T : class 33 { 34 var property = obj.GetType().GetProperty(key); 35 var type = property.PropertyType.Name; 36 if (value is System.DBNull) 37 { 38 property.SetValue(obj, null, null); 39 return; 40 } 41 switch (type) 42 { 43 case "Int32": 44 property.SetValue(obj, int.Parse(value.ToString()), null); 45 break; 46 case "String": 47 property.SetValue(obj, value.ToString(), null); 48 break; 49 case "DateTime": 50 property.SetValue(obj, (DateTime)value, null); 51 break; 52 default: 53 property.SetValue(obj, value, null); 54 break; 55 } 56 } 57 /// <summary> 58 /// 获得SQLSession实例 59 /// </summary> 60 /// <typeparam name="T"></typeparam> 61 /// <returns></returns> 62 public SqlSession<T> GetSqlExpression<T>() where T : class 63 { 64 var temp = new SqlSession<T>(); 65 conn = new MySqlConnection(connectionString); 66 return temp; 67 } 68 } 69 }
1 using System; 2 using System.Collections.Generic; 3 using System.Data; 4 using System.Linq; 5 using System.Linq.Expressions; 6 using MySql.Data.MySqlClient; 7 using RA.DataAccess.Common; 8 9 namespace RA.DataAccess.MySqlDbUtility 10 { 11 public partial class DbUtility 12 { 13 /// <summary> 14 /// 获取列表，适用于单表查询 15 /// </summary> 16 /// <typeparam name="T"></typeparam> 17 /// <param name="exp"></param> 18 /// <returns></returns> 19 public List<T> GetList<T>(SqlSession<T> exp) where T : class 20 { 21 var datatable = GetDataBySql<T>(exp.SqlExpression); 22 var result = new List<T>(); 23 foreach (DataRow i in datatable.Rows) 24 { 25 T obj = Activator.CreateInstance<T>(); 26 foreach (var k in exp.Fields) 27 { 28 SetValue(ref obj, i[k], k); 29 } 30 result.Add(obj); 31 } 32 return result; 33 } 34 35 /// <summary> 36 /// 获取列表，适用于联表查询 37 /// </summary> 38 /// <typeparam name="Target">DTO类型</typeparam> 39 /// <typeparam name="T">exp代表的Entity类型</typeparam> 40 /// <param name="exp">SQLSession实例</param> 41 /// <returns>DTO列表</returns> 42 public List<Target> GetList<Target, T>(SqlSession<T> exp) where T : class where Target : class 43 { 44 var datatable = GetDataBySql<T>(exp.SqlExpression); 45 var result = new List<Target>(); 46 47 foreach (DataRow i in datatable.Rows) 48 { 49 var obj = Activator.CreateInstance<Target>(); 50 foreach (var k in EntityHelper.GetDTOFields<Target>()) 51 { 52 SetValue(ref obj, i[k], k); 53 } 54 result.Add(obj); 55 } 56 return result; 57 } 58 59 public List<T> Paged<T>(Expression<Func<T, object>> By, int pageIndex, int pageSize = 1) where T : class 60 { 61 var result = new List<T>(); 62 var sql = $@"SELECT * FROM {EntityHelper.GetTableName<T>()} ORDER BY {ExpressionHelper.GetSqlByExpression(By.Body)} LIMIT {pageIndex},{pageSize}"; 63 conn = new MySqlConnection(connectionString); 64 conn.Open(); 65 var datatable = GetDataBySql<T>(sql); 66 conn.Close(); 67 foreach (DataRow i in datatable.Rows) 68 { 69 var obj = Activator.CreateInstance<T>(); 70 foreach (var k in EntityHelper.GetFields<T>(false)) 71 { 72 SetValue(ref obj, i[k], k); 73 } 74 result.Add(obj); 75 } 76 return result; 77 } 78 /// <summary> 79 /// 按照主键获取单条记录 80 /// </summary> 81 /// <typeparam name="T">实体类型</typeparam> 82 /// <param name="func">筛选条件</param> 83 /// <returns>实体的实例</returns> 84 public T GetSingle<T>(Expression<Func<T, bool>> func) where T : class 85 { 86 var temptable = new DataTable(); 87 var exp = ExpressionHelper.GetSqlByExpression(func.Body); 88 var fields = EntityHelper.GetFiledString<T>(); 89 var tablename = EntityHelper.GetTableName<T>(); 90 var sql = "SELECT " + fields + " FROM " + tablename + " WHERE " + exp; 91 conn = new MySqlConnection(connectionString); 92 da = new MySqlDataAdapter(sql, conn); 93 da.Fill(temptable); 94 if(temptable.Rows.Count == 0) 95 { 96 return null; 97 } 98 T obj = Activator.CreateInstance<T>(); 99 foreach (var k in EntityHelper.GetFields<T>(false)) 100 { 101 SetValue(ref obj, temptable.Rows[0][k], k); 102 } 103 return obj; 104 } 105 106 /// <summary> 107 /// 删除单个记录 108 /// </summary> 109 /// <typeparam name="T"></typeparam> 110 /// <param name="func"></param> 111 public int Delete<T>(Expression<Func<T, bool>> func) where T : class 112 { 113 var tablename = EntityHelper.GetTableName<T>(); 114 var exp = ExpressionHelper.GetSqlByExpression(func.Body); 115 var sql = "DELETE FROM " + tablename + " WHERE " + exp; 116 return RunSingleSql<T>(sql); 117 } 118 119 120 /// <summary> 121 /// 添加单个记录 122 /// </summary> 123 /// <param name="obj"></param> 124 public int Add<T>(T obj) where T : class 125 { 126 var data = ObjectHelper.GetKeyValue(obj); 127 128 var sql = "INSERT INTO {0}({1}) VALUES({2})"; 129 var tablename = EntityHelper.GetTableName<T>(); 130 var keys = string.Join(",", data.Keys.ToArray()); 131 var values = string.Join(",", data.Values.Select(a => a == null ? "''" : "'" + a.ToString() + "'")); 132 133 sql = string.Format(sql, tablename, keys, values); 134 135 return RunSingleSql<T>(sql); 136 } 137 138 /// <summary> 139 /// 执行除了Select以外的SQL，请不要在循环中使用这个方法，会有性能问题 140 /// </summary> 141 /// <typeparam name="T"></typeparam> 142 /// <param name="sql"></param> 143 /// <returns></returns> 144 public int RunSingleSql<T>(string sql) where T : class 145 { 146 var conn = new MySqlConnection(connectionString); 147 var dc = new MySqlCommand(sql, conn); 148 149 if (conn.State != ConnectionState.Open) 150 { 151 conn.Open(); 152 } 153 try 154 { 155 return dc.ExecuteNonQuery(); 156 } 157 finally 158 { 159 conn.Close(); 160 } 161 } 162 163 /// <summary> 164 /// 通过sql获取数据 165 /// </summary> 166 /// <typeparam name="T"></typeparam> 167 /// <param name="sql"></param> 168 /// <returns></returns> 169 public DataTable GetDataBySql<T>(string sql) where T : class 170 { 171 var conn = new MySqlConnection(connectionString); 172 da = new MySqlDataAdapter(sql, conn); 173 var result = new DataTable(); 174 da.Fill(result); 175 return result; 176 } 177 178 /// <summary> 179 /// 更新一条记录 180 /// </summary> 181 /// <typeparam name="T"></typeparam> 182 /// <param name="obj">要修改的数据的Entity的实例</param> 183 /// <param name="func">要修改数据的条件</param> 184 /// <returns></returns> 185 public int Update<T>(T obj, Expression<Func<T, bool>> func) where T : class 186 { 187 if (func == null) 188 { 189 throw new ArgumentNullException("表达式不能为空！"); 190 } 191 var tablename = EntityHelper.GetTableName<T>(); 192 var data = ObjectHelper.GetKeyValue(obj); 193 var updatestr = data.Aggregate("", (current, i) => current + (i.Key + "='" + i.Value.ToString() + "',")); 194 updatestr = updatestr.Substring(0, updatestr.Length - 1); 195 var where = ExpressionHelper.GetSqlByExpression(func.Body); 196 var sql = $"UPDATE {tablename} SET {updatestr} WHERE {@where}"; 197 return RunSingleSql<T>(sql); 198 } 199 200 /// <summary> 201 /// 批量添加记录 202 /// </summary> 203 /// <typeparam name="T"></typeparam> 204 /// <param name="list"></param> 205 public int AddList<T>(List<T> objs) where T : class 206 { 207 if (!objs.Any()) 208 { 209 throw new ArgumentNullException("列表为空！"); 210 } 211 var tablename = EntityHelper.GetTableName(objs[0].GetType()); 212 var conn = new MySqlConnection(connectionString); 213 if (conn.State != ConnectionState.Open) 214 { 215 conn.Open(); 216 } 217 var transaction = conn.BeginTransaction(); 218 var dc = conn.CreateCommand(); 219 dc.Transaction = transaction; 220 var count = 0; 221 try 222 { 223 foreach (var k in objs) 224 { 225 var data = new Dictionary<string, object>(); 226 foreach (var i in k.GetType().GetProperties()) 227 { 228 var value = k.GetType().GetProperty(i.Name).GetValue(k, null); 229 data.Add(i.Name, value); 230 } 231 var keys = string.Join(",", data.Keys.ToArray()); 232 var values = string.Join(",", data.Values.Select(a => "'" + a.ToString() + "'")); 233 var sql = $"INSERT INTO {tablename}({keys}) VALUES({values})"; 234 dc.CommandText = sql; 235 dc.ExecuteNonQuery(); 236 count++; 237 } 238 transaction.Commit(); 239 return count; 240 } 241 catch (Exception ex) 242 { 243 transaction.Rollback(); 244 throw ex; 245 } 246 finally 247 { 248 conn.Close(); 249 } 250 } 251 252 /// <summary> 253 /// 获取总数 254 /// </summary> 255 /// <typeparam name="T"></typeparam> 256 /// <param name="func">不传值的话，返回总数</param> 257 /// <returns></returns> 258 public int Count<T>(Expression<Func<T, bool>> func = null) where T : class 259 { 260 var tablename = EntityHelper.GetTableName<T>(); 261 var sql = ""; 262 if (func == null) 263 { 264 sql = $"SELECT COUNT(*) FROM {tablename}"; 265 } 266 else 267 { 268 var where = ExpressionHelper.GetSqlByExpression(func.Body); 269 sql = $"SELECT COUNT(*) FROM {tablename} WHERE {@where}"; 270 } 271 conn = new MySqlConnection(connectionString); 272 da = new MySqlDataAdapter(sql, conn); 273 var datatable = new DataTable(); 274 da.Fill(datatable); 275 var result = (int)datatable.Rows[0][0]; 276 return result; 277 } 278 279 /// <summary> 280 /// 获取单个值 281 /// </summary> 282 /// <typeparam name="T"></typeparam> 283 /// <typeparam name="Target"></typeparam> 284 /// <param name="field">字段名</param> 285 /// <param name="func">条件表达式</param> 286 /// <returns></returns> 287 public Target Scala<T,Target>(Expression<Func<T, Target>> field,Expression<Func<T,bool>> func) 288 { 289 var fieldname = ExpressionHelper.GetSqlByExpression(field.Body); 290 var exp = ExpressionHelper.GetSqlByExpression(func.Body); 291 var sql = $"SELECT {fieldname} FROM {EntityHelper.GetTableName<T>()} WHERE {exp}"; 292 conn = new MySqlConnection(connectionString); 293 da = new MySqlDataAdapter(sql, conn); 294 var datatable = new DataTable(); 295 da.Fill(datatable); 296 if(datatable.Rows.Count == 0) 297 { 298 return default(Target); 299 } 300 var result = (Target)datatable.Rows[0][0]; 301 return result; 302 } 303 } 304 }
