Ｃ＃批量执行ＳSql语句，实现数据库事务

示例，通过自定义Command对象来储存sql命令和防sql注入参数

   public int BatchAdd(List<Public_Equipment> ListInfo)
        {
            int result = 0;
            try
            {
                List<CommandInfo> execs = new List<CommandInfo>();
                StringBuilder strsql = new StringBuilder();
                strsql.Append("insert into Public_Equipment(DeviceName,DeviceAddress,RegisterAddress,RegisterType,DeviceType,DeviceIP,FloorId,CreateTime)");
                strsql.Append("values(@DeviceName,@DeviceAddress,@RegisterAddress,@RegisterType,@DeviceType,@DeviceIP,@FloorId,@CreateTime)");
                if (ListInfo != null && ListInfo.Count > 0)
                {
                    for (int i = 0; i < ListInfo.Count; i++)
                    {
                        MySqlParameter[] parameAdd = { 
                                                new MySqlParameter("@DeviceName",MySqlDbType.VarChar),
                                                new MySqlParameter("@DeviceAddress",MySqlDbType.VarChar),
                                                new MySqlParameter("@RegisterAddress",MySqlDbType.Int32),
                                                new MySqlParameter("@RegisterType",MySqlDbType.VarChar),
                                                new MySqlParameter("@DeviceType",MySqlDbType.Int32),
                                                new MySqlParameter("@DeviceIP",MySqlDbType.VarChar),
                                                new MySqlParameter("@FloorId",MySqlDbType.Int32),
                                                new MySqlParameter("@CreateTime",MySqlDbType.VarChar)
                                                };
                        parameAdd[0].Value = ListInfo[i].DeviceName;
                        parameAdd[1].Value = ListInfo[i].DeviceAddress;
                        parameAdd[2].Value = ListInfo[i].RegisterAddress;
                        parameAdd[3].Value = ListInfo[i].RegisterType;
                        parameAdd[4].Value = ListInfo[i].DeviceType;
                        parameAdd[5].Value = ListInfo[i].DeviceIP;
                        parameAdd[6].Value = ListInfo[i].FloorId;
                        parameAdd[7].Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                        CommandInfo cmd = new CommandInfo();
                        cmd.StrSql = strsql.ToString();
                        cmd.MyParameters = parameAdd;
                        execs.Add(cmd);
                    }
                    result = MyDBObject.ExecuteSqlTran(execs);
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteError(ex.Message, "云溪别院-公共设备", "添加公共设备");
            }
            return result;
        }

cmmandInfo 类的参数

   public class CommandInfo
    {
        private string _strSql;
        private SqlParameter[] _parameters;
        private MySqlParameter[] _MyParameters;
        /// <summary>
        /// Sql字符串
        /// </summary>
        public string StrSql
        {
            get { return _strSql; }
            set { _strSql = value; }
        }
        /// <summary>
        /// sql参数
        /// </summary>
        public SqlParameter[] Parameters
        {
            get { return _parameters; }
            set { _parameters = value; }
        }

        /// <summary>
        /// sql参数
        /// </summary>
        public MySqlParameter[] MyParameters
        {
            get { return _MyParameters; }
            set { _MyParameters = value; }
        }
    }

执行

       /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
        public static int ExecuteSqlTran(List<CommandInfo> SQLStringList)
        {
            int result = 0;
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                using (MySqlTransaction trans = conn.BeginTransaction())
                {
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.Transaction = trans;
                    try
                    {
                        cmd.Connection = conn;
                        cmd.CommandType = CommandType.Text;
                        foreach (CommandInfo info in SQLStringList)
                        {
                            cmd.CommandText = info.StrSql;
                            if (info.MyParameters != null)
                            {
                                cmd.Parameters.AddRange(info.MyParameters);
                            }
                            result += cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        result = 0;
                        trans.Rollback();
                        throw;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            return result;
        }