|
三、??? 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是否存在，然后验证该Role是否已经存在，最后将Mapping关系添加到T_USERS_IN_ROLES中：
CREATE?Procedure?P_USERS_IN_ROLES_I
????(
????????@user_name????NVARCHAR(256),
????????@role_name????????)
????)
AS
DECLARE@user_idVARCHAR50)
@role_idSELECT?=?[USER_ID]FROM?dbo.T_USERS?WHERE?LOWERED_USER_NAME?LOWER(IFISNULL)
????BEGIN
????????RAISERROR?('The?user?dose?not?exist',161)
????????RETURN
????END
????
ROLE_ID?dbo.T_ROLES??LOWERED_ROLE_NAME?The?role?dose?not?exist????
(EXISTS*?T_USERS_IN_ROLES?AND?ROLE_ID?))
????The?user?is?already?in?the?role)?
????????INSERTINTO?????dbo.T_USERS_IN_ROLES(VALUES)
虽然说在上面的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可以改成下面的样子：
?P_USERS_IN_ROLES_I
????(
????????????256????????)
????)
50)
)
@error_message)?
@error_serverityINT@error_state????????
?TRY
)
????
????????161
????
))
????)?
????????
?CATCH????
????SET?????ERROR_MESSAGE)
?????ERROR_SEVERITY()
?????????????ERROR_STATE()
????)????????
?CATCh
当执行上面一个SQL的时候，碰到任何一个我们自己抛出的Exception和系统异常，都会跳到Catch Block中执行相应的操作。在CATCH中，我们把在TRY Block中遇到的Error从新抛出。
在这里有一些需要注意的是：并非所有的Error都会使用SQL的执行流入Catch Block，下面是两个主要的例外：
此外，相信大家也看见了在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_logreplace?]
sp_dropmessage??message_number????]
sp_altermessage??@message_id?=??message_number???,1)">?@parameter?=?write_to_log???,1)">?@parameter_value?=?value?
关于如何使用这些stored procedure，可以参阅SQL Server Books Online。在这里，我同下面的script添加我需要的Error。
sp_addmessage????@msgnum?50001@severity@msgtext?NThis?user?is?already?existent@replace???
GO
sp_addmessage????50002This?role?is?already?existentGo50003This?user?does?not?exist50004This?role?does?not?exist50005This?user?is?already?in?the?roleGO
[注：直接操作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
?P_USERS_I
????(
???????? ????????????varcharnvarchar
@error_number?TRY
??dbo.T_USERS?)?OR?(?dbo.T_USERS
???????????(
???????????,USER_NAME?????))???
?????
?CATCH
????
?????ERROR_NUMBER()
????ERROR_SEVERITY()
?????ERROR_STATE()
????)????????
????????
?CATCH
??????????·???????? Create Role：T_ROLES_I
?T_ROLES_I
????(
???????? ??dbo.T_ROLES?@ROLE_name?dbo.T_ROLES
???????????(ROLE_NAME?????@ROLE_id))
?????
?????)????????
?CATCH
·???????? Add User in Role：P_USERS_IN_ROLES_I
)
)
?
????????????)????
?CATCH
·???????? Delete Data: P_CLEAR_DATA
?P_CLEAR_DATA
????DELETE?dbo.T_USERS_IN_ROLES
?????dbo.T_USERS
?????dbo.T_ROLES
????
4．?? Common Function：Utility. ExecuteCommand
private conststring?connectionStringName?"TestDb;
publicstaticbool?ExecuteCommand(?procedureName,?Dictionary<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?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();
????????????????????returntrue;
????????????????}
????????????????catch?
????????????????
{
????????????????????transation.Rollback();
????????????????????throw
????????????}
}
5．?? Create User,Create Role,Add User In Role,Delete All Data
?ErrorUserExists? This?user?is?already?existent;
?????????ErrorRoleExists?This?role?is?already?existent?ErrorUserNotExists?This?user?does?not?exist?ErrorRoleNotExists?This?role?does?not?exist?ErrorUserInRole?This?user?is?already?in?the?role;
???????????CreateUser(?userName)
????????
{
?????????????procedureName?P_USERS_I;
????????????Dictionary?parameters?new?Dictionary();
????????????parameters.Add(user_id????????????parameters.Add(user_name????????????
????????????
{
????????????????ExecuteCommand(procedureName,?parameters);
????????????????;
????????????}
?????????????(Exception?ex)
????????????
{
????????????????if?(ex.Message?==?ErrorUserExists)
????????????????
{
????????????????????Console.WriteLine(The?user?"
{0}?you?specify?is?already?existent!????????????????????false;?;
????????????????}
????????????????Console.WriteLine(A?unhandled?exception?is?thrown?for?some?unknown?reason!);
????????????????
????????}
?????????CreateRole(?roleName)
????????
P_ROLES_Irole_idrole_name????????????
????????????
?(Exception?ex)
????????????
?(ex.MessageErrorRoleExists)
????????????????
{
????????????????????Console.WriteLine(The?role?"
?????????????????????AddUserInRole(?userName,1)">?roleName)
????????
P_USERS_IN_ROLES_I????????????parameters.Add(
????????????
?(Exception?ex)
????????????
ErrorUserNotExists)
????????????????
{
????????????????????Console.WriteLine("
?you?specify?is?not?existent!????????????????????ErrorRoleNotExists)
????????????????
{
????????????????????Console.WriteLine("
?ErrorUserInRole)
????????????????
{
????????????????????Console.WriteLine("
?is?in?the?role?"
!
?}
??void?Clear()
????????
{
????????????ExecuteCommand(P_CLEAR_DATA());
????????}
6．??? ??Programs
class ?Program
????
{
?????????Main([]?args)
????????
{
????????????Utility.Clear();
?????????????(Utility.CreateUser(Artech))
????????????
{
????????????????Console.WriteLine("
?has?been?sucessully?created!);
????????????}))
????????????
{
????????????????Console.WriteLine("
?(Utility.CreateRole(Administrator))
????????????
{
????????????????Console.WriteLine(nThe?role?"
))
????????????
{
????????????????Console.WriteLine("
?(Utility.AddUserInRole())
????????????
{
????????????????Console.WriteLine(nThe?user?"
?has?been?successfully?added?in?the?role?"
""
????????????Dave?Crane))
????????????
{
????????????????Console.WriteLine("
"
Super?Administrator))
????????????
{
????????????????Console.WriteLine("
"
))
????????????
{
????????????????Console.WriteLine("
"
????????}
7．?? 最终执行结果
[原创]谈谈基于SQL Server的Exception Handling - PART I
[原创]谈谈基于SQL Server 的Exception Handling - PART II
[原创]谈谈基于SQL Server 的Exception Handling - PART III? （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
