三、    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中：

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)

虽然说在上面的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。

通过TRY CATCH，上面的Stored procedure可以改成下面的样子：

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

当执行上面一个SQL的时候，碰到任何一个我们自己抛出的Exception和系统异常，都会跳到Catch Block中执行相应的操作。在CATCH中，我们把在TRY Block中遇到的Error从新抛出。

在这里有一些需要注意的是：并非所有的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_addmessage  [  @msgnum =  ]  msg_id ,      [  @severity =  ]  severity ,  [  @msgtext =  ]   ' msg '  
      [  , [ @lang =  ]   ' language '  ] 
      [  , [ @with_log =  ]   ' with_log '  ] 
      [  , [ @replace =  ]   ' replace '  ]

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。

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

[注：直接操作sys.messages是不被允许的]

五、    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

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：T_ROLES_I

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

·         Add User in Role：P_USERS_IN_ROLES_I

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

·         Delete Data: P_CLEAR_DATA

CREATE   Procedure  P_CLEAR_DATA
AS

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

4．   Common Function：Utility. ExecuteCommand

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;
                }
            }
}

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

         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

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");
            }
        }

7．   最终执行结果


[原创]谈谈基于SQL Server的Exception Handling - PART I
[原创]谈谈基于SQL Server 的Exception Handling - PART II
[原创]谈谈基于SQL Server 的Exception Handling - PART III 

最后

以上就是温暖季节为你收集整理的[原创]谈谈基于SQL Server 的Exception Handling - PART II四、    Error message & sys.messages五、    ADO.NET Exception Handling的全部内容，希望文章能够帮你解决[原创]谈谈基于SQL Server 的Exception Handling - PART II四、    Error message & sys.messages五、    ADO.NET Exception Handling所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。