如何在SQL Server中根据数组值更新表内对应type类型字段

问题场景 #

我有一个字段存储了数组，内容如下：

[empty, true, true, empty × 2, true, true]
1: true
2: true
5: true
6: true
length: 7

需求规则：数组索引1值为true则设置type_1 = true，索引2值为true则设置type_2 = true，索引5值为true则设置type_5 = true，索引6值为true则设置type_6 = true。
原有实现代码如下：

public bool UpdateType(int id, Array selectedTypes)
{
    bool res = false;
    string sqlstr = "";

    try
    {
        using (conn = new SqlConnection(CommonDA.GetConnectionString()))
        {
            conn.Open();
            trans = conn.BeginTransaction();

            try
            {
                SqlCommand cmd = conn.CreateCommand();
                sqlstr = @"Update Goal_Type set type_1 = @type_1,
                                   type_2 = @type_2,
                                   type_3 = @type_3,
                                   type_4 = @type_4,
                                   type_5 = @type_5,
                                   type_6 = @type_6,
                                   where id = @id ";
                cmd.CommandText = sqlstr;
                cmd.Transaction = trans;

                cmd.Parameters.AddWithValue("@id", id);
                cmd.Parameters.AddWithValue("@type_1", selectedTypes[1]);
                cmd.Parameters.AddWithValue("@type_2", selectedTypes[2]);
                cmd.Parameters.AddWithValue("@type_3", selectedTypes[3]);
                cmd.Parameters.AddWithValue("@type_4", selectedTypes[4]);
                cmd.Parameters.AddWithValue("@type_5", selectedTypes[5]);
                cmd.Parameters.AddWithValue("@type_6", selectedTypes[6]);

                cmd.ExecuteNonQuery();

                trans.Commit();
                conn.Close();
                res = true;
            }
            catch (Exception Err)
            {
                trans.Rollback();
                CommonLB.SystemError("UpdateType Fall", Err.Message);
                CommonLB.SystemError("Data", "SQL:" + sqlstr);
            }
        }
    }
    catch (Exception Err)
    {
        throw new ApplicationException(Err.Message, Err);
    }

    return res;
}

优化实现方案 #

首先原有代码存在两个显性问题：

1. Update语句中type_6 = @type_6后面多了多余的逗号，直接执行会报SQL语法错误
2. 单条Update语句本身具备原子性，额外开启事务属于冗余操作，using块会自动释放连接，不需要手动调用Close()方法

优化点说明

• 只处理需求指定的1、2、5、6四个索引，跳过无关的3、4索引，减少无效参数和字段更新
• 动态拼接SQL：仅当对应索引值为true时才生成对应字段的更新逻辑，减少不必要的字段修改
• 替换AddWithValue为显式指定参数类型，避免隐式类型转换带来的潜在问题

优化后代码

public bool UpdateType(int id, bool[] selectedTypes)
{
    bool res = false;
    // 只保留需求指定的索引映射
    var indexMap = new Dictionary<int, string>
    {
        {1, "type_1"},
        {2, "type_2"},
        {5, "type_5"},
        {6, "type_6"}
    };
    var setClauses = new List<string>();
    var parameters = new List<SqlParameter>();
    parameters.Add(new SqlParameter("@id", SqlDbType.Int) { Value = id });

    foreach (var kv in indexMap)
    {
        // 索引越界或者值为false时跳过更新
        if (selectedTypes.Length <= kv.Key || !selectedTypes[kv.Key]) 
            continue;
        setClauses.Add($"{kv.Value} = @{kv.Value}");
        parameters.Add(new SqlParameter($"@{kv.Value}", SqlDbType.Bit) { Value = true });
    }

    // 没有需要更新的字段直接返回
    if (setClauses.Count == 0) 
        return true;

    string sqlstr = $"Update Goal_Type set {string.Join(",", setClauses)} where id = @id";

    try
    {
        using (var conn = new SqlConnection(CommonDA.GetConnectionString()))
        {
            conn.Open();
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = sqlstr;
                cmd.Parameters.AddRange(parameters.ToArray());
                cmd.ExecuteNonQuery();
            }
            res = true;
        }
    }
    catch (Exception Err)
    {
        CommonLB.SystemError("UpdateType Fall", Err.Message);
        CommonLB.SystemError("Data", "SQL:" + sqlstr);
        throw new ApplicationException(Err.Message, Err);
    }

    return res;
}

如果需要保留原逻辑中无论值为true/false都覆盖所有对应字段的逻辑，只需要把索引映射里加上3、4对应的字段，去掉判断值为true的分支即可。

内容的提问来源于stack exchange，提问作者Thomas Lin