怎么实现SQL事务的提交，又不对外进行污染

 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         }

 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 )

 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 [email protected] where [email protected] ");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 [email protected],[email protected]  where [email protected] ");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     }

  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     }

 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         }