谈谈基于SQL Server 的Exception Handling[中篇]

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

FORALL 之 SAVE EXCEPTIONS 子句应用一例

     对于大批量的DML操作中出现的错误,除了使用DML error logging特性来记录在DML期间出现的错误之外,使用批量SQL语句FORALL的S...

7710
来自专栏乐沙弥的世界

Oracle Execute to Parse 执行解析比分析

15830
来自专栏杨建荣的学习笔记

关于exp/imp的总结学习(r4笔记第26天)

关于exp/imp,是很常用的数据导出导入工具,在10g开始推出的数据泵datapump相当于是exp/imp的补充和升级版本。在后续章节再做一个总结。 exp...

31870
来自专栏乐沙弥的世界

Oracle RAC环境下配置statspack

    Statspack是Oracle 9i时代的产物,对于监控与分析数据库性能有着跨里程碑的意义,是AWR的前身。在Oracle 10g后AWR取代了sta...

7630
来自专栏杨建荣的学习笔记

关于dual表的破坏性测试(r3笔记第60天)

关于dual表的破坏性测试,既然是破坏性测试,就需要确定这个测试仅限于测试或者个人学习所用,可能有些sql看似极为简单,但是一旦运行就会导致整个业务系统崩溃。 ...

409130
来自专栏菩提树下的杨过

mybatis 使用经验小结

一、多数据源问题 主要思路是把dataSource、sqlSesstionFactory、MapperScannerConfigurer在配置中区分开,各Map...

30360
来自专栏杨建荣的学习笔记

不经意发现的dba_objects和dba_tables中的细节(r7笔记第56天)

今天有一个同学问我一个问题,因为白天比较忙也没有在意,在下班后坐地铁的时候抽空看了这个问题,感觉还是蛮有意思的。但是当时也没有任何答案,就准备自己回去好好实验一...

40030
来自专栏乐沙弥的世界

PL/SQL 包编译时hang住的处理

       最近PL/SQL包在编译时被hang住,起初以为是所依赖的对象被锁住。结果出乎意料之外。下面直接看代码演示。

9960
来自专栏乐沙弥的世界

PL/SQL-->UTL_FILE包的使用介绍

    在PL/SQL中,UTL_FILE包提供了文本文件输入和输出互功能。也就是说我们可以通过该包实现从操作系统级别来实现文件读取输入或者是写入到操作系统文件...

11010
来自专栏恰童鞋骚年

轻量级ORM框架初探-Dapper与PetaPoco的基本使用

  EF是传统的ORM框架,也是一个比较重量级的ORM框架。这里仍然使用EF的原因在于为了突出轻量级ORM框架的性能,所谓有对比才有更优的选择。

29130

扫码关注云+社区

领取腾讯云代金券