C# 执行带参数的事务方法

我们在写程序时，需要用到事务处理多条语句的情况，但我们还要防止语句中注入式攻击，所以写了这个方法，进行带参数的事务的处理。
下表代码说明：
　　首先将一条语句的参数放在结构sttSqlParameterList中，这样便于知道参数名及其对应值。
　　在将语句及参数列表放在结构sttSqlList中，这样便于知道SQL语句及其对应的参数列表。
　　最后调用ExecParameterTransaction（）执行事务。其传入的是sttSqlList列表。

        /// <summary>
        /// sql语句及参数列表(存放要执行Sql语句及其对应的参数)
        /// </summary>
        public struct sttSqlList
        {
            private string sSQl;
            /// <summary>
            /// SQl语句
            /// </summary>
            public string atbSQl
            {
                get
                {
                    return sSQl;
                }
                set
                {
                    sSQl = value;
                }
            }
            sttSqlParameterList[] splList;
            /// <summary>
            /// 参数列表
            /// </summary>
            public sttSqlParameterList[] atbSqlPraList
            {
                get
                {
                    return splList;
                }
                set
                {
                    splList = value;
                }
            }
        }


        /// <summary>
        /// 参数列表，用于存放参数
        /// </summary>
        public struct sttSqlParameterList
        {
            private string sName;
            private string sType;
            private string sValue;

            /// <summary>
            /// 构造函数(初始化值)
            /// </summary>
            /// <param name="asName">参数名</param>
            /// <param name="asType">参数类型(暂时只针对string型的值)</param>
            /// <param name="asValue">参数值</param>
            public sttSqlParameterList(string asName, string asType, string asValue)
            {
                sName = asName;
                sType = asType;
                sValue = asValue;
            }

            /// <summary>
            /// 参数名(与SQL语句中的参数名相同,但不带:号)
            /// </summary>
            public string Name
            {
                get
                {
                    return sName;
                }
                set
                {
                    sName = value;
                }
            }

            /// <summary>
            /// 参数类型(本参数暂时只接受string型)
            /// </summary>
            public string Type
            {
                get
                {
                    return sType;
                }
                set
                {
                    sType = value;
                }
            }

            /// <summary>
            /// 参数值,与参数类型对应
            /// </summary>
            public string Value
            {
                get
                {
                    return sValue;
                }
                set
                {
                    sValue = value;
                }
            }
        }

        /// <summary>
        /// 执行事务(传入语句列表)
        /// </summary>
        /// <param name="sSqlList">一次执行的多条语句列表</param>
        public static int ExecParameterTransaction(List<sttSqlList> aSqlList)
        {
            //影响的值
            int iExecCou = 0;
            string sConnectionString = ConfigurationManager.AppSettings.Get("UserConnection");
            OracleConnection ocConnection = new OracleConnection(sConnectionString);
            ocConnection.Open();
            //创建并开启事务
            using (OracleTransaction oraTrans = ocConnection.BeginTransaction(IsolationLevel.ReadCommitted))
            {
                OracleCommand ocCommand = ocConnection.CreateCommand();
                ocCommand.Transaction = oraTrans;
                try
                {
                    //执行多表SQL
                    foreach (sttSqlList sttItemSql in aSqlList)
                    {
                        //清除参数(为执行下一条语句作准备)
                        ocCommand.Parameters.Clear();
                        //要执行的语句
                        ocCommand.CommandText = sttItemSql.atbSQl; 
                        sttSqlParameterList[] sttPraList = sttItemSql.atbSqlPraList;
                        for (int i = 0; i < sttPraList.Length; i++)
                        {
                            OracleParameter opName = new OracleParameter();
                            opName.ParameterName = sttPraList[i].Name;
                            switch (sttPraList[i].Type)
                            {
                                case "string":
                                    opName.OracleType = OracleType.VarChar;
                                    break;
                                default:
                                    opName.OracleType = OracleType.VarChar;
                                    break;
                            }
                            opName.Value = sttPraList[i].Value;
                            //增加参数
                            ocCommand.Parameters.Add(opName);
                        }
                        //执行
                        iExecCou += ocCommand.ExecuteNonQuery();
                    }
                   
                    //完成后确认事务
                    oraTrans.Commit();
                }
                catch (Exception ex)
                {
                    //出错后回滚事务
                    oraTrans.Rollback();
                    throw ex;
                }
                finally
                {
                    ocCommand.Dispose();
                    ocConnection.Close();
                }
            }
            return iExecCou;
        }

用法代码：

        /// 保存资料
        /// </summary>
        public int funSave()
        {
            //影响的行数
            int iExecCou = 0;
            string sSQl = "";
            //key
            string sKey = DateTime.Now.ToString("yyyyMMddHHmmssffffff");
　　　　  //将被执行的语句1
            string sSql = @"insert into hp_accountapply(aayid,aayinputerid,aayname,aaytel,aayImputerTel,aaytime,aayitemid,aayreason)
                values('{0}','{1}',:aayname,:aaytel,:aayImputerTel,to_date('{2}','yyyy-MM-dd HH24:mi'),'{3}',:aayreason)";
            sSql = string.Format(sSql, sKey, atbInputEmp, atbTime, sKey);

            //语句1参数列表
            ClbHpDAL.clsDbManage.sttSqlParameterList[] sPraList = new ClbHpDAL.clsDbManage.sttSqlParameterList[4];
            //申请人姓名
            sPraList[0].Name = "aayname";
            sPraList[0].Type = "string";
            sPraList[0].Value = atbAypName;
            //申请人电话
            sPraList[1].Name = "aaytel";
            sPraList[1].Type = "string";
            sPraList[1].Value = atbApyTel;
            //填写人电话
            sPraList[2].Name = "aayImputerTel";
            sPraList[2].Type = "string";
            sPraList[2].Value = atbInputTel;
            //原因
            sPraList[3].Name = "aayreason";
            sPraList[3].Type = "string";
            sPraList[3].Value = atbReason;

            //语句1及其参数列表
            ClbHpDAL.clsDbManage.sttSqlList ssLInfo = new ClbHpDAL.clsDbManage.sttSqlList();
            ssLInfo.atbSQl = sSql;
            ssLInfo.atbSqlPraList = sPraList;

            //最终要执行的参数语句列表
            List<ClbHpDAL.clsDbManage.sttSqlList> lParameterSqlList = new List<ClbHpDAL.clsDbManage.sttSqlList>();
            //增加语句1及其参数
            lParameterSqlList.Add(ssLInfo);


           
            string sItemSql = @"insert into hp_accountitem(aimid,aimname,aimvalue,aimtype)
                        values('{0}',:aimname,:aimvalue,{1})";
            //域账户不为空
            if (!string.IsNullOrEmpty(atbAd))
            {
                string sPartSql = String.Format(sItemSql, sKey, 1);
                ClbHpDAL.clsDbManage.sttSqlParameterList[] sPraAd = new ClbHpDAL.clsDbManage.sttSqlParameterList[3];
                sPraAd[0].Name = "aimname";
                sPraAd[0].Value = atbAypName;
                sPraAd[0].Type = "string";

                sPraAd[1].Name = "aimvalue";
                sPraAd[1].Value = atbAd;
                sPraAd[1].Type = "string";
　　　　　　//语句2及其参数列表
                ClbHpDAL.clsDbManage.sttSqlList sslAd = new ClbHpDAL.clsDbManage.sttSqlList();
                sslAd.atbSQl = sPartSql;
                sslAd.atbSqlPraList = sPraAd;
                //增加语句2及其参数列表
                lParameterSqlList.Add(sslAd);
            }
            if (!string.IsNullOrEmpty(atbMail))
            {
                string sPartSql = String.Format(sItemSql, sKey, 2);
                ClbHpDAL.clsDbManage.sttSqlParameterList[] sPraMail = new ClbHpDAL.clsDbManage.sttSqlParameterList[3];
                sPraMail[0].Name = "aimname";
                sPraMail[0].Value = atbAypName;
                sPraMail[0].Type = "string";

                sPraMail[1].Name = "aimvalue";
                sPraMail[1].Value = atbMail;
                sPraMail[1].Type = "string";

　　　　　　//语句3及其参数列表
                ClbHpDAL.clsDbManage.sttSqlList sslMail = new ClbHpDAL.clsDbManage.sttSqlList();
                sslMail.atbSQl = sPartSql;
                sslMail.atbSqlPraList = sPraMail;
                //增加语句3及其参数列表
                lParameterSqlList.Add(sslMail);
            }     
　　　　 //执行事务(传入多条SQL)
            iExecCou = ExecParameterTransaction(lParameterSqlList);
            return iExecCou;
        }