如何用ADO实现SQL Server用户设置表的新增/更新（UPSERT）操作？

要实现你需要的「用户设置存在则更新、不存在则插入（包括用户首次添加设置）」的需求，最稳妥高效的方式是用SQL Server的MERGE语句（也就是常说的UPSERT），配合ADO.NET的参数化查询来完成——既避免并发问题，也能杜绝SQL注入风险。

第一步：SQL MERGE语句的设计 #

首先我们需要编写一条MERGE语句，它会根据UserId和Key的组合键来判断是更新还是插入：

MERGE INTO UserSettings AS Target
USING (
    SELECT @UserId AS UserId, @Key AS Key, @Value AS Value
) AS Source
ON Target.UserId = Source.UserId AND Target.[Key] = Source.[Key]
WHEN MATCHED THEN
    UPDATE SET Target.Value = Source.Value
WHEN NOT MATCHED THEN
    INSERT (UserId, [Key], Value)
    VALUES (Source.UserId, Source.[Key], Source.Value);

注意：这里假设你的表名为UserSettings，而且必须给UserId+Key添加组合唯一约束，不然MERGE无法准确判断匹配条件，还会出现重复数据。

第二步：ADO.NET代码实现 #

接下来我们把这个SQL逻辑封装成C#方法，结合你提供的UserSettingsModel类来实现：

using System.Data.SqlClient;

public static void UpsertUserSetting(UserSettingsModel setting, string connectionString)
{
    // 用参数化查询避免SQL注入
    const string sql = @"
        MERGE INTO UserSettings AS Target
        USING (
            SELECT @UserId AS UserId, @Key AS Key, @Value AS Value
        ) AS Source
        ON Target.UserId = Source.UserId AND Target.[Key] = Source.[Key]
        WHEN MATCHED THEN
            UPDATE SET Target.Value = Source.Value
        WHEN NOT MATCHED THEN
            INSERT (UserId, [Key], Value)
            VALUES (Source.UserId, Source.[Key], Source.Value);
    ";

    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        using (var command = new SqlCommand(sql, connection))
        {
            // 添加参数，对应模型的属性
            command.Parameters.AddWithValue("@UserId", setting.UserId);
            // Key是SQL关键字，表字段需加[]，参数名用@Key没问题
            command.Parameters.AddWithValue("@Key", setting.Key);
            // 处理Value为空的情况，转换成SQL可识别的DBNull
            command.Parameters.AddWithValue("@Value", setting.Value ?? DBNull.Value);

            // 执行命令，返回受影响的行数（1表示插入或更新，0表示值未变化）
            int affectedRows = command.ExecuteNonQuery();
        }
    }
}

关键注意事项 #

• 必须添加组合唯一约束：一定要在UserSettings表上创建这个约束，否则MERGE无法正确判断匹配，还会产生重复数据。创建约束的SQL：

  ALTER TABLE UserSettings
  ADD CONSTRAINT UC_UserSettings_UserId_Key UNIQUE (UserId, [Key]);
  

• 坚持使用参数化查询：绝对不能直接拼接SQL字符串，否则会有严重的SQL注入风险，你也可以用command.Parameters.Add()指定SqlDbType来更严谨地处理参数类型。
• 处理空值情况：如果Value可能为null，要转换成DBNull.Value，否则ADO.NET会抛出参数为空的错误。
• 优先选择MERGE保证并发安全：MERGE语句是原子操作，比先查询再判断然后插入/更新的方式更安全，不会出现并发情况下的重复插入问题。

替代方案：旧版SQL Server（不支持MERGE） #

如果你的SQL Server版本低于2008（不支持MERGE），可以用「先更新，判断是否有行受影响，没有则插入」的方式，但这种方式在高并发下可能有重复插入的风险，需要加事务：

public static void UpsertUserSettingLegacy(UserSettingsModel setting, string connectionString)
{
    const string updateSql = @"
        UPDATE UserSettings 
        SET Value = @Value 
        WHERE UserId = @UserId AND [Key] = @Key;
    ";
    const string insertSql = @"
        INSERT INTO UserSettings (UserId, [Key], Value)
        VALUES (@UserId, @Key, @Value);
    ";

    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        using (var transaction = connection.BeginTransaction())
        {
            try
            {
                // 先尝试更新
                using (var updateCommand = new SqlCommand(updateSql, connection, transaction))
                {
                    updateCommand.Parameters.AddWithValue("@UserId", setting.UserId);
                    updateCommand.Parameters.AddWithValue("@Key", setting.Key);
                    updateCommand.Parameters.AddWithValue("@Value", setting.Value ?? DBNull.Value);
                    int updatedRows = updateCommand.ExecuteNonQuery();

                    // 如果没有更新到任何行，说明不存在，执行插入
                    if (updatedRows == 0)
                    {
                        using (var insertCommand = new SqlCommand(insertSql, connection, transaction))
                        {
                            insertCommand.Parameters.AddWithValue("@UserId", setting.UserId);
                            insertCommand.Parameters.AddWithValue("@Key", setting.Key);
                            insertCommand.Parameters.AddWithValue("@Value", setting.Value ?? DBNull.Value);
                            insertCommand.ExecuteNonQuery();
                        }
                    }

                    transaction.Commit();
                }
            }
            catch (Exception)
            {
                transaction.Rollback();
                throw; // 重新抛出异常让上层处理
            }
        }
    }
}

内容的提问来源于stack exchange，提问作者Connie DeCinko CS