SQL脚本MERGE操作异常未被C#捕获，如何确保异常传递至C#？

问题背景 #

你的SQL脚本包含INSERT（带OUTPUT返回结果集）和MERGE操作，C#仅读取第一个结果集就返回，导致MERGE阶段的异常无法被捕获——因为ADO.NET只有在遍历所有结果集时才会检测到后续语句的执行错误。

可行解决方案 #

1. 遍历所有结果集 #

修改C#代码，在获取到插入ID后，必须遍历完所有剩余结果集，确保ADO.NET能检测到后续操作的异常：

await using (var reader = await cmd.ExecuteReaderAsync())
{
    long? insertedId = null;
    // 读取INSERT返回的第一个结果集
    if (await reader.ReadAsync())
    { 
        insertedId = (long)reader["ID"];
    }
    // 强制遍历所有后续结果集，触发异常检测
    while (await reader.NextResultAsync())
    {
        // 无需处理数据，仅完成结果集遍历
    }
    return insertedId;
}

当MERGE出错时，SQL的CATCH块抛出的错误会绑定到后续结果流中，只有执行NextResultAsync时，ADO.NET才会捕获并抛出该异常。

2. 修改SQL为单结果集 #

调整SQL逻辑，将插入的ID存入变量，最后统一返回，避免多结果集的问题：

BEGIN TRY
BEGIN TRAN

DECLARE @InsertedId BIGINT;
-- 将插入的ID存入变量，不直接返回结果集
INSERT INTO MY_TABLE (COLUMN1, COLUMN2)
OUTPUT inserted.ID INTO @InsertedId
VALUES ('ABC', 'DEF')

MERGE MY_OTHER_TABLE T
USING
  (
    SELECT ID, COLUMN1, COLUMN2
    FROM MY_TABLE
    WHERE ID = @InsertedId -- 仅关联刚插入的记录，优化性能
  ) S
  ON T.MY_TABLE_ID = S.ID
WHEN NOT MATCHED THEN
  INSERT (MY_TABLE_ID) VALUES (S.ID);

-- 最后统一返回插入的ID
SELECT @InsertedId AS ID;

COMMIT TRAN
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRAN

  DECLARE @ErrorMessage NVARCHAR(4000);
  DECLARE @ErrorSeverity INT;
  DECLARE @ErrorState INT;

  SELECT
    @ErrorMessage = ERROR_MESSAGE(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE();

  RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH

此时SQL仅返回一个结果集，C#读取时，MERGE阶段的错误会直接让ExecuteReaderAsync抛出异常，无需处理多结果集。

3. 使用ExecuteScalarAsync简化代码 #

如果只需要获取插入的ID，结合上述单结果集的SQL修改，可以改用ExecuteScalarAsync，它会等待整个批处理执行完成，任何阶段的错误都会直接触发异常：

var result = await cmd.ExecuteScalarAsync();
return result != DBNull.Value ? (long)result : null;

核心原理 #

ADO.NET处理多结果集批处理时，会分阶段返回结果。若仅读取第一个结果集就终止，后续语句的执行错误不会被检测到。通过遍历所有结果集或改为单结果集，可确保错误被及时传递到C#代码中。

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