谈谈基于SQL Server 的Exception Handling

谈谈基于SQL Server 的Exception Handling

分类: 2007-12-18 17:00 287人阅读 评论(0) 收藏 举报

对于所有的开发人员来说,Exception Handling是我们每天都要面对的事情。对于基于Source CodeException Handling,我想大家已经司空见惯了,但是对于Database级别的Exception Handling,就没有那么常见了。在这篇文章中,我将会介绍我对于基于Database编程中Exception Handling的一些粗浅的认识:在编写Stored Procedure时,如何抛出一个可预知的ExceptionADO.NET如何处理从Database抛出的Exception,如何保存基于Database ExceptionError 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

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699DECLARE @result INT
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
SET @result = 10/0
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
PRINT @@ERROR
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
Go
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699

会得到如下的执行结果:

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

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

 

 

 

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699UPDATE T_USERS_IN_ROLES SET [USER_ID] = 'dummy text'
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
IF @@ERROR = 547
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
PRINT 'The user is not existent'

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699

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

实际上,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来进行验证:

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699DECLARE @result INT
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
DECLARE @error INT
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
SET @result = 5/0
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
SET @error = @@ERROR
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
SELECT @error,sys.messages.* FROM sys.messages WHERE message_id = @error
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
Go
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699

下面是执行的结果:

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

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

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

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

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

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699CREATE Procedure P_USERS_I
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    (
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
@user_id            varchar(50),
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
@user_name    nvarchar(256),
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
@flag                INT OUTPUT
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    )
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
AS
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
IF(EXISTS(SELECT * FROM  dbo.T_USERS WHERE LOWERED_USER_NAME = LOWER(@user_nameOR [USER_ID] = @user_id))
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
BEGIN
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
SET @flag = 1
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
RETURN
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
END     
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
INSERT INTO dbo.T_USERS
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699           (
[USER_ID]
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699           ,
[USER_NAME]
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699           ,LOWERED_USER_NAME)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699     
VALUES(@user_id@user_nameLOWER(@user_name))     
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
SET @flag = @@ERROR

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699

很显然通过

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

二、       RAISEERROR

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

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699RAISERROR ( { msg_id | msg_str | @local_variable }
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    { ,severity ,state }
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
[ ,argument [ ,http://blog.weirong.li/wp-content/uploads/2016/09/2622f858-e4d5-4655-8229-20d38ee3f28a] ] )
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
[ WITH option [ ,http://blog.weirong.li/wp-content/uploads/2016/09/2622f858-e4d5-4655-8229-20d38ee3f28a] ]
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699

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

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

明白了RAISERROR如何使用了后,我们可以修改我们的先前创建UserStored Procedure

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

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699CREATE Procedure P_USERS_IN_ROLES_I
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    (
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
@user_name    NVARCHAR(256),
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
@role_name        NVARCHAR(256)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    )
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
AS
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
DECLARE @user_id    VARCHAR(50)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
DECLARE @role_id    VARCHAR(50)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
SELECT @user_id = [USER_ID] FROM dbo.T_USERS WHERE LOWERED_USER_NAME = LOWER(@user_name)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
IF(@user_id IS NULL)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
BEGIN
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
RAISERROR ('The user dose not exist',16,1)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
RETURN
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
END
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
SELECT @role_id = [ROLE_ID] FROM dbo.T_ROLES WHERE LOWERED_ROLE_NAME = LOWER(@role_name)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
IF(@role_id IS NULL)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
BEGIN
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
RAISERROR ('The role dose not exist',16,1)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
RETURN
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
END    
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
IF(EXISTS(SELECT * FROM T_USERS_IN_ROLES WHERE [USER_ID] = @user_id AND ROLE_ID = @role_id))
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
BEGIN
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
RAISERROR ('The user is already in the role',16,1
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
RETURN
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
END
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
INSERT INTO     dbo.T_USERS_IN_ROLES([USER_ID],ROLE_ID) VALUES(@user_id,@role_id)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699

虽然说在上面的

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

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699CREATE Procedure P_USERS_IN_ROLES_I
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    (
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
@user_name    NVARCHAR(256),
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
@role_name    NVARCHAR(256)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    )
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
AS
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
DECLARE @user_id    VARCHAR(50)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
DECLARE @role_id    VARCHAR(50)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
DECLARE @error_message    NVARCHAR(256
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
DECLARE @error_serverity    INT
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
DECLARE @error_state        INT
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
BEGIN TRY
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
SELECT @user_id = [USER_ID] FROM dbo.T_USERS WHERE LOWERED_USER_NAME = LOWER(@user_name)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
IF(@user_id IS NULL)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
BEGIN
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
RAISERROR ('The user dose not exist',16,1)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
END
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
SELECT @role_id = [ROLE_ID] FROM dbo.T_ROLES WHERE LOWERED_ROLE_NAME = LOWER(@role_name)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
IF(@role_id IS NULL)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
BEGIN
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
RAISERROR ('The role dose not exist',16,1)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
END
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
IF(EXISTS(SELECT * FROM T_USERS_IN_ROLES WHERE [USER_ID] = @user_id AND ROLE_ID = @role_id))
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
BEGIN
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
RAISERROR ('The user is already in the role',16,1
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
END    
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
INSERT INTO     dbo.T_USERS_IN_ROLES([USER_ID],ROLE_ID) VALUES(@user_id,@role_id)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
END TRY
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
BEGIN CATCH    
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
SET @error_ message    = ERROR_MESSAGE)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
SET @error_serverity    = ERROR_SEVERITY()
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
SET @error_state        = ERROR_STATE()
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
RAISERROR(@error_message,@error_serverity,@error_state)        
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
END CATCh
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699

当执行上面一个

在这里有一些需要注意的是:并非所有的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的添加、删除和修改:

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699sp_dropmessage [ @msgnum = ] message_number   [ , [ @lang = ] 'language' ]

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699sp_altermessage [ @message_id = ] message_number   , [ @parameter = ] 'write_to_log'   , [ @parameter_value = ] 'value' 

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

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699sp_addmessage    @msgnum = 50001,@severity = 16,@msgtext = N'This user is already existent' , @replace =  'replace' 
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
GO
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699sp_addmessage    
@msgnum = 50002,@severity = 16,@msgtext = N'This role is already existent'@replace =  'replace' 
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
Go
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699sp_addmessage    
@msgnum = 50003,@severity = 16,@msgtext = N'This user does not exist'@replace =  'replace' 
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
GO
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699sp_addmessage    
@msgnum = 50004,@severity = 16,@msgtext = N'This role does not exist'@replace =  'replace' 
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
GO
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699sp_addmessage    
@msgnum = 50005,@severity = 16,@msgtext = N'This user is already in the role'@replace =  'replace' 
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
GO
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699

[

五、    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
T_ROLES_I·         Add User in Role:P_USERS_IN_ROLES_I·         Delete Data: P_CLEAR_DATA4.   Common Function:Utility. ExecuteCommand

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699CREATE Procedure P_USERS_I
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    (
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
@user_id            varchar(50),
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
@user_name    nvarchar(256)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    )
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
AS
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
DECLARE @error_number    INT
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
DECLARE @error_serverity    INT
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
DECLARE @error_state        INT
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
BEGIN TRY
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
IF(EXISTS(SELECT * FROM  dbo.T_USERS WHERE LOWERED_USER_NAME = LOWER(@user_nameOR [USER_ID] = @user_id))
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
BEGIN
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
RAISERROR (50001,16,1)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
END
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
INSERT INTO dbo.T_USERS
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699           (
[USER_ID]
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699           ,
[USER_NAME]
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699           ,LOWERED_USER_NAME)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699     
VALUES(@user_id@user_nameLOWER(@user_name))   
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699     
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
END TRY
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
BEGIN CATCH
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
SET @error_number        = ERROR_NUMBER()
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
SET @error_serverity    =ERROR_SEVERITY()
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
SET @error_state        = ERROR_STATE()
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
RAISERROR(@error_number,@error_serverity,@error_state)        
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
END CATCH
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699

          ·         Create Role:

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699CREATE Procedure T_ROLES_I
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    (
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
@role_id            varchar(50),
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
@role_name    nvarchar(256)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    )
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
AS
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
DECLARE @error_number    INT
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
DECLARE @error_serverity    INT
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
DECLARE @error_state        INT
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
BEGIN TRY
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
IF(EXISTS(SELECT * FROM  dbo.T_ROLES WHERE LOWERED_ROLE_NAME = LOWER(@ROLE_nameOR [ROLE_ID] = @role_id))
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
BEGIN
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
RAISERROR (50002,16,1)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
END
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
INSERT INTO dbo.T_ROLES
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699           (
[ROLE_ID]
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699           ,
[ROLE_NAME]
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699           ,LOWERED_ROLE_NAME)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699     
VALUES(@ROLE_id@ROLE_nameLOWER(@ROLE_name))
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699     
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699     
END TRY
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
BEGIN CATCH
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
SET @error_number        = ERROR_NUMBER()
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
SET @error_serverity    =ERROR_SEVERITY()
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
SET @error_state        = ERROR_STATE()
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
RAISERROR(@error_number,@error_serverity,@error_state)        
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
END CATCH
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699

 

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699CREATE Procedure P_USERS_IN_ROLES_I
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    (
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
@user_name    NVARCHAR(256),
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
@role_name    NVARCHAR(256)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    )
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
AS
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
DECLARE @user_id    VARCHAR(50)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
DECLARE @role_id    VARCHAR(50)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
DECLARE @error_number    INT
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
DECLARE @error_serverity    INT
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
DECLARE @error_state        INT
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
BEGIN TRY
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
SELECT @user_id = [USER_ID] FROM dbo.T_USERS WHERE LOWERED_USER_NAME = LOWER(@user_name)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
IF(@user_id IS NULL)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
BEGIN
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
RAISERROR (50003,16,1)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
END
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
SELECT @role_id = [ROLE_ID] FROM dbo.T_ROLES WHERE LOWERED_ROLE_NAME = LOWER(@role_name)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
IF(@role_id IS NULL)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
BEGIN
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
RAISERROR (50004,16,1)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
END
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
IF(EXISTS(SELECT * FROM T_USERS_IN_ROLES WHERE [USER_ID] = @user_id AND ROLE_ID = @role_id))
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
BEGIN
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
RAISERROR (50005,16,1)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
END
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
INSERT INTO     dbo.T_USERS_IN_ROLES([USER_ID],ROLE_ID) VALUES(@user_id,@role_id)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699 
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
END TRY
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
BEGIN CATCH
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
SET @error_number        = ERROR_NUMBER()
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
SET @error_serverity    =ERROR_SEVERITY()
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
SET @error_state            = ERROR_STATE()
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
RAISERROR(@error_number,@error_serverity,@error_state)    
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
END CATCH
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699

 

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699CREATE Procedure P_CLEAR_DATA
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
AS
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
DELETE FROM dbo.T_USERS_IN_ROLES
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
DELETE FROM dbo.T_USERS
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
DELETE FROM dbo.T_ROLES
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
GO
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699

 

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699private const string connectionStringName = TestDb;
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
public static bool ExecuteCommand(string procedureName, Dictionary<stringobject> parameters)
http://blog.weirong.li/wp-content/uploads/2016/09/ac49643a-bf5f-493e-9cd8-b4de359ef0cc        
{
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            ConnectionStringSettings connectionStringSection 
= ConfigurationManager.ConnectionStrings[connectionStringName];
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            DbProviderFactory dbProviderFactory 
= DbProviderFactories.GetFactory(connectionStringSection.ProviderName);
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            
using (DbConnection connection = dbProviderFactory.CreateConnection())
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3            
{
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                connection.ConnectionString 
= connectionStringSection.ConnectionString;
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                connection.Open();
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                DbCommand command 
= connection.CreateCommand();
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                command.CommandText 
= procedureName;
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                command.CommandType 
= CommandType.StoredProcedure;
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                DbParameter parameter;
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                
foreach (KeyValuePair<stringobject> param in parameters)
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3                
{
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                    parameter 
= dbProviderFactory.CreateParameter();
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                    parameter.ParameterName 
= param.Key;
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                    parameter.DbType 
= DbType.String;
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                    parameter.Value 
= param.Value;
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                    command.Parameters.Add(parameter); 
http://blog.weirong.li/wp-content/uploads/2016/09/e2d9427e-99ff-4d47-96cd-b6d255d08138                }

http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                DbTransaction transation 
= connection.BeginTransaction();
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                command.Transaction 
= transation;
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                
try
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3                
{
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                    command.ExecuteNonQuery();
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                    transation.Commit();
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                    
return true;
http://blog.weirong.li/wp-content/uploads/2016/09/e2d9427e-99ff-4d47-96cd-b6d255d08138                }

http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                
catch 
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3                
{
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                    transation.Rollback();
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                    
throw;
http://blog.weirong.li/wp-content/uploads/2016/09/e2d9427e-99ff-4d47-96cd-b6d255d08138                }

http://blog.weirong.li/wp-content/uploads/2016/09/e2d9427e-99ff-4d47-96cd-b6d255d08138            }

http://blog.weirong.li/wp-content/uploads/2016/09/149c6c50-5eef-46ce-ad42-31db5e7a2529}

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

5.  

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        private const string ErrorUserExists = This user is already existent;
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
private const string ErrorRoleExists = This role is already existent;
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
private const string ErrorUserNotExists = This user does not exist;
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
private const string ErrorRoleNotExists = This role does not exist;
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
private const string ErrorUserInRole = This user is already in the role;
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
public static  bool CreateUser(string userName)
http://blog.weirong.li/wp-content/uploads/2016/09/ac49643a-bf5f-493e-9cd8-b4de359ef0cc        
{
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            
string procedureName = P_USERS_I;
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            Dictionary
<stringobject> parameters = new Dictionary<stringobject>();
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            parameters.Add(
user_id, Guid.NewGuid().ToString());
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            parameters.Add(
user_name, userName);
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            
try
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3            
{
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                ExecuteCommand(procedureName, parameters);
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                
return true;
http://blog.weirong.li/wp-content/uploads/2016/09/e2d9427e-99ff-4d47-96cd-b6d255d08138            }

http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            
catch (Exception ex)
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3            
{
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                
if (ex.Message == ErrorUserExists)
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3                
{
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3                    Console.WriteLine(
The user /{0}/ you specify is already existent!,userName);
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                    
return false; ;
http://blog.weirong.li/wp-content/uploads/2016/09/e2d9427e-99ff-4d47-96cd-b6d255d08138                }

http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                Console.WriteLine(
A unhandled exception is thrown for some unknown reason!);
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                
return false;
http://blog.weirong.li/wp-content/uploads/2016/09/e2d9427e-99ff-4d47-96cd-b6d255d08138            }

http://blog.weirong.li/wp-content/uploads/2016/09/149c6c50-5eef-46ce-ad42-31db5e7a2529        }

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
public static bool CreateRole(string roleName)
http://blog.weirong.li/wp-content/uploads/2016/09/ac49643a-bf5f-493e-9cd8-b4de359ef0cc        
{
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            
string procedureName = P_ROLES_I;
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            Dictionary
<stringobject> parameters = new Dictionary<stringobject>();
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            parameters.Add(
role_id, Guid.NewGuid().ToString());
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            parameters.Add(
role_name, roleName);
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            
try
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3            
{
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                ExecuteCommand(procedureName, parameters);
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                
return true;
http://blog.weirong.li/wp-content/uploads/2016/09/e2d9427e-99ff-4d47-96cd-b6d255d08138            }

http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            
catch (Exception ex)
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3            
{
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                
if (ex.Message==ErrorRoleExists)
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3                
{
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3                    Console.WriteLine(
The role /{0}/ you specify is already existent!,roleName);
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                    
return false; ;
http://blog.weirong.li/wp-content/uploads/2016/09/e2d9427e-99ff-4d47-96cd-b6d255d08138                }

http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                Console.WriteLine(
A unhandled exception is thrown for some unknown reason!);
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                
return false;
http://blog.weirong.li/wp-content/uploads/2016/09/e2d9427e-99ff-4d47-96cd-b6d255d08138            }

http://blog.weirong.li/wp-content/uploads/2016/09/149c6c50-5eef-46ce-ad42-31db5e7a2529        }

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
public static bool AddUserInRole(string userName, string roleName)
http://blog.weirong.li/wp-content/uploads/2016/09/ac49643a-bf5f-493e-9cd8-b4de359ef0cc        
{
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            
string procedureName = P_USERS_IN_ROLES_I;
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            Dictionary
<stringobject> parameters = new Dictionary<stringobject>();
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            parameters.Add(
user_name, userName);
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            parameters.Add(
role_name, roleName);
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            
try
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3            
{
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                ExecuteCommand(procedureName, parameters);
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                
return true;
http://blog.weirong.li/wp-content/uploads/2016/09/e2d9427e-99ff-4d47-96cd-b6d255d08138            }

http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            
catch (Exception ex)
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3            
{
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                
if (ex.Message==ErrorUserNotExists)
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3                
{
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3                    Console.WriteLine(
The user /{0}/ you specify is not existent!, userName);
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                    
return false;
http://blog.weirong.li/wp-content/uploads/2016/09/e2d9427e-99ff-4d47-96cd-b6d255d08138                }

http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                
if (ex.Message==ErrorRoleNotExists)
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3                
{
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3                    Console.WriteLine(
The role /{0}/ you specify is not existent!,roleName);
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                    
return false;
http://blog.weirong.li/wp-content/uploads/2016/09/e2d9427e-99ff-4d47-96cd-b6d255d08138                }

http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                
if (ex.Message == ErrorUserInRole)
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3                
{
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3                    Console.WriteLine(
The user /{0}/ is in the role /{1}/!,userName,roleName);
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                    
return false;
http://blog.weirong.li/wp-content/uploads/2016/09/e2d9427e-99ff-4d47-96cd-b6d255d08138                }

http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                Console.WriteLine(
A unhandled exception is thrown for some unknown reason!);
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                
return false;
http://blog.weirong.li/wp-content/uploads/2016/09/e2d9427e-99ff-4d47-96cd-b6d255d08138            }

http://blog.weirong.li/wp-content/uploads/2016/09/149c6c50-5eef-46ce-ad42-31db5e7a2529 }

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699  
public static void Clear()
http://blog.weirong.li/wp-content/uploads/2016/09/ac49643a-bf5f-493e-9cd8-b4de359ef0cc        
{
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            ExecuteCommand(
P_CLEAR_DATAnew Dictionary<stringobject>());
http://blog.weirong.li/wp-content/uploads/2016/09/149c6c50-5eef-46ce-ad42-31db5e7a2529        }

 

6.      Programs

7.   最终执行结果

六、SqlException

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699class Program
http://blog.weirong.li/wp-content/uploads/2016/09/ac49643a-bf5f-493e-9cd8-b4de359ef0cc    
{
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93        
static void Main(string[] args)
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3        
{
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            Utility.Clear();
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            
if (Utility.CreateUser(Artech))
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3            
{
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3                Console.WriteLine(
The user /{0}/ has been sucessully created!Artech);
http://blog.weirong.li/wp-content/uploads/2016/09/e2d9427e-99ff-4d47-96cd-b6d255d08138            }

http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            
if (Utility.CreateUser(Artech))
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3            
{
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3                Console.WriteLine(
The user /{0}/ has been sucessully created!Artech);
http://blog.weirong.li/wp-content/uploads/2016/09/e2d9427e-99ff-4d47-96cd-b6d255d08138            }

http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            
if (Utility.CreateRole(Administrator))
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3            
{
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3                Console.WriteLine(
/nThe role /{0}/ has been sucessully created!Administrator);
http://blog.weirong.li/wp-content/uploads/2016/09/e2d9427e-99ff-4d47-96cd-b6d255d08138            }

http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            
if (Utility.CreateRole(Administrator))
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3            
{
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3                Console.WriteLine(
The role /{0}/ has been sucessully created!Administrator);
http://blog.weirong.li/wp-content/uploads/2016/09/e2d9427e-99ff-4d47-96cd-b6d255d08138            }

http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            
if (Utility.AddUserInRole(ArtechAdministrator))
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3            
{
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3                Console.WriteLine(
/nThe user /{0}/ has been successfully added in the role /{1}/“”ArtechAdministrator);
http://blog.weirong.li/wp-content/uploads/2016/09/e2d9427e-99ff-4d47-96cd-b6d255d08138            }

http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            
if (Utility.AddUserInRole(Dave CraneAdministrator))
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3            
{
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3                Console.WriteLine(
The user /{0}/ has been successfully added in the role /{1}/“”Dave CraneAdministrator);
http://blog.weirong.li/wp-content/uploads/2016/09/e2d9427e-99ff-4d47-96cd-b6d255d08138            }

http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            
if (Utility.AddUserInRole(ArtechSuper Administrator))
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3            
{
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3                Console.WriteLine(
The user /{0}/ has been successfully added in the role /{1}/“”ArtechSuper Administrator);
http://blog.weirong.li/wp-content/uploads/2016/09/e2d9427e-99ff-4d47-96cd-b6d255d08138            }

http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            
if (Utility.AddUserInRole(ArtechAdministrator))
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3            
{
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3                Console.WriteLine(
The user /{0}/ has been successfully added in the role /{1}/“”ArtechAdministrator);
http://blog.weirong.li/wp-content/uploads/2016/09/e2d9427e-99ff-4d47-96cd-b6d255d08138            }

http://blog.weirong.li/wp-content/uploads/2016/09/e2d9427e-99ff-4d47-96cd-b6d255d08138        }

http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93

 

在上面一节中,我给出了一个完整的例子说明了:如何在将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抛出异常[在整篇文章中,使用到ErrorException,大家可以看成是等效的]
    ·         Error Number50001
    ·         Severity16
    ·         State1
    ·         MessageThis user is already existent

我们来修正一下CreateUser方法:

Main()中调用这个CreateUser():

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699public static  bool CreateUser(string userName)
http://blog.weirong.li/wp-content/uploads/2016/09/ac49643a-bf5f-493e-9cd8-b4de359ef0cc        
{
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            
string procedureName = P_USERS_I;
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            Dictionary
<stringobject> parameters = new Dictionary<stringobject>();
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            parameters.Add(
user_id, Guid.NewGuid().ToString());
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            parameters.Add(
user_name, userName);
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            
try
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3            
{
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                ExecuteCommand(procedureName, parameters);
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                
return true;
http://blog.weirong.li/wp-content/uploads/2016/09/e2d9427e-99ff-4d47-96cd-b6d255d08138            }

http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93            
catch (SqlException ex)
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3            
{
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                Console.WriteLine(
ex.Class/t: {0}, ex.Class);
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                Console.WriteLine(
ex.ErrorCode/t: {0}, ex.ErrorCode);
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                Console.WriteLine(
ex.LineNumber/t: {0}, ex.LineNumber);
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                Console.WriteLine(
ex.Message/t: {0}, ex.Message);
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                Console.WriteLine(
ex.Number/t: {0}, ex.Number);
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                Console.WriteLine(
ex.Procedure/t: {0}, ex.Procedure);
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                Console.WriteLine(
ex.Server/t: {0}, ex.Server);
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                Console.WriteLine(
ex.Source/t: {0}, ex.Source);
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                Console.WriteLine(
ex.State/t: {0}, ex.State);
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93         
return false;
http://blog.weirong.li/wp-content/uploads/2016/09/e2d9427e-99ff-4d47-96cd-b6d255d08138            }

http://blog.weirong.li/wp-content/uploads/2016/09/149c6c50-5eef-46ce-ad42-31db5e7a2529        }

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699

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

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699SET @error_message    = ERROR_MESSAGE()
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
SET @error_serverity    =ERROR_SEVERITY()
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
SET @error_state    = ERROR_STATE()
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
RAISERROR(@error_ message, @error_serverity,@error_state
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699

 

将会得到这样的结果:

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

七、    InfoMessage

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

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

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

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699CREATE Procedure P_CLEAR_DATA
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
AS
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
DELETE FROM dbo.T_USERS_IN_ROLES
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
DELETE FROM dbo.T_USERS
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
DELETE FROM dbo.T_ROLES
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
PRINT ('All data have been deleted!')
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
GO
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699

 

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

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699SqlConnection sqlConnection = connection as SqlConnection;
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699 
if (sqlConnection != null)
http://blog.weirong.li/wp-content/uploads/2016/09/ac49643a-bf5f-493e-9cd8-b4de359ef0cc    
{
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93          sqlConnection.InfoMessage 
+= delegate(object sender, SqlInfoMessageEventArgs e)
http://blog.weirong.li/wp-content/uploads/2016/09/36571739-fc71-4123-b140-2bd2de02e6a3          
{
http://blog.weirong.li/wp-content/uploads/2016/09/91ca8df5-873b-4bb2-bf21-25b10bd4ba93                        Console.WriteLine(e.Message);
http://blog.weirong.li/wp-content/uploads/2016/09/e2d9427e-99ff-4d47-96cd-b6d255d08138           }
;
http://blog.weirong.li/wp-content/uploads/2016/09/149c6c50-5eef-46ce-ad42-31db5e7a2529     }

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699

 

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699RAISERROR(@error_message,@error_serverity,@error_stateWITH SETERROR

stored procedureErrorADO.NET捕获的ErrorMapping有一个更加清晰的认识。我们来写一个Sample,我们沿用Create User的例子:

 

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699sp_addmessage [ @msgnum = ] msg_id ,     [ @severity = ] severity , [ @msgtext = ] 'msg' 
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699     
[ , [ @lang = ] 'language' ] 
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699     
[ , [ @with_log = ] 'with_log' ] 
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699     
[ , [ @replace = ] 'replace' ]
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699

http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699CREATE Procedure P_USERS_I
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    (
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
@user_id            varchar(50),
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
@user_name    nvarchar(256)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    )
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
AS
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
IF(EXISTS(SELECT * FROM  dbo.T_USERS WHERE LOWERED_USER_NAME = LOWER(@user_nameOR [USER_ID] = @user_id))
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
BEGIN
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699        
RAISERROR ('This role is already existent',16,1)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699    
END
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
INSERT INTO dbo.T_USERS
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699           (
[USER_ID]
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699           ,
[USER_NAME]
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699           ,LOWERED_USER_NAME)
http://blog.weirong.li/wp-content/uploads/2016/09/33061bfa-3fed-4c3c-bc1b-e5bdd8e48699
VALUES(@user_id@user_nameLOWER(@user_name)) 

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

此条目发表在Uncategorized分类目录,贴了标签。将固定链接加入收藏夹。