C# 执行多条SQL语句，实现数据库事务(通过Hashtable存储数据) .

1)Web.config中添加连接数据库的语句

<add key="ConnectionString" value="server=./sqlexpress;database=transactionDemo;uid=sa;pwd=123456"/>

 

2)在表现层Default.aspx.cs中添加多条需要插入数据库的数据。

public void Add()
        {
            List<Maticsoft.Model.ClassDemo> modellist = new List<Maticsoft.Model.ClassDemo>();
            modellist.Add(new Maticsoft.Model.ClassDemo("张三1", "浙江1"));
            modellist.Add(new Maticsoft.Model.ClassDemo("张三2", "浙江2"));
            modellist.Add(new Maticsoft.Model.ClassDemo("张三3张三3张三3张三3", "浙江3"));
            bll.Add(modellist);
        }

3)在业务逻辑层ClassDemo.cs中添加业务逻辑

        public void Add(List<Maticsoft.Model.ClassDemo> modellist)
        {
            dal.Add(modellist);
        }

4）在数据访问层ClassDemo.cs、DbHelperSQL.cs中添加将数据插入数据库

/// <summary>
        /// 通过事务添加多条数据
        /// </summary>
        /// <param name="modellist"></param>
        public void Add(List<Maticsoft.Model.ClassDemo> modellist)
        {
            Hashtable SQLStringList = new Hashtable();
            for (int i = 0; i < modellist.Count;i++ )
            {
                SqlParameter[] parameters = {
     new SqlParameter(string.Format("@name{0}",i), SqlDbType.NVarChar,50),
     new SqlParameter(string.Format("@address{0}",i), SqlDbType.NVarChar,50)};
                parameters[0].Value = modellist[i].name;
                parameters[1].Value = modellist[i].address;
                SQLStringList.Add(string.Format("insert into ClassDemo (name,address) values (@name{0},@address{0})", i), parameters);
            }
            DbHelperSQL.ExecuteSqlTran(SQLStringList);
        }

 

 public static string connectionString = ConfigurationManager.AppSettings["ConnectionString"].ToString();  

/// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
        public static void ExecuteSqlTran(Hashtable SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                    }
                    catch(Exception ex)
                    {
                        trans.Rollback();
                    }
                }
            }
        }

一个例子：

Hashtable SQLStringList = new Hashtable(); 
string cmdText = "insert into userinfo (username,userpwd)values(@username,@userpwd) "; 
SqlParameter[] cmdParms = new SqlParameter[2]; 
cmdParms[0] = new SqlParameter( "@username ", "name "); 
cmdParms[1] = new SqlParameter( "@userpwd ", "pwd "); 
....
SQLStringList.Add(cmdText, cmdParms); 
ExecuteSqlTran(SQLStringList);