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

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

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

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

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

    1）实体

        /// <summary>
        /// 订单表
        /// </summary>
        public class Orders
        {
            public Int32 Id { get; set; }
            public String Name{get;set;}
        }
        /// <summary>
        /// 订单详细表
        /// </summary>
        public class OrderDetail
        {
            public Int32 Id { get; set; }
            public Int32 OrderId { get; set; }
            public String Name { get; set; }
        }

   2)sql语句

/*订单*/
CREATE TABLE Orders 
(
   PRIMARY KEY(Id),
  Id int,
  Name varchar(20)
)
/*订单详细*/
CREATE TABLE OrderDetail 
(
  PRIMARY KEY(Id),
  Id INT,
  OrderId INT,
  Name varchar(20)
)

2、写增、改方法，作为事务的参数（较简单，用于进行测试）

    public class Repository
    {
        public const String connStr = "server=;database=TestDB;user id=;pwd=";

        /// <summary>
        /// 添加订单
        /// </summary>
        /// <param name="order">订单信息</param>
        /// <param name="tran">事务</param>
        /// <returns>受影响的数量</returns>
        public Int32 AddOrder(Orders order, SqlTransaction tran = null)
        {
            StringBuilder sb = new StringBuilder();
            SqlParameter parId = new SqlParameter("@Id", SqlDbType.Int);
            SqlParameter parName = new SqlParameter("@Name", SqlDbType.VarChar, 25);

            parId.Value = order.Id;
            parName.Value = order.Name;
            sb.Append(" insert into Orders(Id,Name) values(@Id,@Name)");

            if (tran == null)
                return SqlHelper.ExecuteNonQuery(connStr, CommandType.Text, sb.ToString(), parId, parName);
            return SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sb.ToString(), parId, parName);
        }

        /// <summary>
        /// 更新订单
        /// </summary>
        /// <param name="order">订单信息</param>
        /// <param name="tran">事务</param>
        /// <returns>受影响的数量</returns>
        public Int32 UpdateOrder(Orders order, SqlTransaction tran = null)
        {
            StringBuilder sb = new StringBuilder();
            SqlParameter parId = new SqlParameter("@Id", SqlDbType.Int);
            SqlParameter parName = new SqlParameter("@Name", SqlDbType.VarChar, 25);

            parId.Value = order.Id;
            parName.Value = order.Name;
            sb.Append(" update Orders set Name=@Name where Id=@id ");

            if (tran == null)
                return SqlHelper.ExecuteNonQuery(connStr, CommandType.Text, sb.ToString(), parId, parName);
            return SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sb.ToString(), parId, parName);
        }

        /// <summary>
        /// 添加订单详细
        /// </summary>
        /// <param name="order">订单详细信息</param>
        /// <param name="tran">事务</param>
        /// <returns>受影响的数量</returns>
        public Int32 AddOrderDetail(OrderDetail orderDetail, SqlTransaction tran = null)
        {
            StringBuilder sb = new StringBuilder();
            SqlParameter parId = new SqlParameter("@Id", SqlDbType.Int);
            SqlParameter parOrderId = new SqlParameter("@OrderId", SqlDbType.Int);
            SqlParameter parName = new SqlParameter("@Name", SqlDbType.VarChar, 25);

            parId.Value = orderDetail.Id;
            parOrderId.Value = orderDetail.OrderId;
            parName.Value = orderDetail.Name;
            sb.Append(" insert into OrderDetail(Id,OrderId,Name) values(@Id,@OrderId,@Name)");

            if (tran == null)
                return SqlHelper.ExecuteNonQuery(connStr, CommandType.Text, sb.ToString(), parId, parOrderId, parName);
            return SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sb.ToString(), parId, parOrderId, parName);
        }

        /// <summary>
        /// 更新订单详细
        /// </summary>
        /// <param name="order">订单详细信息</param>
        /// <param name="tran">事务</param>
        /// <returns>受影响的数量</returns>
        public Int32 UpdateOrderDetail(OrderDetail orderDetail, SqlTransaction tran = null)
        {
            StringBuilder sb = new StringBuilder();
            SqlParameter parId = new SqlParameter("@Id", SqlDbType.Int);
            SqlParameter parOrderId = new SqlParameter("@OrderId", SqlDbType.Int);
            SqlParameter parName = new SqlParameter("@Name", SqlDbType.VarChar, 25);

            parId.Value = orderDetail.Id;
            parOrderId.Value = orderDetail.OrderId;
            parName.Value = orderDetail.Name;
            sb.Append(" update OrderDetail set Name=@Name,OrderId=@OrderId  where Id=@id ");

            if (tran == null)
                return SqlHelper.ExecuteNonQuery(connStr, CommandType.Text, sb.ToString(), parId, parOrderId, parName);
            return SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sb.ToString(), parId, parOrderId, parName);
        }
    }

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

    /// <summary>
    /// 事务类
    /// </summary>
    public class SqlTran
    {
        /// <summary>
        /// 执行事务（单一方法）
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="method">要执行的方法（SqlTransaction 默认传入为null）</param>
        /// <param name="obj1">参数值</param>
        /// <returns></returns>
        public static Int32 ExecuteTran<T>(Func<T, SqlTransaction, Int32> method, T obj1)
            where T : new()
        {
            Int32 count = 0;
            SqlConnection conn = null;
            SqlTransaction tran = null;
            try
            {
                conn = new SqlConnection(Repository.connStr);
                conn.Open();
                tran = conn.BeginTransaction();

                count += method(obj1, tran);          //执行方法

                tran.Commit();
                return count;
            }
            catch (Exception ex)
            {
                tran.Rollback();
                return -1;
            }
            finally
            {
                if (tran != null)
                    tran.Dispose();
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }

        }

        /// <summary>
        /// 执行事务（事务中存在两个方法）
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="method">要执行的方法（SqlTransaction 默认传入为null）</param>
        /// <param name="obj1">参数值</param>
        /// <returns></returns>
        public static Int32 ExecuteTran<T>(Func<T, SqlTransaction, Int32> method1, Func<T, SqlTransaction, Int32> method2, T obj1, T obj2)
            where T : new()
        {
            Int32 count = 0;
            SqlConnection conn = null;
            SqlTransaction tran = null;
            try
            {
                conn = new SqlConnection(Repository.connStr);
                conn.Open();
                tran = conn.BeginTransaction();

                count += method1(obj1, tran);
                count += method2(obj2, tran);

                tran.Commit();
                return count;
            }
            catch (Exception ex)
            {
                tran.Rollback();
                return -1;
            }
            finally
            {
                if (tran != null)
                    tran.Dispose();
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }

        }

        /// <summary>
        /// 执行事务(同实体事务执行【方法不一定相同】)
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="method">要执行的方法（SqlTransaction 默认传入为null）</param>
        /// <param name="obj1">参数值</param>
        /// <returns></returns>
        public static Int32 ExecuteTran<T>(IList<Func<T, SqlTransaction, Int32>> methods, IList<T> objs)
            where T : new()
        {
            Int32 count = 0;
            SqlConnection conn = null;
            SqlTransaction tran = null;
            try
            {
                conn = new SqlConnection(Repository.connStr);
                conn.Open();
                tran = conn.BeginTransaction();
                if (methods.Count() != objs.Count())
                    return -1;  //异常

                for (int i = 0; i < objs.Count; i++)
                    count += methods[i](objs[i], tran);

                tran.Commit();
                return count;
            }
            catch (Exception ex)
            {
                tran.Rollback();
                return -1;
            }
            finally
            {
                if (tran != null)
                    tran.Dispose();
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }

        }
    }

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

        public void Test()
        {
            Repository repository = new Repository();
            Orders order1 = new Orders() { Id = 1, Name = "name1" };
            Orders order2 = new Orders() { Id = 2, Name = "name2" };
            Orders order3 = new Orders() { Id = 3, Name = "name3" };
            Orders order4 = new Orders() { Id = 4, Name = "name4" };
            Orders order5 = new Orders() { Id = 5, Name = "name5" };
            OrderDetail orderDetail1 = new OrderDetail() { Id = 1, OrderId = 1, Name = "namedetail1" };
            OrderDetail orderDetail2 = new OrderDetail() { Id = 2, OrderId = 1, Name = "namedetail2" };


            SqlTran.ExecuteTran<Orders>(repository.AddOrder, order1);
            SqlTran.ExecuteTran<OrderDetail>(repository.AddOrderDetail, orderDetail1);   //泛型的好处，可以少写代码

            SqlTran.ExecuteTran<Orders>(repository.AddOrder, repository.AddOrder, order2, order3);   //同方法，同实体类型
            order1.Name = "orderName1update";
            SqlTran.ExecuteTran<Orders>(repository.AddOrder, repository.UpdateOrder, order4, order1); //不同方法，同实体类型

            List<Func<Orders, SqlTransaction, Int32>> list = new List<Func<Orders, SqlTransaction, Int32>>();   //多方法(混合更新和添加)
            List<Orders> listObj = new List<Orders>();
            list.Add(repository.UpdateOrder);
            order1.Name = "orderName1updatet";
            listObj.Add(order1);
            list.Add(repository.AddOrder);
            listObj.Add(order5);
            SqlTran.ExecuteTran<Orders>(list, listObj);
        }

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