前言：
通常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("时间:" + DateTime.Now.ToString() + "----错误消息：" + ex.Message); sTran.Rollback(); } return num2; } /// <summary> /// 执行sql语句并返回受影响行数 /// </summary> /// <param name="cmdText">sql语句</param> /// <returns></returns> public int ExecuteNonQuerySqlTextWithNoTrans(string cmdText) { int num2 = 0; SqlConnection connection = new SqlConnection(_SqlConnectionString); connection.Open(); SqlCommand cmd = new SqlCommand(); try { PrepareCommand(cmd, connection, null, CommandType.Text, cmdText, null); num2 = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); connection.Close(); } catch (Exception ex) { } return num2; } /// <summary> /// Command准备 /// </summary> /// <param name="cmd"></param> /// <param name="conn"></param> /// <param name="isOpenTrans"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> private void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction isOpenTrans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) { if (conn.State != ConnectionState.Open) { conn.Open(); } cmd.Connection = conn; cmd.CommandText = cmdText; if (isOpenTrans != null) { cmd.Transaction = isOpenTrans; } cmd.CommandType = cmdType; if (cmdParms != null) { cmd.Parameters.AddRange(cmdParms); } } }
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("时间:" + DateTime.Now.ToString() + "----错误消息：" + ex.Message); sTran.Rollback(); } return num2; } /// <summary> /// 执行sql语句并返回受影响行数 /// </summary> /// <param name="cmdText">sql语句</param> /// <returns></returns> public int ExecuteNonQuerySqlTextWithNoTrans(string cmdText) { int num2 = 0; SqlConnection connection = new SqlConnection(_SqlConnectionString); connection.Open(); SqlCommand cmd = new SqlCommand(); try { string[] sqlContexts= cmdText.Split(';'); foreach(string sql in sqlContexts) { PrepareCommand(cmd, connection, null, CommandType.Text, sql, null); num2 = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } connection.Close(); } catch (Exception ex) { } return num2; } /// <summary> /// Command准备 /// </summary> /// <param name="cmd"></param> /// <param name="conn"></param> /// <param name="isOpenTrans"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> private void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction isOpenTrans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) { if (conn.State != ConnectionState.Open) { conn.Open(); } cmd.Connection = conn; cmd.CommandText = cmdText; if (isOpenTrans != null) { cmd.Transaction = isOpenTrans; } cmd.CommandType = cmdType; if (cmdParms != null) { cmd.Parameters.AddRange(cmdParms); } } }
