SQL Server 自治事务（续）

上一篇中介绍了在 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;