用C#和SQLCLR编写SQL Server用户定义函数
发布日期：2009-05-24
作者：郑佐
摘要： 文档阐述使用C#和SQLCLR为SQL Server编写用户定义函数，并演示用户定义函数在T-SQL中的应用。文档中实现的 Base64 编码解码函数和正则表达式函数属于标量值函数，字符串分割函数属于表值函数，而平方平均数函数属于聚合函数。
环境 要求：SQL Server 2005/2008，Visual Studio 2005/2008，C# 2.0。
本页内容
微软在推出SQL Server 2005后，实现了对.NET CLR的集成，使得.NET代码可在SQL Server服务器进程中执行。开发人员通过C#和SQLCLR可轻松创建存储过程、用户定义函数、触发器和用户定义类型等功能，改变了以前只能通过T-SQL语言来实现这些功能的局面。作为SQLCLR的典型应用，本文将通过C#编写Base64编码解码函数、正则表达式函数、字符串分割函数以及 平方平均数函数 来演示如何为SQL Server编写 标量值函数、表值函数和聚合函数。
在讲解具体函数之前，我们先来了解一下如何启用SQLCLR，并通过Visual Studio 2005/2008创建数据库项目，最后部署.NET程序集到SQL Server的过程。
默认情况下，SQL Server的SQLCLR是禁用的，要使用SQLCLR需要通过sp_configure系统存储过程设置服务器配置选项来启用，下面显示了默认情况下的设置。
|
sp_configure 'clr enabled' ;
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
clr enabled 0 1 0 0
|
下面用T-SQL来启用SQLCLR，并查看修改后的设置信息。
|
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 Studio 提供了一个项目模板来建立用于数据库开发的项目，在新建项目中选择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;
}
}
|
UserDefinedFunctions 类引用了System.Data.SqlTypes命名空间。System.Data.SqlTypes 命名空间包含了SQL Server 中本地数据类型对应的类型，比如上面的SqlString类型对应于SQL Server的char、nchar、text、ntext、nvarchar、varchar数据类型。这些类提供一种比.NET Framework公共语言运行库(CLR)提供的数据类型更快更安全的替代方案。使用此命名空间中的类有助于防止类型转换错误时出现精度损失的情况。
UserDefinedFunctions 类还引用了Microsoft.SqlServer.Server命名空间，该命名空间包含将 Microsoft .NET Framework公共语言运行库(CLR)集成到Microsoft SQL Server和SQL Server 数据库引擎进程执行环境时所要用到的类、接口和枚举。上面的代码我们为 HelloSqlClr 指定了SqlFunction特性，并设置Name属性，这使得通过Visual Studio轻松实现托管用户定义函数在SQL Server中的部署。
如果采用非自动化方式部署需要通过C#编译器编译源代码，并通过T-SQL语言进行程序集安装，最后进行函数注册等步骤。
下面通过CSC进行C#代码的编译，输出 SqlServer.SqlClr.Functions.dll 程序集。
|
csc /target:library /out:SqlServer.SqlClr.Functions.dll UserDefinedFunctions.cs
|
通过SQL Server Management Studio执行T-SQL加载SqlServer.SqlClr.Functions.dll程序集到SQL Server中。
|
CREATE ASSEMBLY [SqlServer.SqlClr.Functions]
FROM 'D:/SqlServer SqlClr Solution/SqlServer.SqlClr.Functions/SqlServer.SqlClr.Functions.dll'
WITH PERMISSION_SET = SAFE
|
当程序集安装到SQL Server中后，可以通过以下T-SQL来查看程序集。
|
SELECT * FROM sys . assemblies
|
下面的T-SQL注册程序集中包含的 HelloSqlClr 函数。
|
CREATE FUNCTION [dbo] . [Clr_HelloSqlClr] ( @input [nvarchar] ( 128))
RETURNS [nvarchar] ( 128) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SqlServer.SqlClr.Functions] . [UserDefinedFunctions] . [HelloSqlClr]
|
以上工作完成后，就可以通过T-SQL来调用用户定义函数了。
|
SELECT dbo . Clr_HelloSqlClr ( 'Hello sqlclr' );
---------------------------
Hello sqlclr
|
本节简单介绍了通过C#实现用户定义函数并部署应用的一般流程，接下去的章节来具体实现几个常用的功能函数，其中 Base64 编码解码函数和正则表达式函数属于标量值函数，字符串分割函数属于表值函数，而平方平均数函数属于聚合函数。
SQL Server 虽然能够通过FOR XML语法间接实现对数据的Base64编码，但到目前为止实际上并没有提供可直接调用Base64编码解码的函数，这不免让人感到意外，不过通过SQLCLR可轻松解决这一问题。
下面通过C#为UserDefinedFunctions类增加了Base64编码和解码函数。Convert类的ToBase64String和FromBase64String方法很好地发挥了作用，轻松实现了Base64编码和解码操作。
Base64 编码函数。
|
[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);
}
|
Base64 解码函数。
|
[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项目进行编译，并部署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]
|
使用T-SQL进行应用测试。
|
SELECT dbo . Clr_Base64Encode ( 'StarCraft|WarCraft|Diablo' ) AS ITEM ;
SELECT dbo . Clr_Base64Decode ( 'U3RhckNyYWZ0fFdhckNyYWZ0fERpYWJsbw==' ) AS ITEM ;
ITEM
-------------------------------------------------------
U3RhckNyYWZ0fFdhckNyYWZ0fERpYWJsbw==
ITEM
-------------------------------------------------------
StarCraft|WarCraft|Diablo
|
正则表达式为字符串处理提供了强大的功能，可惜的是目前SQL Server还没有提供对正则表达式的支持。虽然Oracle早期版本中也缺乏对SQL正则表达式支持，不过在Oracle 10g中内建了符合POSIX 标准的正则表达式，增加了REGEXP_LIKE、REGEXP_INSTR、REGEXP_SUBSTR和EGEXP_REPLACE四个新函数。本节我们将通过C#和SQLCLR来实现类似的正则表达式函数。
在.NET中使用正则表达式需要用到System.Text.RegularExpressions命名空间中的类型，核心类为Regex，那么我们继续来完善UserDefinedFunctions类，添加正则表达式匹配函数、正则表达式匹配索引函数、正则表达式匹配项函数和正则表达式替换函数这四个函数。
正则表达式匹配函数。RegexLike函数功能为对字符串进行模式匹配查询，如果匹配成功返回true，失败返回false。函数接收三个参数，分别对应输入字符串，匹配模式，和正则表达式选项，在函数内部通过Regex.IsMatch方法返回匹配结果。
|
[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);
}
|
正则表达式匹配索引函数。RegexMatchIndex函数功能为对字符串进行模式匹配查找，如果存在匹配，返回第一个匹配项的第一个字符的索引，如果不存在返回-1。
|
[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);
}
|
正则表达式匹配项函数。RegexMatchValue函数功能为对字符串进行模式匹配查找，如果存在匹配，返回第一个匹配项的内容，如果不存在，返回空字符串。
|
[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;
}
|
正则表达式替换函数。RegexReplace函数功能为对字符串进行模式查找替换，相比前面的函数，增加了一个替换内容参数，内部通过Regex.Replace方法对查找到的匹配使用新的字符串进行替换。
|
[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);
}
|
四个正则表达式函数都提供了一个SqlInt32类型的options参数，该参数功能对应.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);
}
|
再次对SqlServer.SqlClr.Functions项目进行编译和部署，在SQL Server对象资源管理器对应数据库的标量值函数目录下可以发现增加了Clr_RegexLike、Clr_RegexMatchIndex、Clr_RegexMatchValue和Clr_RegexReplace四个函数。
为测试正则表达式函数，准备一张GameInfo表并写入一些测试数据。
|
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' );
|
下面的T-SQL对四个正则表达式函数进行了测试。
|
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
|
字符串分割是字符串处理的一项基本功能，在.NET中通过String.Split方法可以轻松实现按特定字符组或字符串组进行的分割，并返回分割后的子字符串数组，当然Regex.Split方法提供了更为强大的分割功能，支持由正则表达式匹配项定义的分割字符串将输入的字符串拆分为一个子字符串数组。相对于.NET提供对字符串处理的强大支持，SQL Server就显得相对乏力，下面就来实现SQLCLR版本的Split函数。
由于字符串分割后返回的是数组，在SQL 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;
}
}
|
不难发现表值函数需要返回IEnumerable接口，行填充函数可以指定任意名称，上面的代码还指定数据表只包含一个item列。
通过Visual Studio对SqlServer.SqlClr.Functions项目进行编译和部署，在SQL Server对象资源管理器对应数据库的表值函数目录下发现增加了Clr_Split函数，通过修改右键菜单查看部署生成的T-SQL代码。
|
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]
|
部署工具对于item列生成nvarchar(4000)数据类型，可以在T-SQL中修改数据长度大小。下面通过两条T-SQL语句对Clr_Split函数进行测试，输出同一结果。
|
SELECT * FROM dbo . Clr_Split ( 'StarCraft|WarCraft|Diablo' , '|' );
SELECT * FROM dbo . Clr_Split ( 'StarCraft|WarCraft//Diablo' , '|,//' );
item
-----------------------------
StarCraft
WarCraft
Diablo
|
下面的C#代码实现了Split函数的另外一个版本，返回的记录集包含两列，除了分割值还增加了分割序号。
|
[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;
}
}
|
通过T-SQL对SplitWithOrder函数进行测试。
|
SELECT * FROM dbo . Clr_SplitWithOrder ( 'StarCraft|WarCraft//Diablo' , '|,//' ) ORDER BY orderId DESC ;
orderId item
----------- ----------------------------------------
3 Diablo
2 WarCraft
1 StarCraft
|
聚合函数的功能是对一组值进行计算并返回单个值。在SQL Server 2005之前，数据库引擎只支持内置聚合函数，例如常见的SUM、MAX、AVG和COUNT等函数，这些聚合函数对一组输入标量值执行操作，并且从该组值生成单个聚合值。在SQL Server 2005版本推出后，SQL Server同.NET CLR集成，使得开发人员能够通过.NET托管代码创建自定义聚合函数，并且使这些函数可应用于T-SQL编程。
本节，我们通过实现平方平均数函数来讲解如何使用.NET实现聚合函数。在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;
}
|
聚合函数模板包含一个Aggregate1结构，该结构除了声明可序列化外还指定了SqlUserDefinedAggregate特性来指示结构类型应如何注册为用户定义的聚合。SqlUserDefinedAggregate的Format属性通过Microsoft.SqlServer.Server.Format枚举来指定聚合的序列化格式，如果在聚合函数中只使用值类型成员，那么可以设置Format属性为Format.Native，如果设置为Format.UserDefined，开发人员需要自己实现Microsoft.SqlServer.Server.IBinarySerialize接口以支持序列化。另外SqlUserDefinedAggregate特性类还包含 IsInvariantToNulls 和IsInvariantToDuplicates等其他属性，关于这些属性功能具体可以参考MSDN。
Aggregate1 包含4个方法，这是查询处理器计算聚合所用的方法，如果编程人员对BizTalk组件开发比较熟悉，你会发现聚合函数编程类似于BizTalk中的自定义Functoid组件编程。MSDN文档对4个方法进行了具体解释。
Init 方法。
|
public void Init();
|
查询处理器使用此方法初始化聚合的计算。对于查询处理器正在聚合的每个组调用此方法一次。查询处理器可以选择重用聚合类的同一实例来计算多个组的聚合。Init 方法应在上一次使用此实例后根据需要执行清除，并允许重新启动新的聚合计算。
Accumulate 方法。
|
public void Accumulate(input_type Value);
|
查询处理器使用此方法累计聚合值。对于正在聚合的组中的每个值调用此方法一次。查询处理器仅在为聚合类的指定实例调用Init方法之后才调用此方法。此方法的实现应更新实例的状态以反映正在传递的参数值的累计。input_type参数是托管的 SQL Server 数据类型，该数据类型与CREATE AGGREGATE 语句中input_sqltype 所指定的本机SQL Server数据类型等效。
Merge 方法。
|
public void Merge(Aggregate1 Group);
|
此方法可以将此聚合的另一实例与当前实例合并。查询处理器使用此方法合并聚合的多个部分计算。
Terminate 方法。
|
public return_type Terminate();
|
此方法完成聚合计算并返回聚合的结果。return_type返回值类型应是托管的SQL Server数据类型，该数据类型是CREATE AGGREGATE语句中指定的return_sqltype 的托管等效类型。
通过以上的介绍，实现平方平均数聚合函数就变得比较简单，修改结构名称为QuadraticMean，并设置两个内部变量， 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);
}
}
|
通过Visaul Studio编译和部署项目，在SQL Server对象资源管理器对应数据库的聚合函数目录下发现增加了QuadraticMean函数，查看生成的T-SQL代码。
|
CREATE AGGREGATE [dbo] . [QuadraticMean]
( @input [float] )
RETURNS [float]
EXTERNAL NAME [SqlServer.SqlClr.Functions] . [QuadraticMean]
|
结合AVG函数对QuadraticMean函数进行测试。
|
SELECT avg ( Id ), dbo . QuadraticMean ( Id ) FROM dbo . GameInfo ;
----------- ----------------------
2 2.16024689946929
|
SQL Server 同.NET CLR的紧密集成使得.NET开发人员能够快速实现SQL Server扩展功能编程，一般使用SQLCLR来实现专门执行计算的操作功能，使用T-SQL来实现基于集合的操作功能。该文档全面讲解了如何通过C#和SQLCLR为SQL Server实现标量值、表值和聚合3类用户定义函数，并演示用户定义函数在T-SQL中的实际应用。 Base64 编码解码函数、正则表达式函数、字符串分割函数和平方平均数函数均属于基础函数，可被方便应用到实际业务系统中。
