C# 事务使用（新增、更新、删除数据）

悠悠 2023-10-05 15:49 26阅读 0赞

### 1 通用方法 ###

#### 1.1 AddSqlParameters() ####

将参数值添加到命令对象中。

// 将参数值添加到命令对象中.
    private void AddSqlParameters(SqlCommand cmd, List<IDataParameter> list){
        foreach (SqlParameter parm in list) cmd.Parameters.Add(parm);
    }

#### 1.2 GetPartStringAtInsert() ####

将字段参数处理成insert语句的格式。

//将字段参数处理成insert语句的格式
    public static Tuple<string, string> GetPartStringAtInsert(ICollection<KeyValuePair<string, string>> list){
        if (list == null || list.Count == 0) return null;
        var enu = list.GetEnumerator();
        enu.MoveNext();
        var str1 = enu.Current.Key;
        var str2 = enu.Current.Value;
        while (enu.MoveNext()){
            str1 += "," + enu.Current.Key;
            str2 += "," + enu.Current.Value;
        }
        return new Tuple<string, string>(str1, str2);
    }

#### 1.3 GetPartStringAtUpdate() ####

将字段参数处理成update语句的格式。

//将字段参数处理成update语句的格式
    public static string GetPartStringAtUpdate(ICollection<KeyValuePair<string, string>> list){
        if (list == null || list.Count == 0) return null;
        var sb = new StringBuilder();
        var enu = list.GetEnumerator();
        enu.MoveNext();
        sb.Append(enu.Current.Key + "=" + enu.Current.Value);
        while (enu.MoveNext()){
            sb.AppendFormat(",{0}={1}", enu.Current.Key, enu.Current.Value);
        }
        return sb.ToString();
    }

### 2 新增数据 ###

T_UserInfo userInfo=new T_UserInfo(){UserInfoID='1',Passowrd='123456'};
    //连接对象
    SqlConnection conn = null;
    //事物
    SqlTransaction trans = null;
    SqlCommand cmd = new SqlCommand();
    try{
        //打开连接
        conn = new SqlConnection("数据库地址");
        if (conn.State != ConnectionState.Open){
            conn.Open();
        }
        //开始事物
        trans = conn.BeginTransaction();
        cmd.Connection = conn;
        cmd.Transaction = trans;
        cmd.CommandTimeout = 180;
        //获取字段参数
        var strSql = new StringBuilder();
        var paramList = new List<IDataParameter>();
        var pairList = new List<KeyValuePair<string, string>>();
        pairList.Add(BuildKeyValue("UserInfoID", "@UserInfoID"));
        paramList.Add(new SqlParameter("@UserInfoID", SqlDbType.NVarChar) { Value = userInfo.UserInfoID });
        pairList.Add(BuildKeyValue("Passowrd", "@Passowrd"));
        paramList.Add(new SqlParameter("@Passowrd", SqlDbType.NVarChar) { Value = userInfo.Passowrd });
        //拼接insert语句
        var tp = GetPartStringAtInsert(pairList);
        strSql.AppendFormat("INSERT INTO T_UserInfo({0}) VALUES ({1});\r\n", tp.Item1, tp.Item2);
        //注入参数
        AddSqlParameters(cmd, paramList);
        cmd.CommandText=strSql.ToString();
        //执行新增语句
        cmd.ExecuteNonQuery();
        cmd.Parameters.Clear();
        //事务提交
        trans.Commit();
    catch (Exception e){
        //执行异常时，通过事务回滚insert操作
        if (trans != null) trans.Rollback();
    }

### 3 更新数据 ###

T_UserInfo userInfo=new T_UserInfo(){UserInfoID='1',Passowrd='123456'};
    //连接对象
    SqlConnection conn = null;
    //事物
    SqlTransaction trans = null;
    SqlCommand cmd = new SqlCommand();
    try{
        //打开连接
        conn = new SqlConnection("数据库地址");
        if (conn.State != ConnectionState.Open){
            conn.Open();
        }
        //开始事物
        trans = conn.BeginTransaction();
        cmd.Connection = conn;
        cmd.Transaction = trans;
        cmd.CommandTimeout = 180;
        //获取字段参数
        var strSql = new StringBuilder();
        var paramList = new List<IDataParameter>();
        var pairList = new List<KeyValuePair<string, string>>();
        paramList.Add(new SqlParameter("@UserInfoID", SqlDbType.NVarChar) { Value = userInfo.UserInfoID });
        pairList.Add(BuildKeyValue("Passowrd", "@Passowrd"));
        paramList.Add(new SqlParameter("@Passowrd", SqlDbType.NVarChar) { Value = userInfo.Passowrd });
        //拼接update语句
        var tp = GetPartStringAtUpdate(pairList);
        strSql.AppendFormat("UPDATE T_UserInfo SET {0} WHERE UserInfoID=@UserInfoID\r\n", tp);
        //注入参数
        AddSqlParameters(cmd, paramList);
        cmd.CommandText=strSql.ToString();
        //执行更新语句
        cmd.ExecuteNonQuery();
        cmd.Parameters.Clear();
        //事务提交
        trans.Commit();
    catch (Exception e){
        //执行异常时，通过事务回滚update操作
        if (trans != null) trans.Rollback();
    }

#### 4 删除数据 ####