首页 > 代码库 > 如何实现SQL事务的提交，又不对外进行污染
如何实现SQL事务的提交，又不对外进行污染
一、以下是本人的一点思路:
1、在事务方法中，参数运用委托Func，选用Func 的原因是多入参，单一出参
2、事务传参运用泛型，选用泛型的原因是可以减少代码量，类型安全
二、说明中涉及4个类：
1、Orders、OrderDetail：订单实体，订单详细实体
2、Repository:进行数据操作
3、SqlTran：事务处理方法（前期的时候方法比较多，后期就会共用【泛型好处】）
三、步骤
1、创建实体（创建对应的数据库语句）
1）实体
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 OrderDetail13 {14 public Int32 Id { get; set; }15 public Int32 OrderId { get; set; }16 public String Name { get; set; }17 }
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 )
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 =http://www.mamicode.com/ order.Id;18 parName.Value =http://www.mamicode.com/ 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 =http://www.mamicode.com/ order.Id;39 parName.Value =http://www.mamicode.com/ 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 =http://www.mamicode.com/ orderDetail.Id;61 parOrderId.Value =http://www.mamicode.com/ orderDetail.OrderId;62 parName.Value =http://www.mamicode.com/ 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 =http://www.mamicode.com/ orderDetail.Id;84 parOrderId.Value =http://www.mamicode.com/ orderDetail.OrderId;85 parName.Value =http://www.mamicode.com/ 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 }
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 try105 {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 finally124 {125 if (tran != null)126 tran.Dispose();127 if (conn != null)128 {129 conn.Close();130 conn.Dispose();131 }132 }133 134 }135 }
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 }
这里只是对单一实体进行处理，下文继续对多实体进行处理
注：SqlHelper为微软的简单类文件，可以在网上自行下载
如何实现SQL事务的提交，又不对外进行污染
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。