如何实现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 = 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     }

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.BeginTransact