C#數據庫事務處理

前言：

      通常SqlHelper類為了方便處理，做成了靜態類，靜態類的問題是不方便添加事務處理。

      實例化類方便添加事務處理，DoTrans/CommitTrans/RollBackTrans  三個函數

說明：

1：ExecuteNonQuery執行多條SQL語句，默認包含事務。

實際執行代碼：

SqlServerInfo ssi = new SqlServerInfo();
            string strSql="UPDATE dbo.Test SET testname='2321' WHERE testid=1;UPDATE dbo.TestCustorm SET TestNickName='Nick2321' WHERE testid=1;";
            //string strsql1 = "UPDATE dbo.TestCustorm SET TestNickName='Nick2321' WHERE testid=1;";
            //string strsql2 = "UPDATE dbo.Test SET testname='2321' WHERE testid=1";
            int i=ssi.ExecuteNonQuerySqlTextWithNoTrans(strSql);
            if(i>0)
            {
                Response.Write("執行成功");
            }
            else
            {
                Response.Write("執行失敗");
            }

SQL執行代碼：

public class SqlServerInfo
    {
        private string _SqlConnectionString = "Data Source=(local);Initial Catalog=test;User ID=sa;Password=sasa;";
        public string SqlConnectionString
        {
            get
            {
                return _SqlConnectionString;
            }
            set
            {
                _SqlConnectionString = value;
            }
        }

        /// <summary>
        /// 執行sql語句並返回受影響行數
        /// </summary>
        /// <param name="cmdText">sql語句</param>
        /// <returns></returns>
        public int ExecuteNonQuerySqlTextWithTrans(string cmdText)
        {
            int num2=0;
            SqlConnection connection = new SqlConnection(_SqlConnectionString);
            connection.Open();
            SqlCommand cmd = new SqlCommand();
            SqlTransaction sTran = connection.BeginTransaction();
            try
            {
                PrepareCommand(cmd, connection, sTran, CommandType.Text, cmdText, null);
                num2 = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                sTran.Commit();
                connection.Close();

            }
            catch (Exception ex)
            {
                //LogHelper log = new LogHelper();
                //log.WriteLog("
2：分割執行包含事務
執行語句：

protected void Button1_Click(object sender, EventArgs e)
        {
            SqlServerInfo ssi = new SqlServerInfo();
            int iSeed = 3;
            Random ran = new Random(iSeed);
            int RandKey=ran.Next(100,999);
            string strSql = "UPDATE dbo.Test SET testname='" + RandKey.ToString() + "' WHERE testid=1;UPDATE dbo.TestCustorm SET TestNickName1='Nick" + RandKey.ToString() + "' WHERE testid=1";
            //string strsql1 = "UPDATE dbo.TestCustorm SET TestNickName='Nick2321' WHERE testid=1;";
            //string strsql2 = "UPDATE dbo.Test SET testname='2321' WHERE testid=1";
            int i=ssi.ExecuteNonQuerySqlTextWithNoTrans(strSql);
            if(i>0)
            {
                Response.Write("執行成功");
            }
            else
            {
                Response.Write("執行失敗");
            }

        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            SqlServerInfo ssi = new SqlServerInfo();
            int iSeed = 4;
            Random ran = new Random(iSeed);
            int RandKey = ran.Next(1000, 9999);
            string strSql = "UPDATE dbo.Test SET testname='" + RandKey.ToString() + "' WHERE testid=1;UPDATE dbo.TestCustorm SET TestNickName1='Nick" + RandKey.ToString() + "' WHERE testid=1";
            //string strsql1 = "UPDATE dbo.TestCustorm SET TestNickName='Nick2321' WHERE testid=1;";
            //string strsql2 = "UPDATE dbo.Test SET testname='2321' WHERE testid=1";
            int i = ssi.ExecuteNonQuerySqlTextWithTrans(strSql);
            if (i > 0)
            {
                Response.Write("執行成功");
            }
            else
            {
                Response.Write("執行失敗");
            }

        }

處理代碼：

public class SqlServerInfo
    {
        private string _SqlConnectionString = "Data Source=(local);Initial Catalog=test;User ID=sa;Password=sasa;";
        public string SqlConnectionString
        {
            get
            {
                return _SqlConnectionString;
            }
            set
            {
                _SqlConnectionString = value;
            }
        }

        /// <summary>
        /// 執行sql語句並返回受影響行數
        /// </summary>
        /// <param name="cmdText">sql語句</param>
        /// <returns></returns>
        public int ExecuteNonQuerySqlTextWithTrans(string cmdText)
        {
            int num2=0;
            SqlConnection connection = new SqlConnection(_SqlConnectionString);
            connection.Open();
            SqlCommand cmd = new SqlCommand();
            SqlTransaction sTran = connection.BeginTransaction();
            try
            {
                string[] sqlContexts= cmdText.Split(';');
                foreach(string sql in sqlContexts)
                {
                    PrepareCommand(cmd, connection, sTran, CommandType.Text, cmdText, null);
                    num2 = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
                sTran.Commit();
                connection.Close();

            }
            catch (Exception ex)
            {
                //LogHelper log = new LogHelper();
                //log.WriteLog("