SQL Server 的 CLR (Common Language Runtime) 集成允许开发人员在 SQL Server 环境中使用 .NET 语言（如 C# 或 VB.NET）来编写存储过程、触发器、用户定义类型、用户定义函数等。这为数据库编程提供了更大的灵活性和功能，特别是在处理复杂的逻辑或需要外部资源访问（如文件系统、网络请求等）时。
在本文中，我们将探讨如何在 SQL Server 中启用 CLR 集成，并提供一些使用 .NET 代码创建和部署数据库对象的示例。
启用 CLR 集成
默认情况下，SQL Server 的 CLR 集成是禁用的。为了使用 CLR 功能，我们需要启用它。以下是启用 CLR 集成的 T-SQL 命令：
sp_configure 'show advanced options', 1;RECONFIGURE;
sp_configure 'clr enabled', 1;RECONFIGURE;
创建 CLR 对象
要在 SQL Server 中使用 CLR 对象，您需要执行以下步骤：
使用 .NET 语言编写代码。
编译代码为 .NET 程序集。
在 SQL Server 中注册程序集。
创建引用程序集的 SQL 对象（如函数、存储过程等）。
示例 1：创建 CLR 用户定义函数
假设我们有一个需求，需要在 SQL Server 中实现一个正则表达式匹配的函数。由于 T-SQL 本身不支持正则表达式，我们可以使用 CLR 集成来实现这个功能。
步骤 1：使用 C# 编写代码
using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;using System.Text.RegularExpressions;
public partial class UserDefinedFunctions{ [SqlFunction] public static SqlBoolean RegexMatch(SqlString input, SqlString pattern) { if (input.IsNull || pattern.IsNull) return SqlBoolean.False;
return Regex.IsMatch(input.Value, pattern.Value); }}
步骤 2：编译代码为 .NET 程序集
使用 Visual Studio 或命令行工具 csc.exe 编译上述代码为 DLL 文件。
步骤 3：在 SQL Server 中注册程序集
CREATE ASSEMBLY RegexFunctionsFROM 'C:\Path\To\Your\Compiled\Assembly.dll'WITH PERMISSION_SET = SAFE;
取得dll的SH512
certutil -hashfile SqlLibex.dll SHA512
注意hash前加上0x
-- 用正确的格式调用 sp_add_trusted_assemblyEXEC sp_add_trusted_assembly 0xD8D0A23662B5BEBAC1217EC4DD1F7F2A39A21915C520C0645BE2081AC6E5729E022206CDF7C283721D1B2BE58E74DF63464C8355FBC0823FA486E9C404EC177F, N'aa';
CREATE ASSEMBLY RegexFunctionsFROM 'D:\BaiduSyncdisk\11Test\SqlLibex\bin\Debug\SqlLibex.dll' WITH permission_set = Safe;
步骤 4：创建引用程序集的 SQL 函数
CREATE FUNCTION RegexMatch(@input NVARCHAR(MAX), @pattern NVARCHAR(100))RETURNS BITAS EXTERNAL NAME RegexFunctions.[SqlLibex.UserDefinedFunctions].RegexMatch;
现在，您可以像调用任何其他 T-SQL 函数一样调用 RegexMatch 函数：
SELECT dbo.RegexMatch('Hello World', '^Hello') AS IsMatch;
示例 2：创建 CLR 存储过程
假设我们需要一个存储过程来读取文件系统中的文件内容并将其作为结果返回。
步骤 1：使用 C# 编写代码
using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;using System.IO;
public partial class StoredProcedures{ [SqlProcedure] public static void ReadFileContent(SqlString filePath) { if (filePath.IsNull) return;
SqlPipe pipe = SqlContext.Pipe;
try { string content = File.ReadAllText(filePath.Value); SqlDataRecord record = new SqlDataRecord(new SqlMetaData("FileContent", SqlDbType.NVarChar, -1)); pipe.SendResultsStart(record); record.SetString(0, content); pipe.SendResultsRow(record); pipe.SendResultsEnd(); } catch (Exception ex) { pipe.Send(ex.Message); } }}
步骤 2-4：编译程序集、注册程序集和创建存储过程
这些步骤与上面的示例类似。创建存储过程的 T-SQL 代码如下：
CREATE PROCEDURE ReadFileContent @filePath NVARCHAR(MAX)AS EXTERNAL NAME FileUtilities.StoredProcedures.ReadFileContent;
调用存储过程：
EXEC ReadFileContent 'C:\Path\To\Your\File.txt';
安全注意事项
使用 CLR 集成时，考虑到安全性，您应该始终使用最小的权限集合来注册程序集。在上面的示例中，我们使用了 SAFE 权限集。如果需要更高的权限（如 EXTERNAL_ACCESS 或 UNSAFE），您需要确保代码是安全的，并且了解赋予这些权限可能带来的风险。
结论
SQL Server 的 CLR 集成为数据库开发人员打开了一个新的世界，使他们能够利用 .NET 框架的强大功能来扩展数据库的功能。通过创建 CLR 对象，我们可以在数据库中实现更复杂的逻辑，执行任务，甚至与外部资源进行交互。然而，使用 CLR 集成时，开发人员应该注意代码的安全性和性能影响。通过谨慎地使用 CLR 功能并遵循最佳实践，您可以安全地为 SQL Server 添加强大的新功能。
该文章在 2024/2/7 23:08:02 编辑过
400 186 1886
