ADO.NET 事务操作封装

 

/// <summary>
/// 事务处理
/// </summary>
/// <param name="sqlList">sql语句数组</param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
public static bool OpenTransation(List<string> sqlList)
{
    SqlConnection conn = new SqlConnection(connStr);
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn;
    try
    {
        conn.Open(); //打开
        cmd.Transaction = conn.BeginTransaction(); //开启事务
        foreach (var sql in sqlList)
        {
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();
        }
        cmd.Transaction.Commit(); //执行事务
        return true;
    }
    catch (Exception ex)
    {
        //出现错误,回滚之前执行的操作
        if(cmd.Transaction != null)
        {
            cmd.Transaction.Rollback(); //回滚
        }
        conn.Close(); //关闭
        throw new Exception(ex.Message);
    }
    finally
    {
        cmd.Transaction = null; //初始化
        conn.Close();
    }
}

 

下面是带参数的事务处理,我还没有测试过是否可行

/// <summary>
/// 带参数的事务处理
/// </summary>
/// <param name="sqlList">sql语句数组</param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
public static bool OpenTransation(List<string> sqlList , List<SqlParameter[]> pars)
{
    SqlConnection conn = new SqlConnection(connStr);
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn;
    try
    {
        conn.Open(); //打开
        cmd.Transaction = conn.BeginTransaction(); //开启事务
         for(int i = 0; i < sqlList.Count; i++)
        {
            cmd.CommandText = sqlList[i];
            cmd.Parameters.Clear();
            cmd.Parameters.AddRange(pars[i]);
            cmd.ExecuteNonQuery();
        }
        cmd.Transaction.Commit(); //执行事务
        return true;
    }
    catch (Exception ex)
    {
        //出现错误,回滚之前执行的操作
        if(cmd.Transaction != null)
        {
            cmd.Transaction.Rollback(); //回滚
        }
        conn.Close(); //关闭
        throw new Exception(ex.Message);
    }
    finally
    {
        cmd.Transaction = null; //初始化
        conn.Close();
    }
}