sql事务

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using ZJS.DBUtility;

namespace DBUtility
{
    public class SqlHelpDxq
    {
        /// <summary>
        /// 数据库表名
        /// </summary>
        public string TableName { get; set; }

        /// <summary>
        /// 带事务的批量增删改功能
        /// </summary>
        /// <param name="list">要处理的集合对象</param>
        /// <param name="NoRow">如果受影响的行数小于1则回滚，否则提交。默认不回滚</param>
        /// <returns>返回每个处理结果的结果集</returns>
        public List<int> SaveChange(List<object> list, bool NoRow = false)
        {
            List<int> listResult = new List<int>();
            using (SqlConnection connection = new SqlConnection(DbHelperSQL.connectionString))
            {
                int i = 0;
                OperSqlType _idus;
                string strWhere;
                string _tab;
                List<SqlPa> listSp;
                List<SqlParameter> paralist;
                object Value;
                Type _tt;
                SqlCommand cmd = new SqlCommand();
                StringBuilder strSql = new StringBuilder();
                StringBuilder column = new StringBuilder();//列字段
                StringBuilder columnvalue = new StringBuilder();//列字段对应的值

                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {                    
                    try
                    {
                        foreach (var item in list)
                        {
                            strSql.Clear();
                            column.Clear();
                            columnvalue.Clear();
                            Value = null;
                            _idus = OperSqlType.None;
                            strWhere = string.Empty;
                            _tab = string.Empty;
                            listSp = null;
                            paralist = new List<SqlParameter>();//Parameter参数                            

                            _tt = item.GetType();
                            PropertyInfo[] piList = _tt.GetProperties();
                            foreach (PropertyInfo info in piList)
                            {
                                Value = info.GetValue(item, null); //获取属性值
                                if (Value == null)
                                {
                                    continue;
                                }
                                if (info.Name == "t_IDUS")
                                {
                                    _idus = (OperSqlType)Value;
                                }                                
                                else if (info.Name == "t_Where")
                                {
                                    strWhere = Value.ToString();
                                }
                                else if (info.Name == "listSp")
                                {
                                    listSp = Value as List<SqlPa>;
                                }
                                else if (info.Name == "TableName")
                                {
                                    _tab = Value.ToString();
                                }
                                if (_idus == OperSqlType.Delete && !string.IsNullOrEmpty(strWhere) && listSp != null && !string.IsNullOrEmpty(_tab))
                                {
                                    break;
                                }
                                SqlField[] field = info.GetCustomAttributes(typeof(SqlField), false) as SqlField[];
                                if (field == null || field.Length < 1)
                                {
                                    continue;
                                }

                                if (_idus == OperSqlType.Insert)
                                {
                                    column.AppendFormat(",{0}", info.Name);
                                    columnvalue.AppendFormat(",@{0}", info.Name);//添加字段
                                }
                                else if (_idus == OperSqlType.Update)
                                {
                                    column.AppendFormat(",{0}=@{0}", info.Name);//添加字段
                                }

                                SqlParameter para = new SqlParameter(info.Name, field[0].SqlDbTypeName);
                                if (field[0].Length > 0)
                                    para.Size = field[0].Length;//设置SqlParameter

                                para.Value = Value;
                                paralist.Add(para);

                            }

                            //SQL语句                                                        
                            if (_idus == OperSqlType.Insert)
                            {
                                //                                                     SELECT @@IDENTITY;
                                strSql.AppendFormat("INSERT INTO {0}({1}) VALUES ({2});SELECT SCOPE_IDENTITY();", _tab, Regex.Replace(column.ToString(), "^,", ""), Regex.Replace(columnvalue.ToString(), "^,", ""));
                            }
                            else if (_idus == OperSqlType.Update)
                            {
                                strSql.AppendFormat("UPDATE {0} SET ", _tab);
                                strSql.Append(Regex.Replace(column.ToString(), "^,", ""));
                                if (!string.IsNullOrEmpty(strWhere))
                                    strSql.AppendFormat(" WHERE {0}", strWhere);
                            }
                            else if (_idus == OperSqlType.Delete)
                            {
                                strSql.AppendFormat("DELETE FROM {0}", _tab);
                                if (!string.IsNullOrEmpty(strWhere))
                                {
                                    strSql.Append(" WHERE " + strWhere);
                                }
                            }
                            if (_idus == OperSqlType.Update || _idus == OperSqlType.Delete)
                            {
                                if (listSp != null && listSp.Count > 0)
                                {
                                    foreach (var itemSp in listSp)
                                    {
                                        SqlParameter para = new SqlParameter(itemSp._sqlcolumn, itemSp._sqldbtypename);
                                        if (itemSp._length > 0)
                                            para.Size = itemSp._length;//设置SqlParameter

                                        para.Value = itemSp._value;
                                        paralist.Add(para);
                                    }
                                }
                            }


                            DbHelperSQL.PrepareCommand(cmd, connection, transaction, strSql.ToString(), paralist.ToArray());
                            if (_idus == OperSqlType.Insert)
                            {
                                object objA = cmd.ExecuteScalar();
                                cmd.Parameters.Clear();
                                if (objA == null)
                                {
                                    i = 0;
                                }
                                else
                                {
                                    i = Convert.ToInt32(objA);
                                }
                            }
                            else if (_idus == OperSqlType.Update || _idus == OperSqlType.Delete)
                            {
                                i = cmd.ExecuteNonQuery();
                                cmd.Parameters.Clear();
                                if (i < 1 && NoRow)
                                {
                                    throw new Exception("无受影响的行数");
                                }
                            }
                            listResult.Add(i);
                        }
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                    finally
                    {
                        if (cmd != null)
                        {
                            cmd.Dispose();
                        }
                    }
                }
            }
            return listResult;
        }

    }
}

using System;
using System.Data;

namespace ZJS.DBUtility
{
/// <summary>
/// 操作参数信息
/// </summary>
public class SqlPa
{
/// <summary>
/// 列名
/// </summary>
public string _sqlcolumn { get; set; }
/// <summary>
/// 数据类型
/// </summary>
public SqlDbType _sqldbtypename { get; set; }
/// <summary>
/// 列名长度
/// </summary>
public int _length { get; set; }
/// <summary>
/// 列值
/// </summary>
public object _value { get; set; }
/// <summary>
/// 设置操作参数信息
/// </summary>
/// <param name="sqlcolumn"></param>
/// <param name="sqldbtypename"></param>
/// <param name="val"></param>
/// <param name="length"></param>
public SqlPa(string sqlcolumn, SqlDbType sqldbtypename, object val, int length)
{
_sqlcolumn = sqlcolumn;
_sqldbtypename = sqldbtypename;
_value = val;
_length = length;
}
/// <summary>
/// 设置操作参数信息
/// </summary>
/// <param name="sqlcolumn"></param>
/// <param name="sqldbtypename"></param>
/// <param name="val"></param>
/// <param name="length"></param>
public SqlPa(string sqlcolumn, SqlDbType sqldbtypename, object val)
{
_sqlcolumn = sqlcolumn;
_sqldbtypename = sqldbtypename;
_value = val;
}
}
/// <summary>
/// 操作类型
/// </summary>
public enum OperSqlType
{
/// <summary>
/// 插入操作
/// </summary>
Insert = 1,
/// <summary>
/// 更新操作
/// </summary>
Update = 2,
/// <summary>
/// 删除操作
/// </summary>
Delete = 3,
/// <summary>
/// 无操作
/// </summary>
None = 4
}
}

using System;
using System.Data;
using ZJS.DBUtility;

namespace Model
{
    public class Pay_Llp_Wap_UrlNotify : DBUtility.SqlHelpDxq
    {
        /// <summary>
        /// 操作类型
        /// </summary>
        public ZJS.DBUtility.OperSqlType t_IDUS { get; set; }
        /// <summary>
        /// 操作条件
        /// </summary>
        public string t_Where { get; set; }
        /// <summary>
        /// 操作参数
        /// </summary>
        public System.Collections.Generic.List<ZJS.DBUtility.SqlPa> listSp { get; set; }

        public Pay_Llp_Wap_UrlNotify()
        {
            TableName = "[dbo].[Pay_Llp_Wap_UrlNotify]";
        }

        [SqlField("Id", SqlDbType.Int, 4)]
        public int? Id { get; set; }
        [SqlField("no_order", SqlDbType.NVarChar, 32)]
        public string no_order { get; set; }
        [SqlField("oid_paybill", SqlDbType.NVarChar, 16)]
        public string oid_paybill { get; set; }
        [SqlField("dt_order", SqlDbType.NVarChar, 14)]
        public string dt_order { get; set; }
        [SqlField("money_order", SqlDbType.NVarChar, 12)]
        public string money_order { get; set; }
        [SqlField("oid_partner", SqlDbType.NVarChar, 18)]
        public string oid_partner { get; set; }
        [SqlField("result_pay", SqlDbType.NVarChar, 12)]
        public string result_pay { get; set; }
        [SqlField("pay_type", SqlDbType.NVarChar, 1)]
        public string pay_type { get; set; }
        [SqlField("id_type", SqlDbType.NVarChar, 2)]
        public string id_type { get; set; }
        [SqlField("id_no", SqlDbType.NVarChar, 32)]
        public string id_no { get; set; }
        [SqlField("acct_name", SqlDbType.NVarChar, 12)]
        public string acct_name { get; set; }
        [SqlField("card_no", SqlDbType.NVarChar, 19)]
        public string card_no { get; set; }
        [SqlField("bank_code", SqlDbType.NVarChar, 8)]
        public string bank_code { get; set; }
        [SqlField("settle_date", SqlDbType.NVarChar, 8)]
        public string settle_date { get; set; }
        [SqlField("no_agree", SqlDbType.NVarChar, 16)]
        public string no_agree { get; set; }
        [SqlField("info_order", SqlDbType.NVarChar, 255)]
        public string info_order { get; set; }
        [SqlField("sign_type", SqlDbType.NVarChar, 3)]
        public string sign_type { get; set; }
        [SqlField("sign", SqlDbType.NVarChar, 255)]
        public string sign { get; set; }
        [SqlField("AddTime", SqlDbType.DateTime, 8)]
        public System.DateTime? AddTime { get; set; }
        [SqlField("Ip", SqlDbType.NVarChar, 32)]
        public string Ip { get; set; }
    }
}
using System;
using System.Data;
using ZJS.DBUtility;

namespace Model
{
    public class Pay_Llp_Wap_UrlReturn : DBUtility.SqlHelpDxq
    {
        /// <summary>
        /// 操作类型
        /// </summary>
        public ZJS.DBUtility.OperSqlType t_IDUS { get; set; }
        /// <summary>
        /// 操作条件
        /// </summary>
        public string t_Where { get; set; }
        /// <summary>
        /// 操作参数
        /// </summary>
        public System.Collections.Generic.List<ZJS.DBUtility.SqlPa> listSp { get; set; }

        public Pay_Llp_Wap_UrlReturn()
        {
            base.TableName = "[dbo].[Pay_Llp_Wap_UrlReturn]";
        }

        [SqlField("Id", SqlDbType.Int, 4)]
        public int? Id { get; set; }
        [SqlField("no_order", SqlDbType.NVarChar, 32)]
        public string no_order { get; set; }
        [SqlField("oid_paybill", SqlDbType.NVarChar, 16)]
        public string oid_paybill { get; set; }
        [SqlField("money_order", SqlDbType.NVarChar, 12)]
        public string money_order { get; set; }
        [SqlField("dt_order", SqlDbType.NVarChar, 14)]
        public string dt_order { get; set; }
        [SqlField("oid_partner", SqlDbType.NVarChar, 18)]
        public string oid_partner { get; set; }
        [SqlField("result_pay", SqlDbType.NVarChar, 12)]
        public string result_pay { get; set; }
        [SqlField("settle_date", SqlDbType.NVarChar, 8)]
        public string settle_date { get; set; }
        [SqlField("sign_type", SqlDbType.NVarChar, 3)]
        public string sign_type { get; set; }
        [SqlField("sign", SqlDbType.NVarChar, 255)]
        public string sign { get; set; }
        [SqlField("AddTime", SqlDbType.DateTime, 8)]
        public System.DateTime? AddTime { get; set; }
        [SqlField("Ip", SqlDbType.NVarChar, 32)]
        public string Ip { get; set; }
    }
}

DateTime cTime = DateTime.Now;

            List<object> list = new List<object>();

            Model.Pay_Llp_Wap_UrlNotify modelNotify = new Model.Pay_Llp_Wap_UrlNotify();
            modelNotify.t_IDUS = ZJS.DBUtility.OperSqlType.Insert;
            modelNotify.acct_name = "acct_name";
            modelNotify.AddTime = cTime;
            modelNotify.bank_code = "bank_code";
            modelNotify.card_no = "card_no";
            modelNotify.dt_order = "dt_order";
            modelNotify.id_no = "id_no";
            modelNotify.id_type = "1";
            modelNotify.info_order = "info_order";
            modelNotify.Ip = "Ip";
            modelNotify.money_order = "money_order";
            modelNotify.no_agree = "no_agree";
            modelNotify.no_order = "no_order";
            modelNotify.oid_partner = "oid_partner";
            modelNotify.oid_paybill = "oid_paybill";
            modelNotify.pay_type = "pay_type";
            modelNotify.result_pay = "result_pay";
            modelNotify.settle_date = "settle_date";
            modelNotify.sign = "sign";
            modelNotify.sign_type = "sign_type";

            list.Add(modelNotify);
            
            Model.Pay_Llp_Wap_UrlReturn modelReturn = new Model.Pay_Llp_Wap_UrlReturn();
            modelReturn.t_IDUS = ZJS.DBUtility.OperSqlType.Update;
            List<ZJS.DBUtility.SqlPa> listSp = new List<ZJS.DBUtility.SqlPa>();
            ZJS.DBUtility.SqlPa sp = new ZJS.DBUtility.SqlPa("Id", System.Data.SqlDbType.Int, 1);
            listSp.Add(sp);
            modelReturn.listSp = listSp;
            modelReturn.t_Where = "Id=@Id";
            modelReturn.money_order = "金额";
            modelReturn.result_pay = "结果";


            list.Add(modelReturn);


            modelNotify = new Model.Pay_Llp_Wap_UrlNotify();
            modelNotify.t_IDUS = ZJS.DBUtility.OperSqlType.Delete;
            listSp = new List<ZJS.DBUtility.SqlPa>();
            sp = new ZJS.DBUtility.SqlPa("Id", System.Data.SqlDbType.Int, 3);
            listSp.Add(sp);
            modelNotify.listSp = listSp;
            modelNotify.t_Where = "Id=@Id";

            list.Add(modelNotify);

            DBUtility.SqlHelpDxq db = new DBUtility.SqlHelpDxq();
            db.SaveChange(list);