如何实现SQL事务的提交,又不对外进行污染_.NET_编程开发_程序员俱乐部

中国优秀的程序员网站程序员频道CXYCLUB技术地图
热搜:
更多>>
 
您所在的位置: 程序员俱乐部 > 编程开发 > .NET > 如何实现SQL事务的提交,又不对外进行污染

如何实现SQL事务的提交,又不对外进行污染

 2015/1/6 13:28:43  六斤五  程序员俱乐部  我要评论(0)
  • 摘要:一、以下是本人的一点思路:1、在事务方法中,参数运用委托Func,选用Func的原因是多入参,单一出参2、事务传参运用泛型,选用泛型的原因是可以减少代码量,类型安全二、说明中涉及4个类:1、Orders、OrderDetail:订单实体,订单详细实体2、Repository:进行数据操作3、SqlTran:事务处理方法(前期的时候方法比较多,后期就会共用【泛型好处】)三、步骤1、创建实体(创建对应的数据库语句)1)实体1///<summary>2///订单表3///<
  • 标签:实现 SQL

一、以下是本人的一点思路:

1、在事务方法中,参数运用委托Func,选用Func 的原因是多入参,单一出参
2、事务传参运用泛型,选用泛型的原因是可以减少代码量,类型安全

二、说明中涉及4个类:
1、Orders、OrderDetail:订单实体,订单详细实体
2、Repository:进行数据操作
3、SqlTran:事务处理方法(前期的时候方法比较多,后期就会共用【泛型好处】)

三、步骤
1、创建实体(创建对应的数据库语句)

    1)实体

class="code_img_closed" src="/Upload/Images/2015010613/0015B68B3C38AA5B.gif" alt="" />logs_code_hide('f9ef9509-de64-4297-a547-18faf305a6cd',event)" src="/Upload/Images/2015010613/2B1B950FA3DF188F.gif" alt="" />
 1         /// <summary>
 2         /// 订单表
 3         /// </summary>
 4         public class Orders
 5         {
 6             public Int32 Id { get; set; }
 7             public String Name{get;set;}
 8         }
 9         /// <summary>
10         /// 订单详细表
11         /// </summary>
12         public class OrderDetail
13         {
14             public Int32 Id { get; set; }
15             public Int32 OrderId { get; set; }
16             public String Name { get; set; }
17         }
View Code

   2)sql语句

 1 /*订单*/
 2 CREATE TABLE Orders 
 3 (
 4    PRIMARY KEY(Id),
 5   Id int,
 6   Name varchar(20)
 7 )
 8 /*订单详细*/
 9 CREATE TABLE OrderDetail 
10 (
11   PRIMARY KEY(Id),
12   Id INT,
13   OrderId INT,
14   Name varchar(20)
15 )
View Code

2、写增、改方法,作为事务的参数(较简单,用于进行测试)

 1     public class Repository
 2     {
 3         public const String connStr = "server=;database=TestDB;user id=;pwd=";
 4 
 5         /// <summary>
 6         /// 添加订单
 7         /// </summary>
 8         /// <param name="order">订单信息</param>
 9         /// <param name="tran">事务</param>
10         /// <returns>受影响的数量</returns>
11         public Int32 AddOrder(Orders order, SqlTransaction tran = null)
12         {
13             StringBuilder sb = new StringBuilder();
14             SqlParameter parId = new SqlParameter("@Id", SqlDbType.Int);
15             SqlParameter parName = new SqlParameter("@Name", SqlDbType.VarChar, 25);
16 
17             parId.Value = order.Id;
18             parName.Value = order.Name;
19             sb.Append(" insert into Orders(Id,Name) values(@Id,@Name)");
20 
21             if (tran == null)
22                 return SqlHelper.ExecuteNonQuery(connStr, CommandType.Text, sb.ToString(), parId, parName);
23             return SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sb.ToString(), parId, parName);
24         }
25 
26         /// <summary>
27         /// 更新订单
28         /// </summary>
29         /// <param name="order">订单信息</param>
30         /// <param name="tran">事务</param>
31         /// <returns>受影响的数量</returns>
32         public Int32 UpdateOrder(Orders order, SqlTransaction tran = null)
33         {
34             StringBuilder sb = new StringBuilder();
35             SqlParameter parId = new SqlParameter("@Id", SqlDbType.Int);
36             SqlParameter parName = new SqlParameter("@Name", SqlDbType.VarChar, 25);
37 
38             parId.Value = order.Id;
39             parName.Value = order.Name;
40             sb.Append(" update Orders set Name=@Name where Id=@id ");
41 
42             if (tran == null)
43                 return SqlHelper.ExecuteNonQuery(connStr, CommandType.Text, sb.ToString(), parId, parName);
44             return SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sb.ToString(), parId, parName);
45         }
46 
47         /// <summary>
48         /// 添加订单详细
49         /// </summary>
50         /// <param name="order">订单详细信息</param>
51         /// <param name="tran">事务</param>
52         /// <returns>受影响的数量</returns>
53         public Int32 AddOrderDetail(OrderDetail orderDetail, SqlTransaction tran = null)
54         {
55             StringBuilder sb = new StringBuilder();
56             SqlParameter parId = new SqlParameter("@Id", SqlDbType.Int);
57             SqlParameter parOrderId = new SqlParameter("@OrderId", SqlDbType.Int);
58             SqlParameter parName = new SqlParameter("@Name", SqlDbType.VarChar, 25);
59 
60             parId.Value = orderDetail.Id;
61             parOrderId.Value = orderDetail.OrderId;
62             parName.Value = orderDetail.Name;
63             sb.Append(" insert into OrderDetail(Id,OrderId,Name) values(@Id,@OrderId,@Name)");
64 
65             if (tran == null)
66                 return SqlHelper.ExecuteNonQuery(connStr, CommandType.Text, sb.ToString(), parId, parOrderId, parName);
67             return SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sb.ToString(), parId, parOrderId, parName);
68         }
69 
70         /// <summary>
71         /// 更新订单详细
72         /// </summary>
73         /// <param name="order">订单详细信息</param>
74         /// <param name="tran">事务</param>
75         /// <returns>受影响的数量</returns>
76         public Int32 UpdateOrderDetail(OrderDetail orderDetail, SqlTransaction tran = null)
77         {
78             StringBuilder sb = new StringBuilder();
79             SqlParameter parId = new SqlParameter("@Id", SqlDbType.Int);
80             SqlParameter parOrderId = new SqlParameter("@OrderId", SqlDbType.Int);
81             SqlParameter parName = new SqlParameter("@Name", SqlDbType.VarChar, 25);
82 
83             parId.Value = orderDetail.Id;
84             parOrderId.Value = orderDetail.OrderId;
85             parName.Value = orderDetail.Name;
86             sb.Append(" update OrderDetail set Name=@Name,OrderId=@OrderId  where Id=@id ");
87 
88             if (tran == null)
89                 return SqlHelper.ExecuteNonQuery(connStr, CommandType.Text, sb.ToString(), parId, parOrderId, parName);
90             return SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sb.ToString(), parId, parOrderId, parName);
91         }
92     }
View Code

3、写事务方法,参数为委托方法Func
(1)用逻辑方法作为参数进行传递,
(2)事务处理、数据库连接都在事务方法中进行处理
(3)运用泛型,减少代码量,类型安全

  1     /// <summary>
  2     /// 事务类
  3     /// </summary>
  4     public class SqlTran
  5     {
  6         /// <summary>
  7         /// 执行事务(单一方法)
  8         /// </summary>
  9         /// <typeparam name="T">实体</typeparam>
 10         /// <param name="method">要执行的方法(SqlTransaction 默认传入为null)</param>
 11         /// <param name="obj1">参数值</param>
 12         /// <returns></returns>
 13         public static Int32 ExecuteTran<T>(Func<T, SqlTransaction, Int32> method, T obj1)
 14             where T : new()
 15         {
 16             Int32 count = 0;
 17             SqlConnection conn = null;
 18             SqlTransaction tran = null;
 19             try
 20             {
 21                 conn = new SqlConnection(Repository.connStr);
 22                 conn.Open();
 23                 tran = conn.BeginTransaction();
 24 
 25                 count += method(obj1, tran);          //执行方法
 26 
 27                 tran.Commit();
 28                 return count;
 29             }
 30             catch (Exception ex)
 31             {
 32                 tran.Rollback();
 33                 return -1;
 34             }
 35             finally
 36             {
 37                 if (tran != null)
 38                     tran.Dispose();
 39                 if (conn != null)
 40                 {
 41                     conn.Close();
 42                     conn.Dispose();
 43                 }
 44             }
 45 
 46         }
 47 
 48         /// <summary>
 49         /// 执行事务(事务中存在两个方法)
 50         /// </summary>
 51         /// <typeparam name="T">实体</typeparam>
 52         /// <param name="method">要执行的方法(SqlTransaction 默认传入为null)</param>
 53         /// <param name="obj1">参数值</param>
 54         /// <returns></returns>
 55         public static Int32 ExecuteTran<T>(Func<T, SqlTransaction, Int32> method1, Func<T, SqlTransaction, Int32> method2, T obj1, T obj2)
 56             where T : new()
 57         {
 58             Int32 count = 0;
 59             SqlConnection conn = null;
 60             SqlTransaction tran = null;
 61             try
 62             {
 63                 conn = new SqlConnection(Repository.connStr);
 64                 conn.Open();
 65                 tran = conn.BeginTransaction();
 66 
 67                 count += method1(obj1, tran);
 68                 count += method2(obj2, tran);
 69 
 70                 tran.Commit();
 71                 return count;
 72             }
 73             catch (Exception ex)
 74             {
 75                 tran.Rollback();
 76                 return -1;
 77             }
 78             finally
 79             {
 80                 if (tran != null)
 81                     tran.Dispose();
 82                 if (conn != null)
 83                 {
 84                     conn.Close();
 85                     conn.Dispose();
 86                 }
 87             }
 88 
 89         }
 90 
 91         /// <summary>
 92         /// 执行事务(同实体事务执行【方法不一定相同】)
 93         /// </summary>
 94         /// <typeparam name="T">实体</typeparam>
 95         /// <param name="method">要执行的方法(SqlTransaction 默认传入为null)</param>
 96         /// <param name="obj1">参数值</param>
 97         /// <returns></returns>
 98         public static Int32 ExecuteTran<T>(IList<Func<T, SqlTransaction, Int32>> methods, IList<T> objs)
 99             where T : new()
100         {
101             Int32 count = 0;
102             SqlConnection conn = null;
103             SqlTransaction tran = null;
104             try
105             {
106                 conn = new SqlConnection(Repository.connStr);
107                 conn.Open();
108                 tran = conn.BeginTransaction();
109                 if (methods.Count() != objs.Count())
110                     return -1;  //异常
111 
112                 for (int i = 0; i < objs.Count; i++)
113                     count += methods[i](objs[i], tran);
114 
115                 tran.Commit();
116                 return count;
117             }
118             catch (Exception ex)
119             {
120                 tran.Rollback();
121                 return -1;
122             }
123             finally
124             {
125                 if (tran != null)
126                     tran.Dispose();
127                 if (conn != null)
128                 {
129                     conn.Close();
130                     conn.Dispose();
131                 }
132             }
133 
134         }
135     }
View Code

4、调用事务方法进行测试

 1         public void Test()
 2         {
 3             Repository repository = new Repository();
 4             Orders order1 = new Orders() { Id = 1, Name = "name1" };
 5             Orders order2 = new Orders() { Id = 2, Name = "name2" };
 6             Orders order3 = new Orders() { Id = 3, Name = "name3" };
 7             Orders order4 = new Orders() { Id = 4, Name = "name4" };
 8             Orders order5 = new Orders() { Id = 5, Name = "name5" };
 9             OrderDetail orderDetail1 = new OrderDetail() { Id = 1, OrderId = 1, Name = "namedetail1" };
10             OrderDetail orderDetail2 = new OrderDetail() { Id = 2, OrderId = 1, Name = "namedetail2" };
11 
12 
13             SqlTran.ExecuteTran<Orders>(repository.AddOrder, order1);
14             SqlTran.ExecuteTran<OrderDetail>(repository.AddOrderDetail, orderDetail1);   //泛型的好处,可以少写代码
15 
16             SqlTran.ExecuteTran<Orders>(repository.AddOrder, repository.AddOrder, order2, order3);   //同方法,同实体类型
17             order1.Name = "orderName1update";
18             SqlTran.ExecuteTran<Orders>(repository.AddOrder, repository.UpdateOrder, order4, order1); //不同方法,同实体类型
19 
20             List<Func<Orders, SqlTransaction, Int32>> list = new List<Func<Orders, SqlTransaction, Int32>>();   //多方法(混合更新和添加)
21             List<Orders> listObj = new List<Orders>();
22             list.Add(repository.UpdateOrder);
23             order1.Name = "orderName1updatet";
24             listObj.Add(order1);
25             list.Add(repository.AddOrder);
26             listObj.Add(order5);
27             SqlTran.ExecuteTran<Orders>(list, listObj);
28         }
View Code

这里只是对单一实体进行处理,下文继续对多实体进行处理
注:SqlHelper为微软的简单类文件,可以在网上自行下载

发表评论
用户名: 匿名