C#一次连接数据库执行多条sql语句(三种方法)

第一种方法:

   string str="server=.;uid=sa;pwd=111111;database=text_db";//连接字符串

            SqlConnection SCON = null;//连接对象
            SqlCommand SCom=new SqlCommand();//命令对象
            SCON=new SqlConnection(str);
            SCON.Open();
            SCom.CommandText = "insert into cum(nm,age,sex,addr) values(‘”+name1+”’, ‘”+age1+”’, ‘”+sex1”’,’”+addr1+”’); insert into cum(nm,age,sex,addr) values(‘”+name1+”’, ‘”+age1+”’, ‘”+sex1”’,’”+addr1+”’)";
            SCom.Connection = SCON;
            SCom.CommandType = CommandType.Text;
            int num = SCom.ExecuteNonQuery();
            SCON.Close();

 注意：这就是用分号隔开，把多条sql语句写成了一条sql语句来执行，因为没有用参数，可能有sql注入风险。

 

第二种方法:

string str="server=.;uid=sa;pwd=111111;database=text_db";//连接字符串

            SqlConnection SCON;
            SqlCommand SCom1=new SqlCommand();//要执行sql语句的第一个命令对象
            SqlCommand SCom2=new SqlCommand();//要执行sql语句的第二个命令对象
            SCON=new SqlConnection(str);
            SCON.Open();

            //第一条要执行的sql语句
            SCom1.CommandText = "insert into cum(nm,age,sex,addr) values(@name,@age,@sex,@addr)";
            SCom1.Parameters.Add("@name", SqlDbType.Text);
            SCom1.Parameters.Add("@age", SqlDbType.Int);
            SCom1.Parameters.Add("@sex", SqlDbType.Bit);
            SCom1.Parameters.Add("@addr", SqlDbType.Text);
            SCom1.Parameters["@name"].Value = name1;
            SCom1.Parameters["@age"].Value = age1;
            SCom1.Parameters["@sex"].Value = sex1;
            SCom1.Parameters["@addr"].Value = addr1;
            SCom1.Connection = SCON;
            SCom1.CommandType = CommandType.Text;
            int num = SCom1.ExecuteNonQuery();

            //第二条要执行行的sql语句
            SCom2.CommandText = "insert into cum(nm,age,sex,addr) values(@name,@age,@sex,@addr)";
            SCom2.Parameters.Add("@name", SqlDbType.Text);
            SCom2.Parameters.Add("@age", SqlDbType.Int);
            SCom2.Parameters.Add("@sex", SqlDbType.Bit);
            SCom2.Parameters.Add("@addr", SqlDbType.Text);
            SCom2.Parameters["@name"].Value = name2;
            SCom2.Parameters["@age"].Value = age2;
            SCom2.Parameters["@sex"].Value = sex2;
            SCom2.Parameters["@addr"].Value = addr2;
            SCom2.Connection = SCON;
            SCom2.CommandType = CommandType.Text;
            int num = SCom2.ExecuteNonQuery();
            SCON.Close();

这种方法就是利用了一个SqlConnection对象进行一次连接，在关闭前执行多个SqlCommand命令对象，执行完后再关闭连接。

 

第三种方法：

 //这里用事务来执行多条语句

        public string InsertAll(string[] cmdtext)
        {
            //打开连接
            Open();
            DbTransaction myTrans;
            DbCommand mycmd;
            if (IsAccess())
            {
                 mycmd =(OleDbCommand) Sqlcon.CreateCommand();
            }
            else
            {
                 mycmd =(SqlCommand) Sqlcon.CreateCommand();
            }

            myTrans = Sqlcon.BeginTransaction(IsolationLevel.ReadCommitted);
            mycmd.Connection = Sqlcon;
            mycmd.Transaction = myTrans;
            try
            {
                for (int i = 0; i < cmdtext.Length; i++)
                {
                    mycmd.CommandText = cmdtext[i];
                    mycmd.ExecuteNonQuery();
                }
                myTrans.Commit();
                return "1";
            }
            catch (Exception)
            {
                if (myTrans != null)
                    myTrans.Rollback();
                return "0";
            }
            finally
            {
                //关闭连接
                //Sqlcon.Close();
                if (Sqlcon != null)
                    Close();
            }
        }

https://blog.csdn.net/dgwind/article/details/5337688