SQL CLR (SQL Common Language Runtime) 是自 SQL Server 2005 才出现的新功能,它将.NET Framework中的CLR服务注入到 SQL Server 中,让 SQL Server 的部份资料库物件可以使用 .NET Framework 的程式语言开发(目前只支援VB.NETC#),包括预存程序使用者自订函数触发程序使用者自订型别以及使用者自订汇总函数等功能[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 被雜湊後的值。

参考资料

编辑
  1. ^ SQL Server CLR 整合簡介. [2008-10-15]. (原始内容存档于2009-06-27). 
  2. ^ Database Engine .NET Programming - Enabling CLR Integration. [2008-10-15]. (原始内容存档于2009-06-27). 
  3. ^ Database Engine .NET Programming - Creating an Assembly. [2008-10-15]. (原始内容存档于2009-06-27).