dapper mysql 拓展_Dapper扩展

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 }