Dapper扩展

  1 using Dapper;
  2 using MySql.Data.MySqlClient;
  3 using System;
  4 using System.Collections.Generic;
  5 using System.Data;
  6 using System.Threading.Tasks;
  7 
  8 namespace DAL
  9 {
 10     /// <summary>
 11     ///DapperHelper
 12     /// </summary>
 13     public class DapperMySQLHelp
 14     {
 15 
 16         private string connection = "";
 17 
 18         public DapperMySQLHelp() { }
 19 
 20 
 21         public DapperMySQLHelp(string connStr)
 22         {
 23             connection = connStr;
 24         }
 25         public IDbConnection Connection()
 26         {
 27             var conn = new MySqlConnection(connection);
 28             conn.Open();
 29             return conn;
 30         }
 31 
 32         #region +ExcuteNonQuery 增、删、改同步操作
 33         /// <summary>
 34         /// 增、删、改同步操作
 35         /// 2016-10-26
 36         ///  </summary>
 37         /// <typeparam name="T">实体</typeparam>
 38         /// <param name="connection">链接字符串</param>
 39         /// <param name="cmd">sql语句</param>
 40         /// <param name="param">参数</param>
 41         /// <param name="flag">true存储过程，false sql语句</param>
 42         /// <returns>int</returns>
 43         public int ExcuteNonQuery<T>(string cmd, DynamicParameters param, bool flag = true) where T : class, new()
 44         {
 45             int result = 0;
 46             using (MySqlConnection con = new MySqlConnection(connection))
 47             {
 48                 if (flag)
 49                 {
 50                     result = con.Execute(cmd, param, null, null, CommandType.StoredProcedure);
 51                 }
 52                 else
 53                 {
 54                     result = con.Execute(cmd, param, null, null, CommandType.Text);
 55                 }
 56             }
 57             return result;
 58         }
 59         #endregion
 60 
 61         #region +ExcuteNonQueryAsync 增、删、改异步操作
 62         /// <summary>
 63         /// 增、删、改异步操作
 64         /// 2016-10-26
 65         /// </summary>
 66         /// <typeparam name="T">实体</typeparam>
 67         /// <param name="connection">链接字符串</param>
 68         /// <param name="cmd">sql语句</param>
 69         /// <param name="param">参数</param>
 70         /// <param name="flag">true存储过程，false sql语句</param>
 71         /// <returns>int</returns>
 72         public async Task<int> ExcuteNonQueryAsync<T>(string cmd, DynamicParameters param, bool flag = true) where T : class, new()
 73         {
 74             int result = 0;
 75             using (MySqlConnection con = new MySqlConnection(connection))
 76             {
 77                 if (flag)
 78                 {
 79                     result = await con.ExecuteAsync(cmd, param, null, null, CommandType.StoredProcedure);
 80                 }
 81                 else
 82                 {
 83                     result = await con.ExecuteAsync(cmd, param, null, null, CommandType.Text);
 84                 }
 85             }
 86             return result;
 87         }
 88         #endregion
 89 
 90         #region +ExecuteScalar 同步查询操作
 91         /// <summary>
 92         /// 同步查询操作
 93         /// 2016-10-26
 94         /// </summary>
 95         /// <typeparam name="T">实体</typeparam>
 96         /// <param name="connection">连接字符串</param>
 97         /// <param name="cmd">sql语句</param>
 98         /// <param name="param">参数</param>
 99         /// <param name="flag">true存储过程，false sql语句</param>
100         /// <returns>object</returns>
101         public object ExecuteScalar(string cmd, DynamicParameters param = null, bool flag = true)
102         {
103             object result = null;
104             using (MySqlConnection con = new MySqlConnection(connection))
105             {
106                 if (flag)
107                 {
108                     result = con.ExecuteScalar(cmd, param, null, null, CommandType.StoredProcedure);
109                 }
110                 else
111                 {
112                     result = con.ExecuteScalar(cmd, param, null, null, CommandType.Text);
113                 }
114             }
115             return result;
116         }
117         #endregion
118 
119         #region +ExecuteScalarAsync 异步查询操作
120         /// <summary>
121         /// 异步查询操作
122         /// 2016-10-26
123         /// </summary>
124         /// <typeparam name="T">实体</typeparam>
125         /// <param name="connection">连接字符串</param>
126         /// <param name="cmd">sql语句</param>
127         /// <param name="param">参数</param>
128         /// <param name="flag">true存储过程，false sql语句</param>
129         /// <returns>object</returns>
130         public async Task<object> ExecuteScalarAsync(string cmd, DynamicParameters param = null, bool flag = true)
131         {
132             object result = null;
133             using (MySqlConnection con = new MySqlConnection(connection))
134             {
135                 if (flag)
136                 {
137                     result = await con.ExecuteScalarAsync(cmd, param, null, null, CommandType.StoredProcedure);
138                 }
139                 else
140                 {
141                     result = con.ExecuteScalarAsync(cmd, param, null, null, CommandType.Text);
142                 }
143             }
144             return result;
145         }
146         #endregion
147 
148         #region +FindOne  同步查询一条数据
149         /// <summary>
150         /// 同步查询一条数据
151         /// 2016-10-26
152         /// </summary>
153         /// <typeparam name="T">实体</typeparam>
154         /// <param name="connection">连接字符串</param>
155         /// <param name="cmd">sql语句</param>
156         /// <param name="param">参数</param>
157         /// <param name="flag">true存储过程，false sql语句</param>
158         /// <returns>t</returns>
159         public T FindOne<T>(string cmd, DynamicParameters param, bool flag = true) where T : class, new()
160         {
161             IDataReader dataReader = null;
162             using (MySqlConnection con = new MySqlConnection(connection))
163             {
164                 if (flag)
165                 {
166                     dataReader = con.ExecuteReader(cmd, param, null, null, CommandType.StoredProcedure);
167                 }
168                 else
169                 {
170                     dataReader = con.ExecuteReader(cmd, param, null, null, CommandType.Text);
171                 }
172                 if (dataReader == null || !dataReader.Read()) return null;
173                 Type type = typeof(T);
174                 T t = new T();
175                 foreach (var item in type.GetProperties())
176                 {
177                     for (int i = 0; i < dataReader.FieldCount; i++)
178                     {
179                         //属性名与查询出来的列名比较
180                         if (item.Name.ToLower() != dataReader.GetName(i).ToLower()) continue;
181                         var kvalue = dataReader[item.Name];
182                         if (kvalue == DBNull.Value) continue;
183                         item.SetValue(t, kvalue, null);
184                         break;
185                     }
186                 }
187                 return t;
188             }
189         }
190         #endregion
191 
192         #region +FindOne  异步查询一条数据
193         /// <summary>
194         /// 异步查询一条数据
195         /// 2016-10-26
196         /// </summary>
197         /// <typeparam name="T">实体</typeparam>
198         /// <param name="connection">连接字符串</param>
199         /// <param name="cmd">sql语句</param>
200         /// <param name="param">参数</param>
201         /// <param name="flag">true存储过程，false sql语句</param>
202         /// <returns>t</returns>
203         public async Task<T> FindOneAsync<T>(string cmd, DynamicParameters param, bool flag = true) where T : class, new()
204         {
205             IDataReader dataReader = null;
206             using (MySqlConnection con = new MySqlConnection(connection))
207             {
208                 if (flag)
209                 {
210                     dataReader = await con.ExecuteReaderAsync(cmd, param, null, null, CommandType.StoredProcedure);
211                 }
212                 else
213                 {
214                     dataReader = await con.ExecuteReaderAsync(cmd, param, null, null, CommandType.Text);
215                 }
216                 if (dataReader == null || !dataReader.Read()) return null;
217                 Type type = typeof(T);
218                 T t = new T();
219                 foreach (var item in type.GetProperties())
220                 {
221                     for (int i = 0; i < dataReader.FieldCount; i++)
222                     {
223                         //属性名与查询出来的列名比较
224                         if (item.Name.ToLower() != dataReader.GetName(i).ToLower()) continue;
225                         var kvalue = dataReader[item.Name];
226                         if (kvalue == DBNull.Value) continue;
227                         item.SetValue(t, kvalue, null);
228                         break;
229                     }
230                 }
231                 return t;
232             }
233         }
234         #endregion
235 
236         #region +FindToList  同步查询数据集合
237         /// <summary>
238         /// 同步查询数据集合
239         /// 2016-10-26
240         /// </summary>
241         /// <typeparam name="T">实体</typeparam>
242         /// <param name="connection">连接字符串</param>
243         /// <param name="cmd">sql语句</param>
244         /// <param name="param">参数</param>
245         /// <param name="flag">true存储过程，false sql语句</param>
246         /// <returns>t</returns>
247         public IList<T> FindToList<T>(string cmd, DynamicParameters param, bool flag = true) where T : class, new()
248         {
249             IDataReader dataReader = null;
250             using (MySqlConnection con = new MySqlConnection(connection))
251             {
252                 if (flag)
253                 {
254                     dataReader = con.ExecuteReader(cmd, param, null, null, CommandType.StoredProcedure);
255                 }
256                 else
257                 {
258                     dataReader = con.ExecuteReader(cmd, param, null, null, CommandType.Text);
259                 }
260                 if (dataReader == null || !dataReader.Read()) return null;
261                 Type type = typeof(T);
262                 List<T> tlist = new List<T>();
263                 while (dataReader.Read())
264                 {
265                     T t = new T();
266                     foreach (var item in type.GetProperties())
267                     {
268                         for (int i = 0; i < dataReader.FieldCount; i++)
269                         {
270                             //属性名与查询出来的列名比较
271                             if (item.Name.ToLower() != dataReader.GetName(i).ToLower()) continue;
272                             var kvalue = dataReader[item.Name];
273                             if (kvalue == DBNull.Value) continue;
274                             item.SetValue(t, kvalue, null);
275                             break;
276                         }
277                     }
278                     if (tlist != null) tlist.Add(t);
279                 }
280                 return tlist;
281             }
282         }
283         #endregion
284 
285         #region +FindToListAsync  异步查询数据集合
286         /// <summary>
287         /// 异步查询数据集合
288         /// 2016-10-26
289         /// </summary>
290         /// <typeparam name="T">实体</typeparam>
291         /// <param name="connection">连接字符串</param>
292         /// <param name="cmd">sql语句</param>
293         /// <param name="param">参数</param>
294         /// <param name="flag">true存储过程，false sql语句</param>
295         /// <returns>t</returns>
296         public async Task<IList<T>> FindToListAsync<T>(string cmd, DynamicParameters param, bool flag = true) where T : class, new()
297         {
298             IDataReader dataReader = null;
299             using (MySqlConnection con = new MySqlConnection(connection))
300             {
301                 if (flag)
302                 {
303                     dataReader = await con.ExecuteReaderAsync(cmd, param, null, null, CommandType.StoredProcedure);
304                 }
305                 else
306                 {
307                     dataReader = await con.ExecuteReaderAsync(cmd, param, null, null, CommandType.Text);
308                 }
309                 if (dataReader == null || !dataReader.Read()) return null;
310                 Type type = typeof(T);
311                 List<T> tlist = new List<T>();
312                 while (dataReader.Read())
313                 {
314                     T t = new T();
315                     foreach (var item in type.GetProperties())
316                     {
317                         for (int i = 0; i < dataReader.FieldCount; i++)
318                         {
319                             //属性名与查询出来的列名比较
320                             if (item.Name.ToLower() != dataReader.GetName(i).ToLower()) continue;
321                             var kvalue = dataReader[item.Name];
322                             if (kvalue == DBNull.Value) continue;
323                             item.SetValue(t, kvalue, null);
324                             break;
325                         }
326                     }
327                     if (tlist != null) tlist.Add(t);
328                 }
329                 return tlist;
330             }
331         }
332         #endregion
333 
334         #region +FindToList  同步查询数据集合
335         /// <summary>
336         /// 同步查询数据集合
337         /// 2016-10-26
338         /// </summary>
339         /// <typeparam name="T">实体</typeparam>
340         /// <param name="connection">连接字符串</param>
341         /// <param name="cmd">sql语句</param>
342         /// <param name="param">参数</param>
343         /// <param name="flag">true存储过程，false sql语句</param>
344         /// <returns>t</returns>
345         public IList<T> FindToListAsPage<T>(string cmd, DynamicParameters param, bool flag = true) where T : class, new()
346         {
347             IDataReader dataReader = null;
348             using (MySqlConnection con = new MySqlConnection(connection))
349             {
350                 if (flag)
351                 {
352                     dataReader = con.ExecuteReader(cmd, param, null, null, CommandType.StoredProcedure);
353                 }
354                 else
355                 {
356                     dataReader = con.ExecuteReader(cmd, param, null, null, CommandType.Text);
357                 }
358                 if (dataReader == null || !dataReader.Read()) return null;
359                 Type type = typeof(T);
360                 List<T> tlist = new List<T>();
361                 while (dataReader.Read())
362                 {
363                     T t = new T();
364                     foreach (var item in type.GetProperties())
365                     {
366                         for (int i = 0; i < dataReader.FieldCount; i++)
367                         {
368                             //属性名与查询出来的列名比较
369                             if (item.Name.ToLower() != dataReader.GetName(i).ToLower()) continue;
370                             var kvalue = dataReader[item.Name];
371                             if (kvalue == DBNull.Value) continue;
372                             item.SetValue(t, kvalue, null);
373                             break;
374                         }
375                     }
376                     if (tlist != null) tlist.Add(t);
377                 }
378                 return tlist;
379             }
380         }
381         #endregion
382 
383         #region +FindToListByPage  同步分页查询数据集合
384         /// <summary>
385         /// 同步分页查询数据集合
386         /// 2016-10-26
387         /// </summary>
388         /// <typeparam name="T">实体</typeparam>
389         /// <param name="connection">连接字符串</param>
390         /// <param name="cmd">sql语句</param>
391         /// <param name="param">参数</param>
392         /// <param name="flag">true存储过程，false sql语句</param>
393         /// <returns>t</returns>
394         public IList<T> FindToListByPage<T>(string cmd, DynamicParameters param, bool flag = true) where T : class, new()
395         {
396             IDataReader dataReader = null;
397             using (MySqlConnection con = new MySqlConnection(connection))
398             {
399                 if (flag)
400                 {
401                     dataReader = con.ExecuteReader(cmd, param, null, null, CommandType.StoredProcedure);
402                 }
403                 else
404                 {
405                     dataReader = con.ExecuteReader(cmd, param, null, null, CommandType.Text);
406                 }
407                 if (dataReader == null || !dataReader.Read()) return null;
408                 Type type = typeof(T);
409                 List<T> tlist = new List<T>();
410                 while (dataReader.Read())
411                 {
412                     T t = new T();
413                     foreach (var item in type.GetProperties())
414                     {
415                         for (int i = 0; i < dataReader.FieldCount; i++)
416                         {
417                             //属性名与查询出来的列名比较
418                             if (item.Name.ToLower() != dataReader.GetName(i).ToLower()) continue;
419                             var kvalue = dataReader[item.Name];
420                             if (kvalue == DBNull.Value) continue;
421                             item.SetValue(t, kvalue, null);
422                             break;
423                         }
424                     }
425                     if (tlist != null) tlist.Add(t);
426                 }
427                 return tlist;
428             }
429         }
430         #endregion
431 
432         #region +FindToListByPageAsync  异步分页查询数据集合
433         /// <summary>
434         /// 异步分页查询数据集合
435         /// 2016-10-26
436         /// </summary>
437         /// <typeparam name="T">实体</typeparam>
438         /// <param name="connection">连接字符串</param>
439         /// <param name="cmd">sql语句</param>
440         /// <param name="param">参数</param>
441         /// <param name="flag">true存储过程，false sql语句</param>
442         /// <returns>t</returns>
443         public async Task<IList<T>> FindToListByPageAsync<T>(string cmd, DynamicParameters param, bool flag = true) where T : class, new()
444         {
445             IDataReader dataReader = null;
446             using (MySqlConnection con = new MySqlConnection(connection))
447             {
448                 if (flag)
449                 {
450                     dataReader = await con.ExecuteReaderAsync(cmd, param, null, null, CommandType.StoredProcedure);
451                 }
452                 else
453                 {
454                     dataReader = await con.ExecuteReaderAsync(cmd, param, null, null, CommandType.Text);
455                 }
456                 if (dataReader == null || !dataReader.Read()) return null;
457                 Type type = typeof(T);
458                 List<T> tlist = new List<T>();
459                 while (dataReader.Read())
460                 {
461                     T t = new T();
462                     foreach (var item in type.GetProperties())
463                     {
464                         for (int i = 0; i < dataReader.FieldCount; i++)
465                         {
466                             //属性名与查询出来的列名比较
467                             if (item.Name.ToLower() != dataReader.GetName(i).ToLower()) continue;
468                             var kvalue = dataReader[item.Name];
469                             if (kvalue == DBNull.Value) continue;
470                             item.SetValue(t, kvalue, null);
471                             break;
472                         }
473                     }
474                     if (tlist != null) tlist.Add(t);
475                 }
476                 return tlist;
477             }
478         }
479         #endregion
480 
481 
482         #region +QueryPage 同步分页查询操作
483         /// <summary>
484         /// 同步分页查询操作
485         /// </summary>
486         /// <param name="sql">查询语句</param>
487         /// <param name="orderBy">排序字段</param>
488         /// <param name="pageIndex">当前页码</param>
489         /// <param name="pageSize">页面容量</param>
490         /// <param name="count">总条数</param>
491         /// <param name="param">参数</param>
492         /// <param name="strWhere">条件</param>
493         /// <returns>返回结果的数据集合</returns>
494         public List<Dictionary<string, Object>> QueryPage(string sql, string orderBy, int pageIndex, int pageSize, out int count, object param = null, string strWhere = "")
495         {
496             count = 0;
497             List<Dictionary<String, Object>> list = new List<Dictionary<string, object>>();
498 
499 
500             if (sql.Contains("where"))
501             {
502                 sql = sql + strWhere;
503             }
504             else
505             {
506                 sql = sql + " where 1=1 " + strWhere;
507             }
508 
509 
510             string strSQL = "SELECT (@i:=@i+1) AS row_id,tab.* FROM (" + sql + ")  AS TAB,(SELECT @i:=0) AS it ORDER BY " + orderBy + " LIMIT " + (pageIndex - 1) + "," + pageSize;
511 
512 
513             list = QueryData(strSQL, param, false);
514 
515 
516             string strCount = "SELECT count(*) FROM (" + sql + ") tcount";
517             count = Convert.ToInt32(ExecuteScalar(strCount));
518 
519             return list;
520         }
521         #endregion
522 
523         #region +QueryData  同步查询数据集合
524         /// <summary>
525         /// 同步查询数据集合
526         /// </summary>
527         /// <param name="cmd">sql语句</param>
528         /// <param name="param">参数</param>
529         /// <param name="flag">true存储过程，false sql语句</param>
530         /// <returns>t</returns>
531         public List<Dictionary<String, object>> QueryData(string cmd, object param = null, bool flag = false)
532         {
533             IDataReader dataReader = null;
534             using (MySqlConnection con = new MySqlConnection(connection))
535             {
536                 if (flag)
537                 {
538                     dataReader = con.ExecuteReader(cmd, param, null, null, CommandType.StoredProcedure);
539                 }
540                 else
541                 {
542                     dataReader = con.ExecuteReader(cmd, param, null, null, CommandType.Text);
543                 }
544                 List<Dictionary<String, object>> list = new List<Dictionary<string, object>>();
545                 Dictionary<String, object> dic = null;
546                 string colName = "";
547                 while (dataReader.Read())
548                 {
549                     dic = new Dictionary<string, object>();
550 
551                     for (int i = 0; i < dataReader.FieldCount; i++)
552                     {
553                         colName = dataReader.GetName(i);
554                         dic.Add(colName, dataReader[colName]);
555                     }
556 
557 
558                     if (dic.Keys.Count > 0)
559                     {
560                         list.Add(dic);
561                     }
562                 }
563                 return list;
564             }
565         }
566         #endregion
567 
568     }
569 }
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/liangwenchao-912/p/8630502.html