SQL Server 中的 CLR 存储过程

引言

在 SQL Server 2005 及更高版本中，我们将能够创建 CLR 数据库对象（函数、存储过程、触发器等）。 有时，CLR 数据库对象比 T-SQL 速度更快。 主要是在需要实现 T-SQL 无法实现或需要更多资源时，可以使用 CLR 数据库对象。

优点

• CLR 存储过程是托管代码，因此它确保了类型安全、内存管理等。
• 它提供面向对象的（OP）功能，因此它支持封装、多态性和继承。
• CLR 存储过程可以用 C#、VB.NET 或 .NET Framework 支持的任何其他语言编写。
• 在执行复杂的逻辑时非常有用，例如密集的 字符串 操作或 字符串 操纵、加密、访问第三方库、访问系统资源和文件管理等。

缺点

• 在某些情况下，部署可能很困难。
• 它并非适用于所有情况，例如它不应用于执行最简单的查询。

创建 CLR 存储过程

使用 Microsoft Visual Studio，我们可以创建 SQL Server 数据库项目。 在此项目中，我们可以添加“SQL CLR C# 存储过程”。

SqlContext 和 SqlPipe 类

SqlContext 类表示调用者上下文的抽象，它提供对 SqlPipes、SqlTriggerContext 和 windowsIdentityobject 的访问。 SqlContext 类在内部是密封的，因此不能被继承。 可以使用此类对象获取管道对象、触发器上下文以及 Windows 标识（调用客户端的 Microsoft Windows 标识）。

SqlPipe 类对于将结果返回给调用者很有用。 Sqlcontext 类具有类型为 SqlPipe 类的管道属性。 此类有一个名为“Send”的方法；它有助于将结果直接设置给客户端或当前输出使用者。 此方法能够发送 SqlDataReader 对象、SqlDataRecord 对象和消息 字符串。

Hello World 示例

使用 SqlPipe.Send(string) 方法，我们可以将消息发送到客户端应用程序。 在这里，文本的长度限制为 8000 个字符。 如果文本超过 8000 个字符，它将被截断。

CLR 过程代码

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void HelloWorld ()
    {
        // Put your code here
        SqlContext.Pipe.Send("This is my CLR SP test");
    }
}

部署 CLR 存储过程的步骤

步骤 1

启用 CLR 集成

Use <database name="">
SP_CONFIGURE 'clr enabled',1 

第二步

如果我们的程序集依赖于其他第三方程序集，则将数据库设置为 TRUSTWORTHY。

ALTER DATABASE <<database name>> SET TRUSTWORTHY ON

步骤 3

匹配数据库中的 SID。

也许在主数据库中记录的数据库所有者 SID 与在数据库中记录的数据库所有者 SID 不同。 我们从 SQL Server 实例（例如服务器 A）分离或备份数据库，并将该数据库附加或还原到 SQL Server 的另一个实例（例如服务器 B）。 在这种情况下，我们收到错误“在主数据库中记录的数据库所有者 SID 与数据库所有者 SID 不同”。

以下脚本有助于我们更新正确的 SID。

Use <<database name>>

DECLARE @Command VARCHAR(MAX) = 'ALTER AUTHORIZATION ON DATABASE::<databasename> TO 
[<>]' 

SELECT @Command = REPLACE(REPLACE(@Command 
            , '<databasename>', SD.Name)
            , '<loginname>', SL.Name)
FROM master..sysdatabases SD 
JOIN master..syslogins SL ON  SD.SID = SL.SID
WHERE  SD.Name = DB_NAME()

EXEC(@Command) 

步骤 4

将 CLR 函数库及其依赖库文件复制到数据库服务器上的本地文件夹。

步骤 5

创建程序集

在以下查询“<<Local folder path >>”中，我们需要替换预编译程序集复制到的路径（在步骤 4 中）。

注意：在删除程序集之前，我们需要删除此程序集的所有引用。

-- Create Assembly
IF (EXISTS(select * from sys.assemblies where name = 'SQLCLR'))
BEGIN
	-- remove the reference 
	IF(EXISTS(select * from sys.objects where name = ' HelloWorld' and type='PC'))
		DROP PROCEDURE HelloWorld
	DROP ASSEMBLY SQLCLR
END

CREATE ASSEMBLY SQLCLR FROM '<<local folder>>\SQLCLR.dll' with PERMISSION_SET =UNSAFE;

步骤 6

创建 CLR 过程

IF(EXISTS(select * from sys.objects where name = ' HelloWorld' and type='PC'))
	DROP PROCEDURE HelloWorld
GO
CREATE PROCEDURE [dbo].[HelloWorld]
AS EXTERNAL NAME [SQLCLR].[StoredProcedures].[HelloWorld]

Hello World 示例的输出

表格结果示例

使用 SqlPipe 对象的 ExecuteAndSend 和 Send (SqlDataReader) 方法，我们可以将查询的结果直接发送到客户端。 在这里，数据将直接传输到网络缓冲区，而无需复制到托管内存。

CLR 过程代码

[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetAllEmployees()
{
    SqlConnection con = new SqlConnection("context connection=true");
    con.Open();
    SqlCommand cmd = new SqlCommand("select * from employee", con);
    SqlDataReader reader = cmd.ExecuteReader();
    SqlContext.Pipe.Send(reader);
}

按照步骤 4、5 和 6 注册程序集并创建 CLR 过程。

输出

将参数传递给 SQL CLR 过程

我们可以将参数传递给 CLR 过程，就像使用“OUTPUT”参数一样，我们可以从存储过程中获取结果。 我们必须使用“out”属性指定参数，以表示 CLR 存储过程中的 OUTPUT 参数。

CLR 过程代码

[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetValue(SqlInt32 value, out SqlString retValue)
{
    retValue = "You have entered : " + value;
}

输出

结论

CLR 过程可用于 T-SQL 无法实现的复杂场景。