.net同时执行多条sql语句(含事务功能)

 

public static readonly string connstr =
ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;//sql连接字符串
private const int TIMEOUT = 999;//sql连接时间

/// <summary>
/// 准备一个待执行的SqlCommand
/// </summary>
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType commandType, string commandText, params SqlParameter[] paras)
{
try
{
if (conn.State != ConnectionState.Open)
{
conn.Close();
conn.Open();
}
cmd.Connection = conn;
if (commandText != null)
cmd.CommandText = commandText;
cmd.CommandTimeout = TIMEOUT;
cmd.CommandType = commandType; //这里设置执行的是T-Sql语句还是存储过程

if (trans != null)
cmd.Transaction = trans;

if (paras != null && paras.Length > 0)
{
//cmd.Parameters.AddRange(paras);
for (int i = 0; i < paras.Length; i++)
{
if (paras[i].Value == null || paras[i].Value.ToString() == "")
paras[i].Value = DBNull.Value; //插入或修改时，如果有参数是空字符串，那么以NULL的形式插入数据库
cmd.Parameters.Add(paras[i]);
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}

/// <summary>
/// 执行多条sql语句（List泛型集合）事务（无参数）
/// </summary>
/// <param name="listSql">包含多条sql语句的泛型集合</param>
/// <returns>受影响行数</returns>
public static int ExecuteNonQuery(List<string> listSql)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
PrepareCommand(cmd, conn, trans, CommandType.Text, null, null);
try
{
int count = 0;
for (int i = 0; i < listSql.Count; i++)
{
string strSql = listSql[i];
if (strSql.Trim().Length > 1)
{
cmd.CommandText = strSql;
cmd.CommandTimeout = TIMEOUT;
count += cmd.ExecuteNonQuery();
}
}
trans.Commit();
cmd.Parameters.Clear();
return count;
}
catch
{
trans.Rollback();
cmd.Parameters.Clear();
return 0;
}
finally
{
conn.Close();
}
}