/* 
 * 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();
        }
    }
}

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;