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 }