7.8  CLR存储过程

SQL Server 2005允许使用任意 .NET语言开发CLR存储过程（以及其他程序一样）。上一章介绍了一些关于CLR程序的基础知识，给出了在什么情况下用CLR程序代替T-SQL的建议，并描述了如何开发CLR程序。 附录A包含了开发、生成、部署和测试 .NET代码的操作指南。在这里，我将演示两个CLR存储过程的示例，它提供了T-SQL代码所不具备的功能。

第一个示例是一个名为usp_GetEnvInfo 的CLR存储过程。该存储过程收集环境变量中的信息并把它们以表格形式返回。该存储过程返回的环境变量包括：机器名称、处理器、OS版本、CLR版本。

为了收集环境变量中的信息，程序集需要对操作系统资源进行外部访问。创建程序集时（使用CREATE ASSEMBLY命令），默认使用限制最高的PERMISSION＿SET选项SAFE，这意味它们被限制为只能访问数据库资源。这是推荐的选项，这样可以获取最大的安全性和稳定性。权限集选项EXTERNAL_ACCESS 和UNSAFE（在CREATE ASSEMBLY 或 ALTER ASSEMBLY命令指定，或者在Visual Studio 中工程 | 属性对话框上的数据库选项卡指定）允许对文件、网络、环境变量或注册表这样的系统资源进

行外部访问。要允许EXTERNAL_ACCESS 和UNSAFE程序集运行，你需要把数据库选项TRUSTWORTHY设置为ON。允许EXTERNAL_ACCESS 或UNSAFE程序集运行会存在安全风险，应该避免这样做。稍后我将描述一个更为安全的解决方案，我先演示EXTERNAL_ACCESS选项。要把CLRUtilities数据库的TRUSTWORTHY选项设置为ON，并把CLRUtilities程序集的权限集改为EXTERNAL_ACCESS，运行下面的代码：

-- 使用EXTERNAL_ACCESS 权限集需要把数据库选项TRUSTWORTHY 设置为ON

ALTER DATABASE CLRUtilities SET TRUSTWORTHY ON;

GO

-- 修改程序集PERMISSION_SET = EXTERNAL_ACCESS

ALTER ASSEMBLY CLRUtilities

WITH PERMISSION_SET = EXTERNAL_ACCESS;

这时你就可以运行usp_GetEnvInfo存储过程了。UNSAFE程序集拥有任意权限并会危及SQL Server的健壮性和系统的安全。EXTERNAL_ACCESS程序集拥有和SAFE程序集一样的可靠性和稳定性，但在安全方面它们类似于UNSAFE程序集。

一个更为安全的解决方案是用强名称密钥文件（strong-named key file）为程序集签名或使用证书为授权码（Authenticode）签名。强名称（或证书）是SQL Server内部创建的非对称秘钥（asymmetric key）（或证书），对应一个拥有EXTERNAL ACCESS ASSEMBLY权限（对于external access程序集） 或UNSAFE ASSEMBLY权限（对于unsafe程序集）的登录。例如，假设CLRUtilities程序集的代码需要用EXTERNAL_ ACCESS权限集运行。你可以在Visual Studio中工程｜属性对话框上的签名选项卡中，使用强名称密钥文件为程序集签名。然后运行下面的代码从可执行的 .dll文件中创建非对称密钥，并创建一个拥有EXTERNAL_ACCESS ASSEMBLY权限的登录。

-- 从已签名的程序创建非对称密钥

-- 注意:必须使用强名称密钥文件为程序集签名

USE master

GO

CREATE ASYMMETRIC KEY CLRUtilitiesKey

  FROM EXECUTABLE FILE =

    'C:\CLRUtilities\CLRUtilities\bin\Debug\CLRUtilities.dll'

-- 创建登录并授予其external access权限

CREATE LOGIN CLRUtilitiesLogin FROM ASYMMETRIC KEY CLRUtilitiesKey

GRANT EXTERNAL ACCESS ASSEMBLY TO CLRUtilitiesLogin

GO

关于程序集安全的更详细的信息，请参考联机丛书：http://msdn2.microsoft.com/en-us/ library/ms345106.aspx。

代码清单7-10显示了使用C# 代码编写的usp_GetEnvInfo存储过程的定义。

代码清单7-10  CLR usp_GetEnvInfo 存储过程，C#版

   // 以表格形式返回环境信息的存储过程

    [SqlProcedure]

    public static void usp_GetEnvInfo()

    {

        // 创建一个record- 表示行的对象

        // 包括SQL表的元数据

        SqlDataRecord record = new SqlDataRecord(

            new SqlMetaData("EnvProperty", SqlDbType.NVarChar, 20),

            new SqlMetaData("Value", SqlDbType.NVarChar, 256));

        // 标记要发送回客户端的结果集的开始

        // record参数用于构造结果集的元数据

        //

        SqlContext.Pipe.SendResultsStart(record);

        // 填充一些行并通过管道发送它们

        record.SetSqlString(0, @"Machine Name");

        record.SetSqlString(1, Environment.MachineName);

        SqlContext.Pipe.SendResultsRow(record);

        record.SetSqlString(0, @"Processors");

        record.SetSqlString(1, Environment.ProcessorCount.ToString());

        SqlContext.Pipe.SendResultsRow(record);

        record.SetSqlString(0, @"OS Version");

        record.SetSqlString(1, Environment.OSVersion.ToString());

        SqlContext.Pipe.SendResultsRow(record);

        record.SetSqlString(0, @"CLR Version");

        record.SetSqlString(1, Environment.Version.ToString());

        SqlContext.Pipe.SendResultsRow(record);

        //标记结果集的结尾

        SqlContext.Pipe.SendResultsEnd();

    }

在这个存储过程，你可以看到在SQL Server CLR程序中使用了一些特殊的ADO.NET扩展。它们定义在 .NET2.0的Microsoft.SqlServer.Server命名空间下。

当你从SQL Server调用存储过程时，已经建立了连接。不必再打开一个新的连接；你需要从服务器上运行的代码中访问调用者的上下文。调用者的上下文是由SqlContext对象提供的。在使用SqlContext对象之前，应该先使用IsAvailable属性测试它是否可用。

该存储过程从操作系统中获取一些运行环境的数据。这些数据可以通过Environment对象的属性获取，该对象位于System命名空间。但是你获取的数据是文本格式的。在这个CLR存储过程中，你会看到如何生成任意格式的行集。程序的代码把数据保存在SqlDataRecord对象中，它表示的一个数据行，并通过SqlMetaData对象为该行定义架构。

T-SQL存储过程使用SELECT语句把结果发送到调用者的“管道（pipe）”。这是把结果发送给调用者的最有效的方式。在SQL Server中运行的CLR程序也公开了同样的方法。SqlPipe对象的send方法可以把结果发送给连接的管道。使用SqlContext对象的Pipe属性可以得到实例化的SqlPipe对象。

代码清单7-11显示了使用Visual Basic代码编写的usp_GetEnvInfo存储过程的定义。

代码清单7-11  CLR usp_GetEnvInfo存储过程，Visual Basic版

   '以表格形式返回环境信息的存储过程

    <SqlProcedure()> _

    Public Shared Sub usp_GetEnvInfo()

        '创建一个record- 表示行的对象

        '包括SQL表的元数据

        Dim record As New SqlDataRecord(_

            New SqlMetaData("EnvProperty", SqlDbType.NVarChar, 20), _

            New SqlMetaData("Value", SqlDbType.NVarChar, 256))

        '标记要发送回客户端的结果集的开始

        ' record参数用于构造结果集的元数据

        '

        SqlContext.Pipe.SendResultsStart(record)

        ''填充一些行并通过管道发送它们

        record.SetSqlString(0, "Machine Name")

        record.SetSqlString(1, Environment.MachineName)

        SqlContext.Pipe.SendResultsRow(record)

        record.SetSqlString(0, "Processors")

        record.SetSqlString(1, Environment.ProcessorCount.ToString())

        SqlContext.Pipe.SendResultsRow(record)

        record.SetSqlString(0, "OS Version")

        record.SetSqlString(1, Environment.OSVersion.ToString())

        SqlContext.Pipe.SendResultsRow(record)

        record.SetSqlString(0, "CLR Version")

        record.SetSqlString(1, Environment.Version.ToString())

        SqlContext.Pipe.SendResultsRow(record)

        '标记结果集的结尾

        SqlContext.Pipe.SendResultsEnd()

    End Sub

运行下面的代码在CLRUtilities数据库中注册C# 版的usp_GetEnvInfo存储过程。

USE CLRUtilities;

GO

IF OBJECT_ID('dbo.usp_GetEnvInfo') IS NOT NULL

  DROP PROC usp_GetEnvInfo;

GO

CREATE PROCEDURE dbo.usp_GetEnvInfo

AS EXTERNAL NAME CLRUtilities.CLRUtilities.usp_GetEnvInfo;

如果你是用Visual Basic开发的，用下面的代码注册该存储过程。

CREATE PROCEDURE dbo.usp_GetEnvInfo

AS EXTERNAL NAME

  CLRUtilities.[CLRUtilities.CLRUtilities].usp_GetEnvInfo;

运行下面的代码测试usp_GetEnvInfo存储过程，产生的输出如表7-15所示。

EXEC dbo.usp_GetEnvInfo;

表7-15  usp_GetEnvInfo存储过程的输出

EnvProperty	Value
Machine Name	DOJO
Processors	1
OS Version	Microsoft Windows NT 5.1.2600 Service Pack 2
CLR Version	2.0.50727.42

CLR存储过程的第二个示例是usp_GetAssemblyInfo存储过程，它返回与输入程序集相关的信息。

代码清单7-12显示了C# 版的usp_GetAssemblyInfo存储过程的定义。

代码清单7-12  CLR usp_GetAssemblyInfo存储过程，C＃版

   // 返回程序集信息的存储过程

    // 使用反射

    [SqlProcedure]

    public static void usp_GetAssemblyInfo(SqlString asmName)

    {

        // 获取程序集的clr 名称

        String clrName = null;

        // 获取上下文

        using (SqlConnection connection =

                 new SqlConnection("Context connection = true"))

        {

            connection.Open();

            using (SqlCommand command = new SqlCommand())

            {

                //得到程序集并加载它

                command.Connection = connection;

                command.CommandText =

                  "SELECT clr_name FROM sys.assemblies WHERE name = @asmName";

                command.Parameters.Add("@asmName", SqlDbType.NVarChar);

                command.Parameters[0].Value = asmName;

                clrName = (String)command.ExecuteScalar();

                if (clrName == null)

                {

                    throw new ArgumentException("Invalid assembly name!");

                }

                Assembly myAsm = Assembly.Load(clrName);

                // 创建一个 record – 表示行的对象

                // 包含SQL表的元数据

                SqlDataRecord record = new SqlDataRecord(

                    new SqlMetaData("Type", SqlDbType.NVarChar, 50),

                    new SqlMetaData("Name", SqlDbType.NVarChar, 256));                 //标记要发送回客户端的结果集的开始

            　  //record参数用于构造结果集的元数据

                SqlContext.Pipe.SendResultsStart(record);

                // 得到程序集中的所有类型

                Type[] typesArr = myAsm.GetTypes();

                foreach (Type t in typesArr)

                {

                    // SQL数据库中的类型应该是一个类或一个结构

                    if (t.IsClass == true)

                    {

                        record.SetSqlString(0, @"Class");

                    }

                    else

                    {

                        record.SetSqlString(0, @"Structure");

                    }

                    record.SetSqlString(1, t.FullName);

                    SqlContext.Pipe.SendResultsRow(record);

                    // 找出所有公共的静态方法

                    MethodInfo[] miArr = t.GetMethods();

                    foreach (MethodInfo mi in miArr)

                    {

                        if (mi.IsPublic && mi.IsStatic)

                        {

                            record.SetSqlString(0, @" Method");

                            record.SetSqlString(1, mi.Name);

                            SqlContext.Pipe.SendResultsRow(record);

                        }

                    }

                }

                // 标记结果集的结尾

                SqlContext.Pipe.SendResultsEnd();

            }

        }

    }

DBA可能会遇到一个问题，即如何精确的找出一个 .NET程序集中的哪些部分被加载到了数据库。幸运的是，这个问题很容易解决。所有的 .NET程序集都包含元数据，用于描述在程序集中定义的所有类型（类和结构），包括所有public方法和属性。.NET中的System.Reflection命名空间中的一些类和接口提供已加载类型的管理视图（managed view）。

要了解存储在文件系统中的 .NET程序集的详细描述，你可以使用Reflector for .NET这个非常出色的工具，它由Lutz Roeder开发。这个工具可以从他的网站免费下载：http://www.aisto.com/roeder/dotnet/，它在 .NET开发人员中非常受欢迎。Miles Trochesset在它的博客（http://blogs.msdn.com/sqlclr/archive/2005/11/21/495438.aspx）中提供了一个SQL Server CLR DDL触发器，这个触发器在执行CREATE ASSEMBLY语句时触发。它会自动地注

册程序集中的所有CLR对象，包括UDT、UDA、UDF、SP和触发器。我想它在数据库开发人员中一定会非常受欢迎。我以这两个工具为切入点创建简化版的SQL Server CLR存储过程。我认为DBA可能更愿意从存储过程中读取程序集元数据，而不是利用外部工具，就像Lutz Roeder的Reflector for .NET，而且DBA可能只是先读取元数据，不会像Miles Trochesset的触发器一样，马上注册程序集中的所有CLR对象。

usp_GetAssemblyInfo存储过程必须从sys.assemblies目录视图中加载一个程序集。为此，它必须执行SqlCommand。SqlCommand需要一个连接。在usp_GetEnvInfo存储过程的代码中你已经看到了SqlContext类的用法；现在你需要一个显示的SqlConnection对象。通过使用新的连接字符串选项“Context connection = true”，你可以得到调用者连接的上下文。

与usp_GetEnvInfo存储过程一样，你希望得到表格形式的结果。还是使用SqlDataRecord和SqlMetaData对象构造返回的记录。SqlPipe对象提供了最好的性能把行返回给调用方。

读取程序集的元数据之前，你必须加载它。之后的工作就非常容易了。已加载程序集的GetTypes方法用于获取该程序集中定义的所有类型的集合。代码把该集合返回到一个数组。然后遍历该数组，使用GetMethods方法获取每个类型的所有的public方法，并保存到MethodInfo对象的数组中。这个存储过程只获取类型和方法名称。其实利用反射还可以获取其他的元数据信息。例如，输入参数的名称和类型。代码清单7-13显示了使用Visual Basic代码编写的usp_GetAssemblyInfo存储过程。

代码清单7-13  CLR usp_GetAssemblyInfo存储过程，Visual Basic版

    '返回程序集信息的存储过程

    '使用反射

    <SqlProcedure()> _

    Public Shared Sub usp_GetAssemblyInfo(ByVal asmName As SqlString)

        '获取程序集的clr 名称

        Dim clrName As String = Nothing

        '获取上下文

        Using connection As New SqlConnection("Context connection = true")

            connection.Open()

            Using command As New SqlCommand

                '获得程序集并加载它

                command.Connection = connection

                command.CommandText = _

                  "SELECT clr_name FROM sys.assemblies WHERE name = @asmName"

                command.Parameters.Add("@asmName", SqlDbType.NVarChar)

                command.Parameters(0).Value = asmName

                clrName = CStr(command.ExecuteScalar())

                If (clrName = Nothing) Then

                    Throw New ArgumentException("Invalid assembly name!")

                End If

                Dim myAsm As Assembly = Assembly.Load(clrName)

                '创建一个 record – 表示行的对象

                '包含SQL表的元数据                 Dim record As New SqlDataRecord( _

                    New SqlMetaData("Type", SqlDbType.NVarChar, 50), _

                    New SqlMetaData("Name", SqlDbType.NVarChar, 256))

                '标记要发送回客户端的结果集的开始

                '

                ' record参数用于构造结果集的元数据

                '

                SqlContext.Pipe.SendResultsStart(record)

                '得到程序集中的所有类型

                Dim typesArr() As Type = myAsm.GetTypes()

                For Each t As Type In typesArr

                    ' SQL数据库中的类型应该是一个类或一个结构

                    If (t.IsClass = True) Then

                        record.SetSqlString(0, "Class")

                    Else

                        record.SetSqlString(0, "Structure")

                    End If

                    record.SetSqlString(1, t.FullName)

                    SqlContext.Pipe.SendResultsRow(record)

                    '找出所有公共的静态方法

                    Dim miArr() As MethodInfo = t.GetMethods

                    For Each mi As MethodInfo In miArr

                        If (mi.IsPublic And mi.IsStatic) Then

                            record.SetSqlString(0, " Method")

                            record.SetSqlString(1, mi.Name)

                            SqlContext.Pipe.SendResultsRow(record)

                        End If

                    Next

                Next

                '标识结果集的结尾

                SqlContext.Pipe.SendResultsEnd()

            End Using

        End Using

    End Sub

运行下面的代码在CLRUtilities数据库中注册C# 版的usp_GetAssemblyInfo存储过程。

IF OBJECT_ID('dbo.usp_GetAssemblyInfo') IS NOT NULL

  DROP PROC usp_GetAssemblyInfo;

GO

CREATE PROCEDURE usp_GetAssemblyInfo

  @asmName AS sysname

AS EXTERNAL NAME CLRUtilities.CLRUtilities.usp_GetAssemblyInfo;

如果你使用Visual Basic开发该存储过程, 使用下面的代码注册:

CREATE PROCEDURE usp_GetAssemblyInfo

  @asmName AS sysname

AS EXTERNAL NAME

  CLRUtilities.[CLRUtilities.CLRUtilities].

usp_GetAssemblyInfo;

运行下面的代码测试usp_GetAssemblyInfo存储过程，用CLRUtilities程序集作为输入。

EXEC usp_GetAssemblyInfo N'CLRUtilities';

你将得到的输出如表7-16所示，其中包含了程序集名称和其中定义的所有方法（程序）的名称。除了trg_GenericDMLAudit这个CLR触发器，你应该能识别出其他的程序名称，我将在下一章介绍trg_GenericDMLAudit触发器。

表7-16  usp_GetAssemblyInfo存储过程的输出

Type	Name
Class	CLRUtilities
Method	fn_RegExMatch
Method	fn_SQLSigCLR
Method	fn_ImpCast
Method	fn_ExpCast
Method	fn_SplitCLR
Method	ArrSplitFillRow
Method	usp_GetEnvInfo
Method	usp_GetAssemblyInfo
Method	trg_GenericDMLAudit