1 usingDapper;2 usingMySql.Data.MySqlClient;3 usingSystem;4 usingSystem.Collections.Generic;5 usingSystem.Data;6 usingSystem.Threading.Tasks;7
8 namespaceDAL9 {10 ///
11 ///DapperHelper12 ///
13 public classDapperMySQLHelp14 {15
16 private string connection = "";17
18 publicDapperMySQLHelp() { }19
20
21 public DapperMySQLHelp(stringconnStr)22 {23 connection =connStr;24 }25 publicIDbConnection Connection()26 {27 var conn = newMySqlConnection(connection);28 conn.Open();29 returnconn;30 }31
32 #region +ExcuteNonQuery 增、删、改同步操作
33 ///
34 ///增、删、改同步操作35 ///2016-10-2636 ///
37 /// 实体
38 /// 链接字符串
39 /// sql语句
40 /// 参数
41 /// true存储过程,false sql语句
42 /// int
43 public int ExcuteNonQuery(string cmd, DynamicParameters param, bool flag = true) where T : class, new()44 {45 int result = 0;46 using (MySqlConnection con = newMySqlConnection(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 returnresult;58 }59 #endregion
60
61 #region +ExcuteNonQueryAsync 增、删、改异步操作
62 ///
63 ///增、删、改异步操作64 ///2016-10-2665 ///
66 /// 实体
67 /// 链接字符串
68 /// sql语句
69 /// 参数
70 /// true存储过程,false sql语句
71 /// int
72 public async Task ExcuteNonQueryAsync(string cmd, DynamicParameters param, bool flag = true) where T : class, new()73 {74 int result = 0;75 using (MySqlConnection con = newMySqlConnection(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 returnresult;87 }88 #endregion
89
90 #region +ExecuteScalar 同步查询操作
91 ///
92 ///同步查询操作93 ///2016-10-2694 ///
95 /// 实体
96 /// 连接字符串
97 /// sql语句
98 /// 参数
99 /// true存储过程,false sql语句
100 /// object
101 public object ExecuteScalar(string cmd, DynamicParameters param = null, bool flag = true)102 {103 object result = null;104 using (MySqlConnection con = newMySqlConnection(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 returnresult;116 }117 #endregion
118
119 #region +ExecuteScalarAsync 异步查询操作
120 ///
121 ///异步查询操作122 ///2016-10-26123 ///
124 /// 实体
125 /// 连接字符串
126 /// sql语句
127 /// 参数
128 /// true存储过程,false sql语句
129 /// object
130 public async Task ExecuteScalarAsync(string cmd, DynamicParameters param = null, bool flag = true)131 {132 object result = null;133 using (MySqlConnection con = newMySqlConnection(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 returnresult;145 }146 #endregion
147
148 #region +FindOne 同步查询一条数据
149 ///
150 ///同步查询一条数据151 ///2016-10-26152 ///
153 /// 实体
154 /// 连接字符串
155 /// sql语句
156 /// 参数
157 /// true存储过程,false sql语句
158 /// t
159 public T FindOne(string cmd, DynamicParameters param, bool flag = true) where T : class, new()160 {161 IDataReader dataReader = null;162 using (MySqlConnection con = newMySqlConnection(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 = newT();175 foreach (var item intype.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 returnt;188 }189 }190 #endregion
191
192 #region +FindOne 异步查询一条数据
193 ///
194 ///异步查询一条数据195 ///2016-10-26196 ///
197 /// 实体
198 /// 连接字符串
199 /// sql语句
200 /// 参数
201 /// true存储过程,false sql语句
202 /// t
203 public async Task FindOneAsync(string cmd, DynamicParameters param, bool flag = true) where T : class, new()204 {205 IDataReader dataReader = null;206 using (MySqlConnection con = newMySqlConnection(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 = newT();219 foreach (var item intype.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 returnt;232 }233 }234 #endregion
235
236 #region +FindToList 同步查询数据集合
237 ///
238 ///同步查询数据集合239 ///2016-10-26240 ///
241 /// 实体
242 /// 连接字符串
243 /// sql语句
244 /// 参数
245 /// true存储过程,false sql语句
246 /// t
247 public IList FindToList(string cmd, DynamicParameters param, bool flag = true) where T : class, new()248 {249 IDataReader dataReader = null;250 using (MySqlConnection con = newMySqlConnection(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 tlist = new List();263 while(dataReader.Read())264 {265 T t = newT();266 foreach (var item intype.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 returntlist;281 }282 }283 #endregion
284
285 #region +FindToListAsync 异步查询数据集合
286 ///
287 ///异步查询数据集合288 ///2016-10-26289 ///
290 /// 实体
291 /// 连接字符串
292 /// sql语句
293 /// 参数
294 /// true存储过程,false sql语句
295 /// t
296 public async Task> FindToListAsync(string cmd, DynamicParameters param, bool flag = true) where T : class, new()297 {298 IDataReader dataReader = null;299 using (MySqlConnection con = newMySqlConnection(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 tlist = new List();312 while(dataReader.Read())313 {314 T t = newT();315 foreach (var item intype.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 returntlist;330 }331 }332 #endregion
333
334 #region +FindToList 同步查询数据集合
335 ///
336 ///同步查询数据集合337 ///2016-10-26338 ///
339 /// 实体
340 /// 连接字符串
341 /// sql语句
342 /// 参数
343 /// true存储过程,false sql语句
344 /// t
345 public IList FindToListAsPage(string cmd, DynamicParameters param, bool flag = true) where T : class, new()346 {347 IDataReader dataReader = null;348 using (MySqlConnection con = newMySqlConnection(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 tlist = new List();361 while(dataReader.Read())362 {363 T t = newT();364 foreach (var item intype.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 returntlist;379 }380 }381 #endregion
382
383 #region +FindToListByPage 同步分页查询数据集合
384 ///
385 ///同步分页查询数据集合386 ///2016-10-26387 ///
388 /// 实体
389 /// 连接字符串
390 /// sql语句
391 /// 参数
392 /// true存储过程,false sql语句
393 /// t
394 public IList FindToListByPage(string cmd, DynamicParameters param, bool flag = true) where T : class, new()395 {396 IDataReader dataReader = null;397 using (MySqlConnection con = newMySqlConnection(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 tlist = new List();410 while(dataReader.Read())411 {412 T t = newT();413 foreach (var item intype.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 returntlist;428 }429 }430 #endregion
431
432 #region +FindToListByPageAsync 异步分页查询数据集合
433 ///
434 ///异步分页查询数据集合435 ///2016-10-26436 ///
437 /// 实体
438 /// 连接字符串
439 /// sql语句
440 /// 参数
441 /// true存储过程,false sql语句
442 /// t
443 public async Task> FindToListByPageAsync(string cmd, DynamicParameters param, bool flag = true) where T : class, new()444 {445 IDataReader dataReader = null;446 using (MySqlConnection con = newMySqlConnection(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 tlist = new List();459 while(dataReader.Read())460 {461 T t = newT();462 foreach (var item intype.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 returntlist;477 }478 }479 #endregion
480
481
482 #region +QueryPage 同步分页查询操作
483 ///
484 ///同步分页查询操作485 ///
486 /// 查询语句
487 /// 排序字段
488 /// 当前页码
489 /// 页面容量
490 /// 总条数
491 /// 参数
492 /// 条件
493 /// 返回结果的数据集合
494 public List> QueryPage(string sql, string orderBy, int pageIndex, int pageSize, out int count, object param = null, string strWhere = "")495 {496 count = 0;497 List> list = new List>();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 returnlist;520 }521 #endregion
522
523 #region +QueryData 同步查询数据集合
524 ///
525 ///同步查询数据集合526 ///
527 /// sql语句
528 /// 参数
529 /// true存储过程,false sql语句
530 /// t
531 public List> QueryData(string cmd, object param = null, bool flag = false)532 {533 IDataReader dataReader = null;534 using (MySqlConnection con = newMySqlConnection(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> list = new List>();545 Dictionary dic = null;546 string colName = "";547 while(dataReader.Read())548 {549 dic = new Dictionary();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 returnlist;564 }565 }566 #endregion
567
568 }569 }