概述
CLR的集成，使得.NET代码可在SQL
Server服务器进程中执行。开发人员通过C#和SQLCLR可轻松创建存储过程、用户定义函数、触发器和用户定义类型等功能，改变了以前只能通过T-
SQL语言来实现这些功能的局面。作为SQLCLR的典型应用，本文将通过C#编写Base64编码解码函数、正则表达式函数、字符串分割函数以及
平方平均数函数
来演示如何为SQL Server编写
标量值函数、表值函数和聚合函数。
启用SQLCLR
|
sp_configure
'clr enabled' ; name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
clr enabled 0 1 0 0
|
|
sp_configure
'clr enabled' , 1; GO
RECONFIGURE
; GO
sp_configure
'clr enabled' ; name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
clr enabled 0 1 1 1
|
提供了一个项目模板来建立用于数据库开发的项目，在新建项目中选择Visual
C#，然后选择数据库项目，在对话框中设置项目名称为SqlServer.SqlClr.Functions，为项目添加一个C#类文件并命名类名称为
UserDefinedFunctions，并添加一个静态公共的HelloSqlClr函数，补充代码结果如下所示。
|
using
System; using
System.Data; using
System.Data.SqlTypes; using
Microsoft.SqlServer.Server; public
class UserDefinedFunctions {
[Microsoft.SqlServer.Server.SqlFunction
(Name = "Clr_HelloSqlClr" )] public
static SqlString HelloSqlClr(SqlString input) {
return
input; }
}
|
类引用了System.Data.SqlTypes命名空间。
System.Data.SqlTypes 命名空间包含了SQL Server
中本地数据类型对应的类型，比如上面的SqlString类型对应于SQL
Server的char、nchar、text、ntext、nvarchar、varchar数据类型。这些类提供一种比.NET
Framework公共语言运行库(CLR)提供的数据类型更快更安全的替代方案。使用此命名空间中的类有助于防止类型转换错误时出现精度损失的情况。
类还引用了Microsoft.SqlServer.Server命名空间，该命名空间包
含将 Microsoft .NET Framework公共语言运行库(CLR)集成到Microsoft SQL Server和SQL
Server 数据库引擎进程执行环境时所要用到的类、接口和枚举。上面的代码我们为
HelloSqlClr
指定了SqlFunction特性，并设置Name属性，这使得通过Visual Studio轻松实现托管用户定义函数在SQL Server中的部署。
SqlServer.SqlClr.Functions.dll
程序集。
|
csc /target:library /out:SqlServer.SqlClr.Functions.dll UserDefinedFunctions.cs
|
|
CREATE
ASSEMBLY [SqlServer.SqlClr.Functions] FROM
'D:/SqlServer SqlClr Solution/SqlServer.SqlClr.Functions/SqlServer.SqlClr.Functions.dll' WITH
PERMISSION_SET = SAFE |
|
SELECT
* FROM sys . assemblies |
HelloSqlClr
函数。
|
CREATE
FUNCTION [dbo] . [Clr_HelloSqlClr] ( @input [nvarchar] ( 128)) RETURNS
[nvarchar] ( 128) WITH EXECUTE AS CALLER AS
EXTERNAL
NAME [SqlServer.SqlClr.Functions] . [UserDefinedFunctions] . [HelloSqlClr] |
|
SELECT
dbo . Clr_HelloSqlClr ( 'Hello sqlclr' ); ---------------------------
Hello sqlclr
|
Base64
编码解码函数和正则表达式函数属于标量值函数，字符串分割函数属于表值函数，而平方平均数函数属于聚合函数。
Base64
编码解码函数
虽然能够通过FOR XML语法间接实现对数据的Base64编码，但到目前为止实际上并没有提供可直接调用Base64编码解码的函数，这不免让人感到意外，不过通过SQLCLR可轻松解决这一问题。
编码函数。
|
[Microsoft.SqlServer.Server.SqlFunction
(Name = "Clr_Base64Encode" )] public
static SqlString Base64Encode(SqlString input) {
if
(input.IsNull) {
return
new SqlString (null ); }
byte
[] array = System.Text.Encoding .UTF8.GetBytes(input.Value); string
result = System.Convert .ToBase64String(array); return
new SqlString (result); }
|
解码函数。
|
[Microsoft.SqlServer.Server.SqlFunction
(Name = "Clr_Base64Decode" )] public
static SqlString Base64Decode(SqlString input) {
if
(input.IsNull) {
return
new SqlString (null ); }
byte
[] array = System.Convert .FromBase64String(input.Value); string
result = Encoding .UTF8.GetString(array); return
new SqlString (result); }
|
SqlServer.SqlClr.Functions.dll到SQL Server，在SQL
Server对象资源管理器对应数据库的标量值函数目录下可以发现增加了Clr_Base64Encode和Clr_Base64Decode函数，通过
右键修改菜单查看Clr_Base64Encode函数对应的T-SQL代码。
|
ALTER
FUNCTION [dbo] . [Clr_Base64Encode] ( @input [nvarchar] ( 4000)) RETURNS
[nvarchar] ( 4000) WITH EXECUTE AS CALLER AS
EXTERNAL
NAME [SqlServer.SqlClr.Functions] . [UserDefinedFunctions] . [Base64Encode] |
|
SELECT
dbo . Clr_Base64Encode ( 'StarCraft|WarCraft|Diablo' ) AS ITEM ; SELECT
dbo . Clr_Base64Decode ( 'U3RhckNyYWZ0fFdhckNyYWZ0fERpYWJsbw==' ) AS ITEM ; ITEM
-------------------------------------------------------
U3RhckNyYWZ0fFdhckNyYWZ0fERpYWJsbw==
ITEM
-------------------------------------------------------
StarCraft|WarCraft|Diablo
|
正则表达式函数
Server还没有提供对正则表达式的支持。虽然Oracle早期版本中也缺乏对SQL正则表达式支持，不过在Oracle
10g中内建了符合POSIX
标准的正则表达式，增加了REGEXP_LIKE、REGEXP_INSTR、REGEXP_SUBSTR和EGEXP_REPLACE四个新函数。本节
我们将通过C#和SQLCLR来实现类似的正则表达式函数。
System.Text.RegularExpressions命名空间中的类型，核心类为Regex，那么我们继续来完善
UserDefinedFunctions类，添加正则表达式匹配函数、正则表达式匹配索引函数、正则表达式匹配项函数和正则表达式替换函数这四个函数。
|
[Microsoft.SqlServer.Server.SqlFunction
(Name = "Clr_RegexLike" )] public
static SqlBoolean RegexLike(SqlString input, SqlString pattern, SqlInt32 options) {
if
(input.IsNull || pattern.IsNull) {
return
new SqlBoolean (false ); }
bool
result = Regex .IsMatch(input.Value, pattern.Value, (RegexOptions )options.Value); return
new SqlBoolean (result); }
|
|
[Microsoft.SqlServer.Server.SqlFunction
(Name = "Clr_RegexMatchIndex" )] public
static SqlInt32 RegexMatchIndex(SqlString input, SqlString pattern, SqlInt32 options) {
if
(input.IsNull || pattern.IsNull) {
return
new SqlInt32 (-1); }
Match
match = Regex .Match(input.Value, pattern.Value, (RegexOptions )options.Value); if
(match.Success) {
return
new SqlInt32 (match.Captures[0].Index); }
return
new SqlInt32 (-1); }
|
|
[Microsoft.SqlServer.Server.SqlFunction
(Name = "Clr_RegexMatchValue" )] public
static SqlString RegexMatchValue(SqlString input, SqlString pattern, SqlInt32 options) {
if
(input.IsNull || pattern.IsNull) {
return
SqlString .Null; }
Match
match = Regex .Match(input.Value, pattern.Value, (RegexOptions )options.Value); if
(match.Success) {
return
new SqlString (match.Captures[0].Value); }
return
SqlString .Null; }
|
|
[Microsoft.SqlServer.Server.SqlFunction
(Name = "Clr_RegexReplace" )] public
static SqlString RegexReplace(SqlString input, SqlString pattern, SqlString replacement, SqlInt32 options) {
if
(input.IsNull || pattern.IsNull || replacement.IsNull) {
return
input; }
string
s = Regex .Replace(input.Value, pattern.Value, replacement.Value, (RegexOptions )options.Value); return
new SqlString (s); }
|
参数功能对应.NET中的RegexOptions枚举，RegexOptions带有FlagAttribute特性，也就是说多个不同的枚举值可以组
合在一起，因此如果要正确设置Options，需要对RegexOptions进行一些了解，下面是通过NUnit确认的RegexOptions枚举项
对应的值，并对枚举组合进行单元测试。
|
[Test
] public
void RegexOptionsTest() {
Assert
.AreEqual((int )RegexOptions .None,0); Assert
.AreEqual((int )RegexOptions .IgnoreCase,1); Assert
.AreEqual((int )RegexOptions .Multiline,2); Assert
.AreEqual((int )RegexOptions .ExplicitCapture,4); Assert
.AreEqual((int )RegexOptions .Compiled,8); Assert
.AreEqual((int )RegexOptions .Singleline,16); Assert
.AreEqual((int )RegexOptions .IgnorePatternWhitespace,32); Assert
.AreEqual((int )RegexOptions .RightToLeft,64); Assert
.AreEqual((int )RegexOptions .ECMAScript, 256); Assert
.AreEqual((int )RegexOptions .CultureInvariant,512); Assert
.AreEqual((int )(RegexOptions .IgnoreCase | RegexOptions .Multiline), 3); }
|
署，在SQL
Server对象资源管理器对应数据库的标量值函数目录下可以发现增加了Clr_RegexLike、Clr_RegexMatchIndex、
Clr_RegexMatchValue和Clr_RegexReplace四个函数。
|
CREATE
TABLE [dbo] . [GameInfo] ( [Id]
[int] NOT NULL, [Name]
[nvarchar] ( 32) NOT NULL )
ON [PRIMARY] GO
INSERT
INTO [dbo] . [GameInfo] ( Id , Name ) VALUES ( 1, 'StarCraft' ); INSERT
INTO [dbo] . [GameInfo] ( Id , Name ) VALUES ( 2, 'WarCraft' ); INSERT
INTO [dbo] . [GameInfo] ( Id , Name ) VALUES ( 1, 'Diablo' ); |
|
SELECT
* FROM dbo . GameInfo WHERE dbo . Clr_RegexLike ( Name , 'Craft$' , 0) = 1; Id Name
----------- --------------------------------
1 StarCraft
2 WarCraft
SELECT
Name , dbo . Clr_RegexMatchIndex ( Name , 'Craft$' , 0) AS MIndex FROM dbo . GameInfo ; Name MIndex
-------------------------------- -----------
StarCraft 4
WarCraft 3
Diablo -1
SELECT
Name , dbo . Clr_RegexMatchValue ( Name , 'craft$' , 1) AS MValue FROM dbo . GameInfo ; Name MValue
-------------------------------- ------------------------------
StarCraft Craft
WarCraft Craft
Diablo NULL
SELECT
Name , dbo . Clr_RegexReplace ( Name , '^StarCraft$' , 'StarCraftII' , 0) AS Name2 FROM dbo . GameInfo ; Name Name2
-------------------------------- -------------------------------
StarCraft StarCraftII
WarCraft WarCraft
Diablo Diablo
|
字符串分割函数
String.Split方法可以轻松实现按特定字符组或字符串组进行的分割，并返回分割后的子字符串数组，当然Regex.Split方法提供了更为强
大的分割功能，支持由正则表达式匹配项定义的分割字符串将输入的字符串拆分为一个子字符串数组。相对于.NET提供对字符串处理的强大支持，SQL
Server就显得相对乏力，下面就来实现SQLCLR版本的Split函数。
Server中体现为多条记录，因而不同于前面的Base64编码解码和正则表达式等标量值函数，Split函数属于表值函数（TVF）。要实现表值函数
除了为SqlFuctionAttribute特性设置Name属性外，还需要指定FillRowMethodName和TableDefinition
两个属性，FillRowMethodName属性指定行填充方法的名称，而TableDefinition属性定义返回的记录集的表结构，下面显示了完
整实现代码。
|
[Microsoft.SqlServer.Server.SqlFunction
(Name = "Clr_Split" , FillRowMethodName = "SplitFillRow" , TableDefinition = "item nvarchar(256)" )] public
static IEnumerable Split(SqlString input, SqlString separators) {
string
[] array; if
(input.IsNull) {
array = new
string [] { null }; }
else
if (separators.IsNull) {
array = new
string [] { input.Value }; }
else
{
string
[] separatorsArray = separators.Value.Split(new char [] { ',' }, StringSplitOptions .RemoveEmptyEntries); array = input.Value.Split(separatorsArray, StringSplitOptions
.None); }
return
array; }
private
static void SplitFillRow(Object obj, ref SqlString item) {
if
(obj != null ) {
item = (string
)obj; }
}
|
|
ALTER
FUNCTION [dbo] . [Clr_Split] ( @input [nvarchar] ( 4000), @separators [nvarchar] ( 4000)) RETURNS
TABLE ( [item]
[nvarchar] ( 256) NULL )
WITH EXECUTE AS CALLER AS
EXTERNAL
NAME [SqlServer.SqlClr.Functions] . [UserDefinedFunctions] . [Split] |
|
SELECT
* FROM dbo . Clr_Split ( 'StarCraft|WarCraft|Diablo' , '|' ); SELECT
* FROM dbo . Clr_Split ( 'StarCraft|WarCraft//Diablo' , '|,//' ); item
-----------------------------
StarCraft
WarCraft
Diablo
|
|
[Microsoft.SqlServer.Server.SqlFunction
(Name = "Clr_SplitWithOrder" , FillRowMethodName = "SplitWithOrderFillRow" , TableDefinition = "orderId int, item nvarchar(4000)" )] public
static IEnumerable SplitWithOrder(SqlString input, SqlString separators) {
Dictionary
<int , string > dictionary = new Dictionary <int , string >(); if
(input.IsNull) {
dictionary.Add(1, null
); }
else
if (separators.IsNull) {
dictionary.Add(1, input.Value);
}
else
{
string
[] separatorsArray = separators.Value.Split(new char [] { ',' }, StringSplitOptions .RemoveEmptyEntries); string
[] array = input.Value.Split(separatorsArray, StringSplitOptions .None); for
(int i = 0; i < array.Length; i++) {
dictionary.Add(i + 1, array[i]);
}
}
return
dictionary; }
private
static void SplitWithOrderFillRow(Object obj, ref SqlInt32 orderid, ref SqlString item) {
if
(obj != null ) {
KeyValuePair
<int , string > kvp = (KeyValuePair <int , string >)obj; orderid = kvp.Key;
item = kvp.Value;
}
}
|
|
SELECT
* FROM dbo . Clr_SplitWithOrder ( 'StarCraft|WarCraft//Diablo' , '|,//' ) ORDER BY orderId DESC ; orderId item
----------- ----------------------------------------
3 Diablo
2 WarCraft
1 StarCraft
|
平方平均数函数
2005之前，数据库引擎只支持内置聚合函数，例如常见的SUM、MAX、AVG和COUNT等函数，这些聚合函数对一组输入标量值执行操作，并且从该组
值生成单个聚合值。在SQL Server 2005版本推出后，SQL Server同.NET
CLR集成，使得开发人员能够通过.NET托管代码创建自定义聚合函数，并且使这些函数可应用于T-SQL编程。
Visual
Studio中通过解决方案管理器右键点击SqlServer.SqlClr.Functions项目打开“添加”子菜单选择“聚合”菜单项，项目中会新
增加包含聚合函数模板的Aggregate1.cs文件，接下去只需要在模板里面添加代码实现功能逻辑即可。
|
using
System; using
System.Data; using
System.Data.SqlClient; using
System.Data.SqlTypes; using
Microsoft.SqlServer.Server; [Serializable
] [Microsoft.SqlServer.Server.SqlUserDefinedAggregate
(Format .Native)] public
struct Aggregate1 {
public
void Init() {
}
public
void Accumulate(SqlString Value) {
}
public
void Merge(Aggregate1 Group) {
}
public
SqlString Terminate() {
return
new SqlString ("" ); }
private
int var1; }
|
定了SqlUserDefinedAggregate特性来指示结构类型应如何注册为用户定义的聚合。SqlUserDefinedAggregate的
Format属性通过Microsoft.SqlServer.Server.Format枚举来指定聚合的序列化格式，如果在聚合函数中只使用值类型成
员，那么可以设置Format属性为Format.Native，如果设置为Format.UserDefined，开发人员需要自己实现
Microsoft.SqlServer.Server.IBinarySerialize接口以支持序列化。另外
SqlUserDefinedAggregate特性类还包含
IsInvariantToNulls
和IsInvariantToDuplicates等其他属性，关于这些属性功能具体可以参考MSDN。
包含4个方法，这是查询处理器计算聚合所用的方法，如果编程人员对BizTalk组件开发比较熟悉，你会发现聚合函数编程类似于BizTalk中的自定义Functoid组件编程。MSDN文档对4个方法进行了具体解释。
方法。
|
public
void Init(); |
方法。
|
public
void Accumulate(input_type Value); |
查询处理器仅在为聚合类的指定实例调用Init方法之后才调用此方法。此方法的实现应更新实例的状态以反映正在传递的参数值的累计。input_type
参数是托管的 SQL Server 数据类型，该数据类型与CREATE AGGREGATE 语句中input_sqltype 所指定的本机SQL
Server数据类型等效。
方法。
|
public
void Merge(Aggregate1 Group); |
方法。
|
public
return_type Terminate(); |
totalValue
变量用来存储输入数据的平方和，而count变量用来存储输入数据的个数，最后通过System.Math.Sqrt方法计算返回平方平均数，下面是实现代码。
|
[Serializable
] [Microsoft.SqlServer.Server.SqlUserDefinedAggregate
(Format .Native,IsInvariantToNulls = true , IsInvariantToDuplicates = false )] public
struct QuadraticMean {
private
double totalValue; private
int count; public
void Init() {
totalValue = 0.0;
count = 0;
}
public
void Accumulate(SqlDouble input) {
if
(input.IsNull) {
return
; }
totalValue += input.Value * input.Value;
count++;
}
public
void Merge(QuadraticMean Group) {
totalValue += Group.totalValue;
count = Group.count;
}
public
SqlDouble Terminate() {
double
result = Math .Sqrt(totalValue / count); return
new SqlDouble (result); }
}
|
|
CREATE
AGGREGATE [dbo] . [QuadraticMean] (
@input [float] ) RETURNS
[float] EXTERNAL
NAME [SqlServer.SqlClr.Functions] . [QuadraticMean] |
|
SELECT
avg ( Id ), dbo . QuadraticMean ( Id ) FROM dbo . GameInfo ; ----------- ----------------------
2 2.16024689946929
|
总结
同.NET CLR的紧密集成使得.NET开发人员能够快速实现SQL
Server扩展功能编程，一般使用SQLCLR来实现专门执行计算的操作功能，使用T-SQL来实现基于集合的操作功能。该文档全面讲解了如何通过C#
和SQLCLR为SQL Server实现标量值、表值和聚合3类用户定义函数，并演示用户定义函数在T-SQL中的实际应用。
Base64
编码解码函数、正则表达式函数、字符串分割函数和平方平均数函数均属于基础函数，可被方便应用到实际业务系统中。