前言：
通常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>)
{
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=;
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 = ;
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 = ;
Random ran = new Random(iSeed);
int RandKey=ran.Next(,);
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>)
{
Response.Write("执行成功");
}
else
{
Response.Write("执行失败");
} } protected void Button2_Click(object sender, EventArgs e)
{
SqlServerInfo ssi = new SqlServerInfo();
int iSeed = ;
Random ran = new Random(iSeed);
int RandKey = ran.Next(, );
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 > )
{
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=;
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 = ;
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);
}
}
}