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

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介:
三、    TRY CATCH & Return

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

现在我们来写将user添加到RoleStored Procedure:首先验证UserRole是否存在,然后验证该UserRole是否已经存在,最后将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 TRYBEGIN 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() :返回出现ErrorStored Procedure名称.

四、    Error message & sys.messages

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

在前面的部分我们说过,sys.messages是可以用于专门存放Error相关的信息:Error number, severitystatemessage等。而且他不但可以用于系统与定义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(通过上面一节末Scriptsys.messages中创建):
·         50001This user is already existent
·        
50002This role is already existent
·         50003This user does not exist
·         50004This role does not exist
·         50005This user is already in the role

3.   Stored procedure
·         Create UserP_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_nameOR  [ 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_nameLOWER( @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_nameOR  [ 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_nameLOWER( @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< stringobject> 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<stringobject> 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<stringobject> parameters = new Dictionary<stringobject>();
            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<stringobject> parameters = new Dictionary<stringobject>();
            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<stringobject> parameters = new Dictionary<stringobject>();
            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<stringobject>());
        }

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 


作者:蒋金楠
微信公众账号:大内老A
微博: www.weibo.com/artech
如果你想及时得到个人撰写文章以及著作的消息推送,或者想看看个人推荐的技术资料,可以扫描左边二维码(或者长按识别二维码)关注个人公众号(原来公众帐号 蒋金楠的自媒体将会停用)。
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
相关文章
|
2月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
4月前
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
89 13
|
4月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
|
4月前
|
SQL 存储 网络安全
关系数据库SQLserver 安装 SQL Server
【7月更文挑战第26天】
57 6
|
3月前
|
SQL 安全 Java
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
378 0
|
4月前
|
存储 SQL C++
对比 SQL Server中的VARCHAR(max) 与VARCHAR(n) 数据类型
【7月更文挑战7天】SQL Server 中的 VARCHAR(max) vs VARCHAR(n): - VARCHAR(n) 存储最多 n 个字符(1-8000),适合短文本。 - VARCHAR(max) 可存储约 21 亿个字符,适合大量文本。 - VARCHAR(n) 在处理小数据时性能更好,空间固定。 - VARCHAR(max) 对于大文本更合适,但可能影响性能。 - 选择取决于数据长度预期和业务需求。
266 1
|
4月前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
246 3
|
4月前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。
|
4月前
|
SQL 监控 数据库
SQL Server 查询超时问题排查
【7月更文挑战第8天】排查 SQL Server 查询超时涉及五个主要方面:检查复杂查询、评估服务器性能、审视配置参数、更新统计信息和分析执行计划。关注点包括查询的结构(如连接、子查询和索引),服务器资源(CPU、内存、网络延迟),连接和内存设置,以及统计信息的时效性。通过这些步骤可定位并解决性能瓶颈。
110 0
|
4月前
|
SQL 存储 Oracle
TDengine 3.3.2.0 发布:新增 UDT 及 Oracle、SQL Server 数据接入
**TDengine 3.3.2.0 发布摘要** - 开源与企业版均强化性能,提升WebSocket、stmt模式写入与查询效率,解决死锁,增强列显示。 - taos-explorer支持geometry和varbinary类型。 - 企业版引入UDT,允许自定义数据转换。 - 新增Oracle和SQL Server数据接入。 - 数据同步优化,支持压缩,提升元数据同步速度,错误信息细化,支持表名修改。 - 扩展跨平台支持,包括麒麟、Euler、Anolis OS等。
109 0