SQL Server 自治事务（CLR 实现）

xman_78tom 2010-09-30 11:21:01

加精

接触过 ORACLE 的用户一定对“自治事务”不会陌生，那么在 SQL Server 中如何实现这个功能？请看下面。

何为自治事务？简单的说，就是让内部的事务可以独立的提交和回滚，不受外部事务的影响。要具体了解，请看我的 BLOG。

DLL 文件



/* 

 * 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;