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 }
