上一篇中介绍了在 SQL Server 中实现自治事务的两种方法。这一篇主要解决上次提供的 CLR 存储过程中存在的几点遗憾,提供更实用的代码。
程序集代码:
/*
* csc.exe /r:c:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/System.Transactions.dll /target:library AutoTranClass.cs
*/
using System;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Transactions;
public class AutoTran
{
[SqlProcedure]
public static void ExecuteATStmt(SqlString sql, SqlString dbname, SqlInt16 iso)
{
// 创建不加入环境事务的事务作用域
using (TransactionScope trans = new TransactionScope(TransactionScopeOption.Suppress))
{
using (SqlConnection conn = new SqlConnection("context connection=true" ))
{
conn.Open();
// 获取过程调用者的安全上下文
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select suser_name()" ;
string loginame = (string )cmd.ExecuteScalar();
// 创建 LOOPBACK 连接(禁止使用连接池)
using (SqlConnection connAT =
new SqlConnection("server=.;integrated security=true;database=master;pooling=false" ))
{
connAT.Open();
connAT.ChangeDatabase((string )dbname);
SqlCommand cmdAT = connAT.CreateCommand();
// 定义自治事务的事务隔离等级
string level;
switch ((Int16 )iso)
{
case 1:
level = "read uncommitted" ;
break ;
case 3:
level = "repeatable read" ;
break ;
case 4:
level = "serializable" ;
break ;
case 5:
level = "snapshot" ;
break ;
default :
level = "read committed" ;
break ;
}
cmdAT.CommandText = "set transaction isolation level " + level;
cmdAT.ExecuteNonQuery();
/ / 将 CLR 存储过程运行的安全上下文切换到调用者的,从而避免 CLR 过程运行在 SQL Server 服务进程的安全上下文中
cmdAT.CommandText = "execute as login = '" + loginame + "' with no revert" ;
cmdAT.ExecuteNonQuery();
// 执行 SQL 语句
cmdAT.CommandText = (string )sql;
cmdAT.ExecuteNonQuery();
}
}
trans.Complete();
}
}
}
CLR 存储过程代码:
use master
go
exec sp_configure 'show advanced options' , 1;
go
reconfigure
go
exec sp_configure 'clr enabled' , 1;
go
reconfigure
go
alter database master set trustworthy on ;
go
if object_id ( 'dbo.ExecuteATStmt' ) is not null
drop procedure dbo. ExecuteATStmt;
go
if exists ( select * from sys.assemblies where name = 'AutoTran' )
drop assembly AutoTran;
go
create assembly AutoTran authorization dbo
from 'C:/Devs/Projects/clrAutoTran/clrAutoTran/bin/Release/clrAutoTran.dll'
with permission_set= external_access;
go
create procedure dbo. ExecuteATStmt
@stmt nvarchar ( max ), @dbname nvarchar ( 128)= 'master' , @iso smallint = 2
as external name AutoTran. AutoTran. ExecuteATStmt;
go
演示:
use tempdb
go
if object_id ( 'emps' ) is not null
drop table emps;
go
create table emps ( id int identity , fname varchar ( 20), salary numeric ( 8, 2));
go
insert into emps ( fname, salary) values ( 'Tom' , 4000);
insert into emps ( fname, salary) values ( 'Jerry' , 6000);
go
if object_id ( 'emp_logs' ) is not null
drop table emp_logs;
go
create table emp_logs ( id int identity , loginame sysname , spid int , stmt nvarchar ( 4000));
go
-- 触发器用于捕获 / 记录所用对 EMPS 表 salary 列的更新操作
if object_id ( 'trg_emps_update' ) is not null
drop trigger trg_emps_update;
go
create trigger trg_emps_update on emps
with execute as owner
for update as
set nocount on ;
if update (salary)
begin
declare @event table ( EventType nvarchar ( 30), Parameters int , EventInfo nvarchar ( 4000));
insert into @event exec ( 'dbcc inputbuffer(@@spid) with no_infomsgs' );
declare @stmt nvarchar ( max );
set @stmt= N'insert into emp_logs(loginame,spid,stmt) ' +
N'select ''' + original_login()+ ''',' + ltrim ( @@spid )+ ',' ;
select @stmt= @stmt+ '''' + replace ( EventInfo, '''' , '''''' )+ '''' from @event;
exec master. dbo. ExecuteATStmt @stmt, 'tempdb' ;
end
go
-- 测试,即使 UPDATE 动作被回滚,也会产生记录
begin tran
declare @salary varchar ( 8);
set @salary= '10000' ;
update emps set salary= salary;
rollback tran
select * from emp_logs;