C#使用事务批量操作实例 - C#基础语法系列

C#基础语法系列- C#事务

业务描述： 写一个 批量插入方法，InsertList, 事务中执行insert方法（单个插入）。

/// <summary>
/// 批量插入操作
/// </summary>
/// <param name="userList">列表</param>
/// <returns></returns>
public  bool Insert(List<User> userList)
{
    bool isSuccess = true;

    using (SqlConnection conn = new SqlConnection(Config.DBConn))
    {
        conn.Open();
        SqlTransaction tran = conn.BeginTransaction();
        try
        {
            foreach (var item in userList)
            {
             //*执行单个插入方法
                int result = Insert(item, tran);
            }
            tran.Commit();
        }
        catch (Exception ex)
        {
         //rollback
            tran.Rollback();
            isSuccess = false;
        }
    }

    return isSuccess;
}


/// <summary>
/// 单行插入
/// </summary>
/// <param name="user">单行</param>
/// <returns></returns>
public bool Insert(User user, SqlTransaction transaction)
{
    string sql = @"insert into Table(Attribute)values(@Attribute)";
    List<SqlParameter> param = new List<SqlParameter>();
    param.Add(new SqlParameter() { ParameterName = "@Attribute", DbType = DbType.String, SqlValue = value });

    formalSql = string.Format(sql, valueStr, paramStr);

    /*此处执行插入方法*/
    int result = DBOptExtend.ExecuteNonQuery(formalSql, transaction, param.ToArray());
    return result > 0;
}


/// <summary>
/// 数据库操作方法扩展
/// </summary>
public class DBOptExtend
{
    /// <summary>
    ///执行查询
    /// </summary>
    /// <param name="sentence">sql语句</param>
    /// <param name="transaction">事务</param>
    /// <param name="parameters">参数</param>
    /// <returns></returns>
    public  static int ExecuteNonQuery(string sentence, SqlTransaction transaction, DbParameter[] parameters = null)
    {
        using (SqlCommand cmd = new SqlCommand(sentence))//modify by wsy
        {
            cmd.Transaction = transaction;
            cmd.Connection = transaction.Connection;
            if (parameters != null)
            {
                cmd.Parameters.AddRange(parameters.ToArray());
            }
            int result = Convert.ToInt32(cmd.ExecuteNonQuery());
            return result;
        }
    }
}