谈谈基于SQL Server 的Exception Handling

对于所有的开发人员来说，Exception Handling是我们每天都要面对的事情。对于基于Source Code的Exception Handling，我想大家已经司空见惯了，但是对于Database级别的Exception Handling，就没有那么常见了。在这篇文章中，我将会介绍我对于基于Database编程中Exception Handling的一些粗浅的认识：在编写Stored Procedure时，如何抛出一个可预知的Exception，ADO.NET如何处理从Database抛出的Exception，如何保存基于Database Exception的Error Message，如何在Database和.NET Application之间进行消息的传递[注：这里的Database主要指SQL Server]。

@@ERROR
RAISEERROR
TRY CATCH & Return
Error message & sys.messages
ADO.NET Exception Handling
SqlException
InfoMessage

一、    @@ERROR

@@ERROR是我们经常使用的系统函数，其返回类型为INT，用以表示上一个语句的执行是否遇到错误，0便是语句正常执行，非0则以为着某个错误的产生。比如下面的一个例子：我在SQL Server Management Studio中执行下面一段简单的SQL：

DECLARE @result INT
SET @result = 10/0
PRINT @@ERROR
Go

会得到如下的执行结果：

由于除零导致错误的产生，使@@ERROR的值变成8134。实际上8134是一个Error Number，代表某个系统定义的Error，每个预定义的Error由一个唯一的Error Number来唯一标识。@@ERROR本质上就是返回上一个语句执行遇到的Error的ID号，所以这并不是一个随机的值，无论是10/0还是100/0，@@ERROR返回的结果都是一样的。

由于可以通过@@ERROR得到Error的类型，可以帮助我们在编写Stored Procedure的过程中，借助这个@@ERROR进行一些流程的控制。比如我们知道Error Number为547代表违反外键约束，我们就可以通过@@ERROR的结果是否等于547来进行异常的处理：

 

 

 

UPDATE T_USERS_IN_ROLES SET [USER_ID] = 'dummy text'
IF @@ERROR = 547
PRINT 'The user is not existent'

通过我们上面一个截图我们发现，在

实际上，SQL Server通过一个名为sys.messages的系统表来存储关于Error的一些信息[关于sys.messages，在后续的章节中还会提及]。下面是sys.messages的结构：message_id不仅仅代表message的唯一标识，对于一个预定义的Error，其Error number就是这个message_id。由于Localization的需要，我们需要为不同的语言定义不同的Message，这些Message共享一个message_id, 具体采用何种语言通过luange_id来标识。Severity代表Error的严重程度，我将在后续部分专门介绍。is_event_logged是一个Indicator，表明出现该Error是否需要在Event log中进行日志记录，text当然就是message文本了。

我们可以通过下面的SQL来进行验证：

DECLARE @result INT
DECLARE @error INT
SET @result = 5/0
SET @error = @@ERROR
SELECT @error,sys.messages.* FROM sys.messages WHERE message_id = @error
Go

下面是执行的结果：

对于@@ERROR，有一点需要特别提醒的是：它仅仅代表前一个语句执行的Error Number，之后任何一段语句的执行都会改变@@ERROR的值，甚至是一个IF语句。我想通过下面一段SQL，你肯定会后一个深刻的认识：

我们可以看到，一个简简单单的IF语句就将@@ERROR从8134变成了0。不过想想也很简单，IF语句本身也是一个执行语句，在执行过程中并没有遇到Error，所以@@ERROR应该返回0。这也是我在上面的Sample中通过SET @error = @@ERROR将@@ERROR进行预存的原因。

@@ERROR实际上代表的是在编写SQL或者Stored procedure中对异常的识别，大多数我们通过@@ERROR来判断一段SQL语句是否成功执行，保证没有遇到不可预知的异常。对于一些可以预知的异常在SQL中又该如何处理呢？

我发现很多程序员喜欢使用Output参数来处理这些预知的异常。比如：我们需要编写一个添加User的Stored procedure，user name具有唯一性，添加一个和database中同名的user显然是不合法的，在很多情况下通过一个Output参数来返回操作最终执行的情况，比如：

flag output参数可以得到User的创建操作最终执行的结果：-1代表重名，0代表成功，大于0代表出现不可预知的异常。

CREATE Procedure P_USERS_I
    (
        @user_id            varchar(50),
        @user_name    nvarchar(256),
        @flag                INT OUTPUT
    )
AS
IF(EXISTS(SELECT * FROM  dbo.T_USERS WHERE LOWERED_USER_NAME = LOWER(@user_name) OR [USER_ID] = @user_id))
    BEGIN
        SET @flag = -1
        RETURN
    END     
INSERT INTO dbo.T_USERS
           ([USER_ID]
           ,[USER_NAME]
           ,LOWERED_USER_NAME)
     VALUES(@user_id, @user_name, LOWER(@user_name))     
SET @flag = @@ERROR

很显然通过

说实话，我不太喜欢这样的异常处理方式，其实这并不是说这样的处理不好，大部分还是由于个人喜好决定。我觉得，既然添加一个同名的User本身代表一种Exception，从语义上讲，认为地抛出这样Exception的方式好像更加合理一点，所以我们喜欢通过调用RAISEERROR的方式将一个Error抛出。

二、       RAISEERROR

RAISEEROR是一个系统函数，用于奖某个可以预知的Exception抛出，供Application捕捉并处理，下面是RAISERROR的声明：：代表被你抛出的Error的Message，你可以同国3中方式来表示Message：msg_id带面sys.messages中的message_id, msg_str表示一个自定义的文本，@local_variable则表示message的变量。

RAISERROR ( { msg_id | msg_str | @local_variable }
    { ,severity ,state }
    [ ,argument [ ,n ] ] )
[ WITH option [ ,n ] ]

msg_id | msg_str | @local_variable

Severity：一个代表严重程度的数字，其范围为0-25，其中0-18可以由任何用户指定，19-25只能由sysadmin指定。一般地，0-10为严重程度很低的错误，11-18来高级别的错误，19-25代表非常严重的错误，以致在执行完成之后会终止当前的Session。

State：一个0-127的整数，代表一个错误状态，对于在多个地方抛出Message一致的的情况，将State在不同的地方设置在不同的值，在Debug的时候可以很快知道是哪里出错了，所以State具有很现实的意义。

Argument：向我们调用String.Format(string,…)一样，我们可以在一个一个参数中使用{0：G}{1：D}这样的站位符和进行格式处理的字符，这些站位符由后面的参数来填充。在这里也一样，在message中你一可以添加站位符，这着站位符由Argument来填充，具体如何定义，可以参阅SQL Server 2005 Books Online.

WITH option [ ,...n ]:代表一些额外的选项, LOG表示进行日志记录，NOWAIT表示立即将Message递交到客户端，SETERROR强制将当前真实的@@ERROR或者message_id返回到客户端。

明白了RAISERROR如何使用了后，我们可以修改我们的先前创建User的Stored Procedure：

 三、    TRY CATCH & Return

在上面一节中，我通过RAISERROR重写了创建User的Stored procedure，实际上上面的Stored procedure是有问题的。我之所以没有立即指出，是因为这是一个很容易犯的错误，尤其是习惯了.NET Exception Handling的人更容易犯这样的错误。我们知道在.NET Application中，如果出现一个未处理的Exception，程序将立即终止，后续的程序将不会执行，但是对于上面的SQL则不一样，虽然我们通过RAISERROR将Error抛出，但是SQL的指定并不会被终止，INSERT语句仍然会被执行的。我想很多人会说在RAISERROR后加一个Return就可以了嘛。不错这是一个常用的解决方案，但是我不倾向于使用这种方法。为了更清楚地说明这个问题，我们举另一个相关的例子，上面我们介绍了创建User的例子，我们现在来引入另一个例子：如何将一个User添加到一个Role里面。由于这个例子在后面还将使用，我先讲设计的Table的结构介绍一下：T_USERS和T_ROLES分别存放User和Role，User和Role不区分大小写并且唯一，两者通过T_USERS_IN_ROLES进行关联。

现在我们来写将user添加到Role的Stored Procedure：首先验证User和Role是否存在，然后验证该User和Role是否已经存在，最后将Mapping关系添加到T_USERS_IN_ROLES中：Stored procedure中，我们在困难出现Exception的地方添加了RETURN，从而防止了后续的程序继续执行，但是对于一些我们无法预知的Exception呢？我们该如何添加这个RETURN呢？我想有人会说在每条语句执行之后都通过@@ERROR判断是否有Exception出现，我知道很多人喜欢这么做，而事实上，我现在真在维护的一些Stored procedure就是这么做的：全篇都是IF@@ERROR RETURN。其实我们完全可以通过其它的方式是我们的SQL看出来更加优雅一点。那就是使用我们很熟悉的TRY CATCH。在SQL Server中我们通过BEGIN TRY/END TRY和BEGIN CATCH/END CATCH这样的结构来进行Exception Handling。

CREATE Procedure P_USERS_IN_ROLES_I
    (
        @user_name    NVARCHAR(256),
        @role_name        NVARCHAR(256)
    )
AS
DECLARE @user_id    VARCHAR(50)
DECLARE @role_id    VARCHAR(50)
SELECT @user_id = [USER_ID] FROM dbo.T_USERS WHERE LOWERED_USER_NAME = LOWER(@user_name)
IF(@user_id IS NULL)
    BEGIN
        RAISERROR ('The user dose not exist',16,1)
        RETURN
    END
    
SELECT @role_id = [ROLE_ID] FROM dbo.T_ROLES WHERE LOWERED_ROLE_NAME = LOWER(@role_name)
IF(@role_id IS NULL)
    BEGIN
        RAISERROR ('The role dose not exist',16,1)
        RETURN
    END    
IF(EXISTS(SELECT * FROM T_USERS_IN_ROLES WHERE [USER_ID] = @user_id AND ROLE_ID = @role_id))
    BEGIN
        RAISERROR ('The user is already in the role',16,1) 
        RETURN
    END
INSERT INTO     dbo.T_USERS_IN_ROLES([USER_ID],ROLE_ID) VALUES(@user_id,@role_id)

虽然说在上面的

通过TRY CATCH，上面的Stored procedure可以改成下面的样子：SQL的时候，碰到任何一个我们自己抛出的Exception和系统异常，都会跳到Catch Block中执行相应的操作。在CATCH中，我们把在TRY Block中遇到的Error从新抛出。

CREATE Procedure P_USERS_IN_ROLES_I
    (
        @user_name    NVARCHAR(256),
        @role_name    NVARCHAR(256)
    )
AS
DECLARE @user_id    VARCHAR(50)
DECLARE @role_id    VARCHAR(50)

DECLARE @error_message    NVARCHAR(256) 
DECLARE @error_serverity    INT
DECLARE @error_state        INT

BEGIN TRY

SELECT @user_id = [USER_ID] FROM dbo.T_USERS WHERE LOWERED_USER_NAME = LOWER(@user_name)
IF(@user_id IS NULL)
    BEGIN
        RAISERROR ('The user dose not exist',16,1)
    END
    
SELECT @role_id = [ROLE_ID] FROM dbo.T_ROLES WHERE LOWERED_ROLE_NAME = LOWER(@role_name)
IF(@role_id IS NULL)
    BEGIN
        RAISERROR ('The role dose not exist',16,1)
    END
IF(EXISTS(SELECT * FROM T_USERS_IN_ROLES WHERE [USER_ID] = @user_id AND ROLE_ID = @role_id))
    BEGIN
        RAISERROR ('The user is already in the role',16,1) 
    END    
INSERT INTO     dbo.T_USERS_IN_ROLES([USER_ID],ROLE_ID) VALUES(@user_id,@role_id)

END TRY

BEGIN CATCH    

    SET @error_ message    = ERROR_MESSAGE)
    SET @error_serverity    = ERROR_SEVERITY()
    SET @error_state        = ERROR_STATE()
    RAISERROR(@error_message,@error_serverity,@error_state)        

END CATCh

当执行上面一个

在这里有一些需要注意的是：并非所有的Error都会使用SQL的执行流入Catch Block，下面是两个主要的例外：

• Severity<10
• Severity>20并且会马上中止Session

此外，相信大家也看见了在Catch中使用了一些Error作为前缀的Function，这些Function为系统定义的Function，用于返回当前Error的一些信息，这样的Function有：

•          ERROR_NUMBER()：返回Error Number，相当于@@ERROR。
•          ERROR_MESSAGE()：返回Error message.
•          ERROR_SEVERITY()：返回Error严重级别.
•          ERROR_STATE() ：返回Error的状态.
•          ERROR_LINE() ：返回出现Error的行号.
          ERROR_PROCEDURE() ：返回出现Error的Stored Procedure名称.

四、    Error message & sys.messages

从前面的部分我们可以主要介绍了一种基于RAISERROR和TRY/CATCH的异常处理机制，个人觉得这是一种值得推荐的做法。但是上面的处理有一种不太理想的做法是：在每个Stored procedure中为不同的Error定义了Message。其实在很多情况下，每个Stored procedure都需要处理一些共同的Error，而且对于.NET Application来说往往是通过Message来判断Exception的类型，所以保持各个Stored Procedure的Message的一致性和Stored procedure和Application的Message的一致性就显得尤为重要。所以我们希望的做法是一次定义，对此使用。在Oracle中，我们知道我们可以通过定义具有全局意义的常数来解决，而对于SQL Server，没有全局常数的概念（在我的印象中好像没有），我们需要寻求另一种解决方案：将Message 添加到sys.messages中。

在前面的部分我们说过，sys.messages是可以用于专门存放Error相关的信息：Error number, severity，state，message等。而且他不但可以用于系统与定义error的存储，也可以用于存放我们自定义的Error。更加可喜的是，SQL Server定义了一些built-in stored procedure来用于message的添加、删除和修改：

sp_dropmessage [ @msgnum = ] message_number   [ , [ @lang = ] 'language' ]

sp_altermessage [ @message_id = ] message_number   , [ @parameter = ] 'write_to_log'   , [ @parameter_value = ] 'value' 

关于如何使用这些stored procedure，可以参阅SQL Server Books Online。在这里，我同下面的script添加我需要的Error。
注：直接操作sys.messages是不被允许的]

sp_addmessage    @msgnum = 50001,@severity = 16,@msgtext = N'This user is already existent' , @replace =  'replace' 
GO
sp_addmessage    @msgnum = 50002,@severity = 16,@msgtext = N'This role is already existent', @replace =  'replace' 
Go
sp_addmessage    @msgnum = 50003,@severity = 16,@msgtext = N'This user does not exist', @replace =  'replace' 
GO
sp_addmessage    @msgnum = 50004,@severity = 16,@msgtext = N'This role does not exist', @replace =  'replace' 
GO
sp_addmessage    @msgnum = 50005,@severity = 16,@msgtext = N'This user is already in the role', @replace =  'replace' 
GO

[

五、    ADO.NET Exception Handling

上面所有的都在介绍在Database层面如何进行Exception handling，下面我们同一个简单的Demo，简单介绍一个我么的.NET Application如何处理从Database Engine抛出的Exception。在这里我们使用一个简单的Cosole application模拟一个简单的Security方面的场景：创建用户、创建角色、添加用户到角色。大部分的功能都在上面提到了，在这里做一下总结：

1．   表结构：

2．   Messages（通过上面一节末Script在sys.messages中创建）：
·         50001：This user is already existent
·         50002：This role is already existent
·         50003：This user does not exist
·         50004：This role does not exist
·         50005：This user is already in the role

3．   Stored procedure
·         Create User：P_USERS_I
T_ROLES_I·         Add User in Role：P_USERS_IN_ROLES_I·         Delete Data: P_CLEAR_DATA4．   Common Function：Utility. ExecuteCommand

CREATE Procedure P_USERS_I
    (
        @user_id            varchar(50),
        @user_name    nvarchar(256)
    )
AS

DECLARE @error_number    INT
DECLARE @error_serverity    INT
DECLARE @error_state        INT

BEGIN TRY

IF(EXISTS(SELECT * FROM  dbo.T_USERS WHERE LOWERED_USER_NAME = LOWER(@user_name) OR [USER_ID] = @user_id))
    BEGIN
        RAISERROR (50001,16,1)
    END

INSERT INTO dbo.T_USERS
           ([USER_ID]
           ,[USER_NAME]
           ,LOWERED_USER_NAME)
     VALUES(@user_id, @user_name, LOWER(@user_name))   
     
END TRY

BEGIN CATCH
    
    SET @error_number        = ERROR_NUMBER()
    SET @error_serverity    =ERROR_SEVERITY()
    SET @error_state        = ERROR_STATE()
    RAISERROR(@error_number,@error_serverity,@error_state)        
        
END CATCH

          ·         Create Role：

CREATE Procedure T_ROLES_I
    (
        @role_id            varchar(50),
        @role_name    nvarchar(256)
    )
AS

DECLARE @error_number    INT
DECLARE @error_serverity    INT
DECLARE @error_state        INT

BEGIN TRY

IF(EXISTS(SELECT * FROM  dbo.T_ROLES WHERE LOWERED_ROLE_NAME = LOWER(@ROLE_name) OR [ROLE_ID] = @role_id))
    BEGIN
        RAISERROR (50002,16,1)
    END

INSERT INTO dbo.T_ROLES
           ([ROLE_ID]
           ,[ROLE_NAME]
           ,LOWERED_ROLE_NAME)
     VALUES(@ROLE_id, @ROLE_name, LOWER(@ROLE_name))
     
     END TRY

BEGIN CATCH
    
    SET @error_number        = ERROR_NUMBER()
    SET @error_serverity    =ERROR_SEVERITY()
    SET @error_state        = ERROR_STATE()
    RAISERROR(@error_number,@error_serverity,@error_state)        
END CATCH

 

CREATE Procedure P_USERS_IN_ROLES_I
    (
        @user_name    NVARCHAR(256),
        @role_name    NVARCHAR(256)
    )
AS

DECLARE @user_id    VARCHAR(50)
DECLARE @role_id    VARCHAR(50)

DECLARE @error_number    INT
DECLARE @error_serverity    INT
DECLARE @error_state        INT

BEGIN TRY

SELECT @user_id = [USER_ID] FROM dbo.T_USERS WHERE LOWERED_USER_NAME = LOWER(@user_name)

IF(@user_id IS NULL)
    BEGIN
        RAISERROR (50003,16,1)
    END
    
SELECT @role_id = [ROLE_ID] FROM dbo.T_ROLES WHERE LOWERED_ROLE_NAME = LOWER(@role_name)

IF(@role_id IS NULL)
    BEGIN
        RAISERROR (50004,16,1)
    END
    
IF(EXISTS(SELECT * FROM T_USERS_IN_ROLES WHERE [USER_ID] = @user_id AND ROLE_ID = @role_id))
    BEGIN
        RAISERROR (50005,16,1)
    END
    
INSERT INTO     dbo.T_USERS_IN_ROLES([USER_ID],ROLE_ID) VALUES(@user_id,@role_id)
 
END TRY

BEGIN CATCH
    
    SET @error_number        = ERROR_NUMBER()
    SET @error_serverity    =ERROR_SEVERITY()
    SET @error_state            = ERROR_STATE()
    RAISERROR(@error_number,@error_serverity,@error_state)    
END CATCH

 

CREATE Procedure P_CLEAR_DATA
AS

    DELETE FROM dbo.T_USERS_IN_ROLES
    DELETE FROM dbo.T_USERS
    DELETE FROM dbo.T_ROLES
    
GO

 

private const string connectionStringName = "TestDb";
public static bool ExecuteCommand(string procedureName, Dictionary<string, object> parameters)
        {
            ConnectionStringSettings connectionStringSection = ConfigurationManager.ConnectionStrings[connectionStringName];
            DbProviderFactory dbProviderFactory = DbProviderFactories.GetFactory(connectionStringSection.ProviderName);
            using (DbConnection connection = dbProviderFactory.CreateConnection())
            {
                connection.ConnectionString = connectionStringSection.ConnectionString;
                connection.Open();
                DbCommand command = connection.CreateCommand();
                command.CommandText = procedureName;
                command.CommandType = CommandType.StoredProcedure;
                DbParameter parameter;
                foreach (KeyValuePair<string, object> param in parameters)
                {
                    parameter = dbProviderFactory.CreateParameter();
                    parameter.ParameterName = param.Key;
                    parameter.DbType = DbType.String;
                    parameter.Value = param.Value;
                    command.Parameters.Add(parameter); 
                }
                DbTransaction transation = connection.BeginTransaction();
                command.Transaction = transation;
                try
                {
                    command.ExecuteNonQuery();
                    transation.Commit();
                    return true;
                }
                catch 
                {
                    transation.Rollback();
                    throw;
                }
            }
}

Create User, Create Role, Add User In Role, Delete All Data

5．  

        private const string ErrorUserExists = "This user is already existent";
        private const string ErrorRoleExists = "This role is already existent";
        private const string ErrorUserNotExists = "This user does not exist";
        private const string ErrorRoleNotExists = "This role does not exist";
        private const string ErrorUserInRole = "This user is already in the role";

        public static  bool CreateUser(string userName)
        {
            string procedureName = "P_USERS_I";
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("user_id", Guid.NewGuid().ToString());
            parameters.Add("user_name", userName);
            try
            {
                ExecuteCommand(procedureName, parameters);
                return true;
            }
            catch (Exception ex)
            {
                if (ex.Message == ErrorUserExists)
                {
                    Console.WriteLine("The user /"{0}/" you specify is already existent!",userName);
                    return false; ;
                }
                Console.WriteLine("A unhandled exception is thrown for some unknown reason!");
                return false;
            }
        }

        public static bool CreateRole(string roleName)
        {
            string procedureName = "P_ROLES_I";
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("role_id", Guid.NewGuid().ToString());
            parameters.Add("role_name", roleName);
            try
            {
                ExecuteCommand(procedureName, parameters);
                return true;
            }
            catch (Exception ex)
            {
                if (ex.Message==ErrorRoleExists)
                {
                    Console.WriteLine("The role /"{0}/" you specify is already existent!",roleName);
                    return false; ;
                }
                Console.WriteLine("A unhandled exception is thrown for some unknown reason!");
                return false;
            }
        }

        public static bool AddUserInRole(string userName, string roleName)
        {
            string procedureName = "P_USERS_IN_ROLES_I";
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("user_name", userName);
            parameters.Add("role_name", roleName);
            try
            {
                ExecuteCommand(procedureName, parameters);
                return true;
            }
            catch (Exception ex)
            {
                if (ex.Message==ErrorUserNotExists)
                {
                    Console.WriteLine("The user /"{0}/" you specify is not existent!", userName);
                    return false;
                }
                if (ex.Message==ErrorRoleNotExists)
                {
                    Console.WriteLine("The role /"{0}/" you specify is not existent!",roleName);
                    return false;
                }
                if (ex.Message == ErrorUserInRole)
                {
                    Console.WriteLine("The user /"{0}/" is in the role /"{1}/"!",userName,roleName);
                    return false;
                }
                Console.WriteLine("A unhandled exception is thrown for some unknown reason!");
                return false;
            }
 }
  public static void Clear()
        {
            ExecuteCommand("P_CLEAR_DATA", new Dictionary<string, object>());
        }

 

6．      Programs

7．   最终执行结果

六、SqlException

class Program
    {
        static void Main(string[] args)
        {
            Utility.Clear();
            if (Utility.CreateUser("Artech"))
            {
                Console.WriteLine("The user /"{0}/" has been sucessully created!", "Artech");
            }
            if (Utility.CreateUser("Artech"))
            {
                Console.WriteLine("The user /"{0}/" has been sucessully created!", "Artech");
            }
            if (Utility.CreateRole("Administrator"))
            {
                Console.WriteLine("/nThe role /"{0}/" has been sucessully created!", "Administrator");
            }
            if (Utility.CreateRole("Administrator"))
            {
                Console.WriteLine("The role /"{0}/" has been sucessully created!", "Administrator");
            }
            if (Utility.AddUserInRole("Artech", "Administrator"))
            {
                Console.WriteLine("/nThe user /"{0}/" has been successfully added in the role /"{1}/"", "Artech", "Administrator");
            }

            if (Utility.AddUserInRole("Dave Crane", "Administrator"))
            {
                Console.WriteLine("The user /"{0}/" has been successfully added in the role /"{1}/"", "Dave Crane", "Administrator");
            }

            if (Utility.AddUserInRole("Artech", "Super Administrator"))
            {
                Console.WriteLine("The user /"{0}/" has been successfully added in the role /"{1}/"", "Artech", "Super Administrator");
            }

            if (Utility.AddUserInRole("Artech", "Administrator"))
            {
                Console.WriteLine("The user /"{0}/" has been successfully added in the role /"{1}/"", "Artech", "Administrator");
            }
        }

 

在上面一节中，我给出了一个完整的例子说明了：如何在将message定义在sys.messages中保证message的一致性和可维护性；如何在Stored procedure中使用RAISERROR将一个可预知的Error抛出；如何在Stored procedure中使用TRY/CATCH进行异常的捕捉；在Application如果处理从SQL Server抛出的Exception。实际上，SQL Server database Engine抛出、被我们的.NET最终捕获的SqlException，我们通过SqlException的属性可以得到Error的相关信息。下面是SqlException的属性列表：

 

• public SqlErrorCollection Errors { get; }
• public int LineNumber { get; }
• public int Number { get; }
• public string Procedure { get; }
• public string Server { get; }
• public override string Source { get; }
• public byte State { get; }

有了前面的内容作铺垫，相信大家都知道每个属性分别表示的什么了吧。为了使大家对

在stored procedure中，遇到重名通过RAISERROR抛出异常[在整篇文章中，使用到Error和Exception，大家可以看成是等效的]：
    ·         Error Number：50001
    ·         Severity：16
    ·         State：1
    ·         Message：This user is already existent

我们来修正一下CreateUser方法：

Main（）中调用这个CreateUser（）：

public static  bool CreateUser(string userName)
        {
            string procedureName = "P_USERS_I";
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("user_id", Guid.NewGuid().ToString());
            parameters.Add("user_name", userName);
            try
            {
                ExecuteCommand(procedureName, parameters);
                return true;
            }
            catch (SqlException ex)
            {
                Console.WriteLine("ex.Class/t: {0}", ex.Class);
                Console.WriteLine("ex.ErrorCode/t: {0}", ex.ErrorCode);
                Console.WriteLine("ex.LineNumber/t: {0}", ex.LineNumber);
                Console.WriteLine("ex.Message/t: {0}", ex.Message);
                Console.WriteLine("ex.Number/t: {0}", ex.Number);
                Console.WriteLine("ex.Procedure/t: {0}", ex.Procedure);
                Console.WriteLine("ex.Server/t: {0}", ex.Server);
                Console.WriteLine("ex.Source/t: {0}", ex.Source);
                Console.WriteLine("ex.State/t: {0}", ex.State);
         return false;
            }
        }

在

在这里我想特别说明一下SqlException.Number这个属性，它代表Database中的Error number[或者是@@ERROR、imessage_id]，不过当我们使用RAISERROR语句，如果我们指定的一个表示error message的字符串，ADO.NET捕获的SqlException.Number这个属性默认为50000。比如我们将Error number换成error message：

SET @error_message    = ERROR_MESSAGE()
SET @error_serverity    =ERROR_SEVERITY()
SET @error_state    = ERROR_STATE()
RAISERROR(@error_ message, @error_serverity,@error_state) 

 

将会得到这样的结果：

还有一点需要特别提醒得是，我们可以在调用RAISERROR加了一个WITH SETERROR重句，强制将当前@@ERROR的值返回到客户端：

七、    InfoMessage

上面的所以内容都围绕一个Exception handling的主题，在文章最后一部分我们想想一个和非Exception handling但是又和上面的内容很相关的主题：在Database通过Print语句输出的Message如何向Application传递。

在上面的例子中，有一个P_CLEAR_DATA的stored procedure，用于数据的清理。在操作结束后，有一个Print语句（PRINT ('All data have been deleted!')）

我们的现在的目标是在Application中，如何得到这段Message。要做到这点很简单，只需要用到SqlConnection的InfoMessage事件，当通过DbCommand执行上面一段Sql的时候，Print语句的执行将出发该事件。我们现在要做的就是注册这个事件，比如下面我们在ExecuteCommand（）种添加了下面一段代码：

CREATE Procedure P_CLEAR_DATA
AS

    DELETE FROM dbo.T_USERS_IN_ROLES
    DELETE FROM dbo.T_USERS
    DELETE FROM dbo.T_ROLES
    
    PRINT ('All data have been deleted!')
    
GO

 

 当我们调用Utility.Clear()的时候，就会输出"All data have been deleted!"

SqlConnection sqlConnection = connection as SqlConnection;
 if (sqlConnection != null)
    {
          sqlConnection.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs e)
          {
                        Console.WriteLine(e.Message);
           };
     }

 

RAISERROR(@error_message,@error_serverity,@error_state) WITH SETERROR

stored procedure的Error和ADO.NET捕获的Error的Mapping有一个更加清晰的认识。我们来写一个Sample，我们沿用Create User的例子：

 

sp_addmessage [ @msgnum = ] msg_id ,     [ @severity = ] severity , [ @msgtext = ] 'msg' 
     [ , [ @lang = ] 'language' ] 
     [ , [ @with_log = ] 'with_log' ] 
     [ , [ @replace = ] 'replace' ]

CREATE Procedure P_USERS_I
    (
        @user_id            varchar(50),
        @user_name    nvarchar(256)
    )
AS

IF(EXISTS(SELECT * FROM  dbo.T_USERS WHERE LOWERED_USER_NAME = LOWER(@user_name) OR [USER_ID] = @user_id))
    BEGIN
        RAISERROR ('This role is already existent',16,1)
    END

INSERT INTO dbo.T_USERS
           ([USER_ID]
           ,[USER_NAME]
           ,LOWERED_USER_NAME)
VALUES(@user_id, @user_name, LOWER(@user_name)) 

SQL Server Management Studio中执行任何一个语句的时候，如果遇到一个预定义的Error，会打印出相关的Error Message。这些Error Message到底是如何存储的呢？Error message和Error Numbder又是如何进行关联的呢？