mvc5 mysql 框架开源_我的基于asp.net mvc5 +mysql+dapper+easyui 的Web开发框架（1）数据库访问（0）...

最新推荐文章于 2021-02-07 03:47:46 发布

贫血王子

最新推荐文章于 2021-02-07 03:47:46 发布

阅读量147

点赞数

文章标签： mvc5 mysql 框架开源

本文链接：https://blog.csdn.net/weixin_30985047/article/details/113232367

版权

1 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.Linq;4 usingSystem.Text;5 usingSystem.Configuration;6 usingSystem.Data;7 usingDapper;8 usingCommonModel;9

10 namespaceDBTools11 {12 ///

13 ///执行sql ，抛出异常14 ///

15 public classDBHelper16 {17 #region 连接信息

18 public virtual stringConnectionString19 {20 get;21 set;22 }23 public virtualDataBaseType DBType24 {25 get;26 set;27 }28 #endregion

30 #region 获取DBAdapter

31 publicDBAdapter GetDBAdapter()32 {33 returnDBTools.DBFactory.GetAdapter(DBType, ConnectionString);34 }35

36 #endregion

38 #region 查询

39 public List Query(string sql, object query = null, IDbTransaction tran = null)40 {41 DBAdapter adapter = null; ;42

43 try

44 {45 if (tran == null)46 {47 adapter =GetDBAdapter();48 }49 var conn = tran != null ?tran.Connection : adapter.OpenDatabase();50 var m =conn.Query(sql, query).ToList();51 returnm;52 }53 catch (Exception ex) { throwex; }54 finally

55 {56 if (adapter != null)57 {58 adapter.CloseDatabase();59 }60 }61 }62

63 public List Query(string sql, object query = null, IDbTransaction tran = null)64 {65 DBAdapter adapter = null; ;66

67 try

68 {69 if (tran == null)70 {71 adapter =GetDBAdapter();72 }73 var conn = tran != null ?tran.Connection : adapter.OpenDatabase();74 var m = conn.Query(sql, query).ToList();75 returnm;76 }77 catch (Exception ex) { throwex; }78 finally

79 {80 if (adapter != null)81 {82 adapter.CloseDatabase();83 }84 }85 }86

87 public T ExecuteScalar(string sql, object query = null, IDbTransaction tran = null)88 {89 DBAdapter adapter = null; ;90

91 try

92 {93 if (tran == null)94 {95 adapter =GetDBAdapter();96 }97 var conn = tran != null ?tran.Connection : adapter.OpenDatabase();98 var m = conn.ExecuteScalar(sql, query, tran);99 returnm;100 }101 catch (Exception ex) { throwex; }102 finally

103 {104 if (adapter != null)105 {106 adapter.CloseDatabase();107 }108 }109 }110

111 #endregion

112

113 #region Result

114

115 ///

116 ///返回一个ResultSet 查询117 ///

118 ///

119 ///

120 ///

121 ///

122 ///

123 public ResultSet GetResultSet(string sql, string OrderBy, ParamList pl, IDbTransaction tran = null)124 {125 DBAdapter adapter =GetDBAdapter();126

127 try

128 {129 var conn =adapter.OpenDatabase();130 if(pl.ispage)131 {132 string pagesql =adapter.GetPageSql(sql, OrderBy);133 string countsql =adapter.GetCountSql(sql);134

135 var m = conn.Query(pagesql, pl.GetParameters(), tran).ToList();136 var c = conn.ExecuteScalar(countsql, pl.GetParameters(), tran);137 return new ResultSet() { Value = m, Total =c };138 }139 else

140 {141 var m = conn.Query(sql + "order by" + OrderBy, pl.GetParameters(), tran).ToList();142 return new ResultSet() { Value = m, Total =m.Count };143 }144 }145 catch

146 {147 throw;148 }149 finally

150 {151 adapter.CloseDatabase();152 }153 }154

155 ///

156 ///返回一个ResultSet 查询157 ///

158 ///

159 ///

160 ///

161 ///

162 ///

163 public ResultSet GetResultSet(string sql, string OrderBy, ParamList pl, IDbTransaction tran = null)164 {165 DBAdapter adapter =GetDBAdapter();166

167 try

168 {169 var conn =adapter.OpenDatabase();170 if(pl.ispage)171 {172 string pagesql =adapter.GetPageSql(sql, OrderBy);173 string countsql =adapter.GetCountSql(sql);174

175 var m =conn.Query(pagesql, pl.GetParameters(), tran);176 var c = conn.ExecuteScalar(countsql, pl.GetParameters(), tran);177 returnResultSet.GetResultSet(m, c);178 }179 else

180 {181 var m = conn.Query(sql + "order by" +OrderBy, pl.GetParameters(), tran);182 returnResultSet.GetResultSet(m);183 }184 }185 catch

186 {187 throw;188 }189 finally

190 {191 adapter.CloseDatabase();192 }193 }194 #endregion

195

196 #region 执行sql

197 ///

198 ///执行sql，返回受影响记录数199 ///

200 ///

201 ///

202 ///

203 public int Execute(string sql, object model = null, IDbTransaction tran = null)204 {205 DBAdapter adapter = null; ;206

207 try

208 {209 if (tran == null)210 {211 adapter =GetDBAdapter();212 }213 var conn = tran != null ?tran.Connection : adapter.OpenDatabase();214 returnconn.Execute(sql, model, tran);215

216 }217 catch (Exception ex) { throwex; }218 finally

219 {220 if (adapter != null)221 {222 adapter.CloseDatabase();223 }224 }225 }226 ///

227 ///执行sql，返回受影响条记录数228 ///

229 ///

230 ///

231 ///

232 ///

233 public int Execute(string sql, ParamList pl, IDbTransaction tran = null)234 {235 DBAdapter adapter = null; ;236

237 try

238 {239 if (tran == null)240 {241 adapter =GetDBAdapter();242 }243 var conn = tran != null ?tran.Connection : adapter.OpenDatabase();244 returnconn.Execute(sql, pl.GetParameters(), tran);245

246 }247 catch { throw; }248 finally

249 {250 if (adapter != null)251 {252 adapter.CloseDatabase();253 }254 }255 }256

257

258

259

260 ///

261 ///同一个sql语句，批量操作多个对象262 ///

263 ///

264 ///

265 ///

266 public int Execute(string sql, IEnumerable models, IDbTransaction tran = null)267 {268 DBAdapter adapter = null; ;269

270 try

271 {272 if (tran == null)273 {274 adapter =GetDBAdapter();275 }276 var conn = tran != null ?tran.Connection : adapter.OpenDatabase();277 returnconn.Execute(sql, models, tran);278 }279 catch (Exception ex) { throwex; }280 finally

281 {282 if (adapter != null)283 {284 adapter.CloseDatabase();285 }286 }287 }288

289 ///

290 ///使用事务批量执行多个语句291 ///

292 ///

293 ///

294 ///

295 public void Execute(List sqllist, object model = null, IDbTransaction tran = null)296 {297 DBAdapter adapter = null; ;298

299 try

300 {301 if (tran == null)302 {303 adapter =GetDBAdapter();304 tran =adapter.BeginTransaction();305 }306 var conn =tran.Connection;307

308 foreach (var sql insqllist)309 {310 conn.Execute(sql, model, tran);311 }312 if (adapter != null)313 {314 adapter.Commit();315 }316 }317 catch

318 {319 if (adapter != null)320 {321 adapter.Rollback();322 }323 throw;324 }325 finally

326 {327 if (adapter != null)328 {329 adapter.CloseDatabase();330 }331 }332

333 }334 ///

335 ///使用事务批量执行sql语句336 ///

337 ///

338 ///

339 ///

340 public void Execute(List sqllist, ParamList pl, IDbTransaction tran = null)341 {342 DBAdapter adapter = null; ;343

344 try

345 {346 if (tran == null)347 {348 adapter =GetDBAdapter();349 tran =adapter.BeginTransaction();350 }351 var conn =tran.Connection;352

353 foreach (var sql insqllist)354 {355 conn.Execute(sql, pl.GetParameters(), tran);356 }357 if (adapter != null)358 {359 adapter.Commit();360 }361 }362 catch

363 {364 if (adapter != null)365 {366 adapter.Rollback();367 }368 throw;369 }370 finally

371 {372 if (adapter != null)373 {374 adapter.CloseDatabase();375 }376 }377

378 }379 #endregion

380

381 }382 }