SQL CLR (SQL Common Language Runtime) 是自 SQL Server 2005 才出现的新功能,它将.NET Framework中的CLR服务注入到 SQL Server 中,让 SQL Server 的部分数据库对象可以使用 .NET Framework 的编程语言开发(目前只支持VB.NET和C#),包括预存程序、用户自定义函数、触发程序、用户自定义类型以及用户自定义汇总函数等功能[1]。
架构
SQL CLR 是利用 .NET Framework 中的 Hosting(装载) 特性所实现的版本,这个功能让 SQL Server 中可以安装 .NET Framework 的组件,经由实现 ADO.NET 2.0 中所开放的 Microsoft.SqlServer.Server
名字空间中的.NET中介数据,来获得 SQL Server 数据库对象的能力:
- 预存程序:
SqlProcedureAttribute
。 - 用户函数:
SqlFunctionAttribute
。 - 触发程序:
SqlTriggerAttribute
。 - 用户自定义汇总:
SqlUserDefinedAggregate
。 - 用户自定义类型:
SqlUserDefinedType
。
SQL CLR 的组件在发展完成后,需要使用 CREATE ASSEMBLY
指令将组件安装到 SQL Server 中,然后使用相对应的 DDL 指令将组件中开放的函数引入数据库对象中,才能在 SQL 指令中调用。
CREATE ASSEMBLY SQLCLRTest
FROM 'C:\MyDBApp\SQLCLRTest.dll'
WITH PERMISSION_SET = SAFE
安全性
对于 SQL Server 来说,SQL CLR 组件是一种外部代码,所以在 SQL Server 默认的安装配置中,SQL CLR 是被封锁不可以使用的,若要使用它,必须要先将它打开[2]:
EXEC sp_configure 'clr enabled', 1;
而对于 SQL CLR 组件本身,SQL Server 也做了三重的防护[3]:
- SAFE:只有最少的权限可以运行,不可访问外部资源与外部代码。
- EXTERNAL_ACCESS:可以访问外部资源,像是文件、登录数据库、网络资源等。
- UNSAFE:可以无限制的访问外部资源,连 Win32 API 等都可以调用。
在大多数的情况来说,使用 SAFE 即可以正常使用组件,除非是要访问外部文件才使用 EXTERNAL_ACCESS,只有在特殊的情况下(例如要调用外部的商业逻辑组件)时,才会激活 UNSAFE 层次。
示例
下列示例为使用 C# 开发 SQL Server 用户函数的代码:
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString HashPasswordString(SqlString HashString)
{
SHA384Managed hashAlgorithm = new SHA384Managed();
byte[] srcPassword = System.Text.Encoding.ASCII.GetBytes(HashString.Value);
byte[] destPassword = null;
string hashedPasswordString = null;
destPassword = hashAlgorithm.ComputeHash(srcPassword);
hashedPasswordString = System.Text.Encoding.ASCII.GetString(destPassword);
hashAlgorithm = null;
return new SqlString(hashedPasswordString);
}
将组件安装到 SQL Server 的脚本为:
CREATE ASSEMBLY MyAssemblyLibrary FROM 'MySQLCLR.dll' WITH PERMISSION_SET = SAFE
将此函数引入 SQL Server 中的 DDL 脚本为:
CREATE FUNCTION dbo.HashPassword
(
@PasswordString VARCHAR(4000)
)
RETURNS VARCHAR(4000)
EXTERNAL NAME [MyAssemblyLibrary]。[MySQLCLR]。[HashPasswordString]
安装并引入后,即可如一般的 SQL 函数方式使用:
SELECT dbo.HashPassword('mypassword') -- 回傳 mypassword 被雜湊後的值。