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

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

dielanchong4440

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

阅读量305

点赞数

文章标签：数据库测试 javascript ViewUI

原文链接：http://www.cnblogs.com/NewBigLiang/p/6179539.html

版权

一、数据库访问概述

　　1. 数据库使用mysql，orm采用dapper框架。dapper框架应用简单，只是需要自己手写sql语句，但是对于像我这样写了多年sql语句的人来说，这应该不算问题，个人还是比较喜欢这种手写sql语句的框架。

　　Dapper 虽然已经非常简单，但是为了调用时方便还是进行了一下封装，这样在写DAL 层方法时，就不用每次都try catch了。

　　2.先放代码：

　　调用方法示例：

 1         #region role  增删改查
 2         /// <summary>
 3         /// 增加一条
 4         /// </summary>
 5         public static void AddRole(Role role, IDbTransaction tran = null)
 6         {
 7             string sql = @"insert into role(Id,Name,Note)
 8                        values(@Id,@Name,@Note)";
 9             role.EnsureIdNotNull();
10             DB.DBHelper.Execute(sql, role, tran);
11         }
12 
13         /// <summary>
14         /// 更新 
15         /// </summary>
16         public static void UpdateRole(Role role, IDbTransaction tran = null)
17         {
18             string sql = @"update role set Name=@Name,Note=@Note where Id=@Id";
19             DB.DBHelper.Execute(sql, role, tran);
20         }
21 
22         /// <summary>
23         /// 删除 一条记录
24         /// </summary>
25         public static void DeleteRole(string Id, IDbTransaction tran = null)
26         {
27             string sql = "select count(1) from userrole where RoleId= @Id";
28             var c = DB.DBHelper.ExecuteScalar<int>(sql, new { Id = @Id });
29             if (c > 0)
30             {
31                 throw new OperateException("该角色已经有用户在使用，不能删除");
32             }
33 
34             List<string> sqllist = new List<string>();
35             sqllist.Add("delete from role where Id=@Id");
36             sqllist.Add("delete from rolemenu where RoleId=@Id"); //同步删除为角色分配的权限
37             DB.DBHelper.Execute(sqllist, new { Id = Id }, tran);
38         }
39 
40         /// <summary>
41         /// 列表查询
42         /// </summary>
43         /// <param name="pl"></param>
44         /// <returns></returns>
45         public static ResultSet<Role> GetRoleList(ParamList pl)
46         {
47             string sql = @"select  Id,Name,Note from role where 1=1";
48             pl.orderby = "Name";
49             //添加各种查询条件
50             if (pl.isnotnull("keywords"))
51             {
52                 sql += " and instr(concat(Name,Note),@keywords)>0";
53             }
54             return DB.DBHelper.GetResultSet<Role>(sql, pl.orderby, pl);
55         }
56         #endregion

View Code

DBHelper 类:

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 using System.Configuration;
  6 using System.Data;
  7 using Dapper;
  8 using CommonModel;
  9 
 10 namespace DBTools
 11 {
 12     /// <summary>
 13     /// 执行sql ，抛出异常
 14     /// </summary>
 15     public class DBHelper
 16     {
 17         #region 连接信息
 18         public virtual string ConnectionString
 19         {
 20             get;
 21             set;
 22         }
 23         public virtual DataBaseType DBType
 24         {
 25             get;
 26             set;
 27         }
 28         #endregion
 29 
 30         #region 获取DBAdapter
 31         public DBAdapter GetDBAdapter()
 32         {
 33             return DBTools.DBFactory.GetAdapter(DBType, ConnectionString);
 34         }
 35 
 36         #endregion
 37 
 38         #region 查询
 39         public List<dynamic> 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                 return m;
 52             }
 53             catch (Exception ex) { throw ex; }
 54             finally
 55             {
 56                 if (adapter != null)
 57                 {
 58                     adapter.CloseDatabase();
 59                 }
 60             }
 61         }
 62 
 63         public List<T> Query<T>(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<T>(sql, query).ToList<T>();
 75                 return m;
 76             }
 77             catch (Exception ex) { throw ex; }
 78             finally
 79             {
 80                 if (adapter != null)
 81                 {
 82                     adapter.CloseDatabase();
 83                 }
 84             }
 85         }
 86 
 87         public T ExecuteScalar<T>(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<T>(sql, query, tran);
 99                 return m;
100             }
101             catch (Exception ex) { throw ex; }
102             finally
103             {
104                 if (adapter != null)
105                 {
106                     adapter.CloseDatabase();
107                 }
108             }
109         }
110 
111         #endregion
112 
113         #region Result
114 
115         /// <summary>
116         /// 返回一个ResultSet  查询
117         /// </summary>
118         /// <typeparam name="T"></typeparam>
119         /// <param name="sql"></param>
120         /// <param name="pl"></param>
121         /// <param name="tran"></param>
122         /// <returns></returns>
123         public ResultSet<T> GetResultSet<T>(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<T>(pagesql, pl.GetParameters(), tran).ToList<T>();
136                     var c = conn.ExecuteScalar<int>(countsql, pl.GetParameters(), tran);
137                     return new ResultSet<T>() { Value = m, Total = c };
138                 }
139                 else
140                 {
141                     var m = conn.Query<T>(sql + " order by " + OrderBy, pl.GetParameters(), tran).ToList<T>();
142                     return new ResultSet<T>() { Value = m, Total = m.Count };
143                 }
144             }
145             catch
146             {
147                 throw;
148             }
149             finally
150             {
151                 adapter.CloseDatabase();
152             }
153         }
154 
155         /// <summary>
156         /// 返回一个ResultSet  查询
157         /// </summary>
158         /// <param name="sql"></param>
159         /// <param name="OrderBy"></param>
160         /// <param name="pl"></param>
161         /// <param name="tran"></param>
162         /// <returns></returns>
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<int>(countsql, pl.GetParameters(), tran);
177                     return ResultSet.GetResultSet(m, c);
178                 }
179                 else
180                 {
181                     var m = conn.Query(sql + " order by " + OrderBy, pl.GetParameters(), tran);
182                     return ResultSet.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         /// <summary>
198         /// 执行sql，返回受影响记录数
199         /// </summary>
200         /// <param name="sql"></param>
201         /// <param name="model"></param>
202         /// <param name="tran"></param>
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                 return conn.Execute(sql, model, tran);
215 
216             }
217             catch (Exception ex) { throw ex; }
218             finally
219             {
220                 if (adapter != null)
221                 {
222                     adapter.CloseDatabase();
223                 }
224             }
225         }
226         /// <summary>
227         /// 执行sql，返回受影响条记录数
228         /// </summary>
229         /// <param name="sql"></param>
230         /// <param name="pl"></param>
231         /// <param name="tran"></param>
232         /// <returns></returns>
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                 return conn.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         /// <summary>
261         /// 同一个sql语句，批量操作多个对象
262         /// </summary>
263         /// <param name="sql"></param>
264         /// <param name="models"></param>
265         /// <param name="tran"></param>
266         public int Execute(string sql, IEnumerable<object> 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                 return conn.Execute(sql, models, tran);
278             }
279             catch (Exception ex) { throw ex; }
280             finally
281             {
282                 if (adapter != null)
283                 {
284                     adapter.CloseDatabase();
285                 }
286             }
287         }
288 
289         /// <summary>
290         /// 使用事务批量执行多个语句
291         /// </summary>
292         /// <param name="sqllist"></param>
293         /// <param name="pl"></param>
294         /// <returns></returns>
295         public void Execute(List<string> 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 in sqllist)
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         /// <summary>
335         /// 使用事务批量执行sql语句
336         /// </summary>
337         /// <param name="sqllist"></param>
338         /// <param name="pl"></param>
339         /// <param name="tran"></param>
340         public void Execute(List<string> 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 in sqllist)
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 }

View Code

3.说明：

　　　　Dapper 相关目录结构如下：

　　　　a.分成两个项目是为了避免数据库访问DBTools 被每层引用。除DAL层外，各层可以只引用CommonModel 就可以了.

　　　　b.使用DBAdapter 和DBFactory 对相关操作进行了封装，用于支持多数据库，目前我已经实现了MySqlDBAdapter,其他的数据库可以自行实现。

　　　　c. 使用时可以使用一个新的DB 类对DBHelper 进行封装，达到方便调用，及同一个项目中使用多个数据库的目的，如在DAL 层中定义一个DB 类，类中包含一个静态DBHelper 对象，这样就可以向文中开始的那样调用DBHelper 了。

　　　　如下：

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using System.Threading.Tasks;
 6 using System.Configuration;
 7 
 8 namespace MVC.DAL
 9 {
10     public class DB 
11     {
12         private static DBTools.DBHelper _instance;
13         private static object lockhelper = new object();
14         public static DBTools.DBHelper DBHelper
15         {
16             get
17             {
18                 if (_instance == null)
19                 {
20                     lock (lockhelper)
21                     {
22                         if (_instance == null)
23                         {
24                             _instance = new DBTools.DBHelper();
25                             _instance.DBType = DBTools.DataBaseType.MySql;
26                             _instance.ConnectionString = ConfigurationManager.ConnectionStrings["mysql"].ConnectionString;
27                         }
28                     }
29                 }
30                 return _instance;
31             }
32         }
33     }
34 }