如何實現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