c# - 如何以与 Management Studio 相同的顺序获取 SQL Server 消息？

Question

我正在尝试增强错误处理。MSDN展示了如何处理 aSqlException和SqlException.Errors Property. 我在 SQL Server 上构建了一个存储过程，它会生成一些输出。此过程是从具有类似 MSDN 的错误处理的 .NET 应用程序调用的。

但是，消息的顺序与 SQL Server Management Studio 不同。如何获得与SqlException.ErrorsManagement Studio 相同的顺序？

存储过程：

CREATE PROCEDURE [dbo].[___PROVOKE_ERRORS]
AS
BEGIN
    SET NOCOUNT ON;
    SET LANGUAGE ENGLISH;

    declare @tab table(id int, word varchar(10))
    declare @id int;

    set @id = (select top(1) object_id from sys.objects)
    print 'Check-ID #1: ' + cast(@id as varchar(10))

    insert into @tab (id, word) values (0, 'test')
    insert into @tab (id, word) values (1, '1234567890abcdef')

    set @id = (select top(1) object_id from sys.objects order by name)
    print 'Check-ID #2: ' + cast(@id as varchar(10))

    insert into @tab (id, word) values (10000000000, 'test')
    
    set @id = (select top(1) object_id from sys.objects order by object_id desc)
    print 'Check-ID #3: ' + cast(@id as varchar(10))

    select * from @tab
END

用于调用具有错误处理的过程的 C# 代码：

public static void Test()
{
    try
    {
        using (SqlConnection con = new SqlConnection(Properties.Settings.Default.TestConnection))
        {
            con.Open();
            using (SqlCommand cmd = new SqlCommand("___PROVOKE_ERRORS", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.ExecuteNonQuery();
            }
            con.Close();
        }
    }
    catch (SqlException ex)
    {
        DisplaySqlErrors(ex);
    }
}

private static void DisplaySqlErrors(SqlException exception)
{
    for (int i = 0; i < exception.Errors.Count; i++)
    {
        Console.WriteLine("Index #{0} - {1}", i, exception.Errors[i].ToString());
    }
}

在 Management Studio 中调用过程时的消息：

Check-ID #1: 3
Msg 8152, Level 16, State 30, Procedure ___PROVOKE_ERRORS, Line 15 [Batch Start Line 0]
String or binary data would be truncated.
The statement has been terminated.
Check-ID #2: 1044210445
Msg 8115, Level 16, State 2, Procedure ___PROVOKE_ERRORS, Line 20 [Batch Start Line 0]
Arithmetic overflow error converting expression to data type int.
The statement has been terminated.
Check-ID #3: 2146991621

在 .NET 应用程序中调用过程时的消息：

Index #0 - System.Data.SqlClient.SqlError: String or binary data would be truncated.
Index #1 - System.Data.SqlClient.SqlError: Arithmetic overflow error converting expression to data type int.
Index #2 - System.Data.SqlClient.SqlError: Check-ID #1: 3
Index #3 - System.Data.SqlClient.SqlError: The statement has been terminated.
Index #4 - System.Data.SqlClient.SqlError: Check-ID #2: 1044210445
Index #5 - System.Data.SqlClient.SqlError: The statement has been terminated.
Index #6 - System.Data.SqlClient.SqlError: Check-ID #3: 2146991621

尤其是错误消息和相应的“语句已终止”不按顺序排列。

标签： c#.netsql-server