用C#自定义SQL聚合函数.docx
《用C#自定义SQL聚合函数.docx》由会员分享，可在线阅读，更多相关《用C#自定义SQL聚合函数.docx（24页珍藏版）》请在冰豆网上搜索。
用C#自定义SQL聚合函数
使用C#编写自定义的数据库聚合函数
---本文以SQLServer2008为例
SQL2008提供的聚合函数已经很强大，但遇到某些特殊聚合时，我们就没办法直接在数据库中实现了。
比如这样的需求：
通常，大部份的程序员会使用存储过程配合游标的方式来实现，或者直接交给前端程序用循环的方式去实现。
但如果这样的话，性能会比较低下（以刚才给出的需求为例，用存储过程加游标的方式，169万条记录，用了45分钟（本人亲测）。
另外，代码量大，也不利于维护。
其实，我们可以利用C#编写自定义聚合函数，然后部署到SQL2008中进行调用。
还是以刚才的需求为例进行测试，结果你完全没有想到吧？
只需4分钟就聚合完毕。
下面结合自己开发的聚合函数，本人将自定义聚合函数的方法详细整理如下：
第一步：
打开MicrosoftVisualStudio2008编译器，新建SQLServer项目，并设置好项目的“名称（N）:
”，”位置（L）:
”，最后单击“确定”。
第二步：
在弹出的“新建数据库引用”对话框中设置数据库服务器名，登录验证方式，用户名，密码等，设置完毕单击“确定”。
第三步：
弹出的提示对话框会询问“要在此连接上启用SQL/CLR调试吗？
”，点击“否”。
第四步：
在解决方案资源管理器中选中项目名（如本例的“unionSmsNo”），然后单击右键，选“添加”“聚合”。
接着输入聚合类名称，本例用默认的“Aggregate1.cs”，然后单击“添加”。
最后，自动生成的代码如下图所示：
简单说明一下自动生成的类中，各成员函数的作用：
Init（）：
给每个要聚合的新组初始化；
Accumulate（）：
扫描到分组中的每一行记录时
Merge（）：
将一个聚合组的结果合并到当前组中
Terminate（）：
返回结果；
第五步：
填充并修改代码（需要实现序列化接口）。
全部源码如下所示：
usingSystem;
usingSystem.Data;
usingSystem.Data.SqlClient;
usingSystem.Data.SqlTypes;
usingMicrosoft.SqlServer.Server;
usingSystem.Text;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate（Format.UserDefined,
IsInvariantToDuplicates=false,
IsInvariantToNulls=true,
IsInvariantToOrder=false,
IsNullIfEmpty=true,
MaxByteSize=8000
）]
publicstructStrJoin:
IBinarySerialize
{
privateStringBuilder_result;
publicvoidInit（）
{
_result=newStringBuilder（）;
}
publicvoidAccumulate（SqlStringValue）
{
if（Value.IsNull）
{
return;
}
else
{
if（_result.Length>0）
_result.Append（","）;
_result.Append（Value.Value）;
}
}
publicvoidMerge（StrJoinGroup）
{
_result.Append（Group._result）;
}
publicSqlStringTerminate（）
{
if（_result.Length>0）
{
returnnewSqlString（_result.ToString（））;
}
returnnewSqlString（""）;
}
#regionIBinarySerializeMembers
publicvoidRead（System.IO.BinaryReaderr）
{
_result=newStringBuilder（r.ReadString（））;
}
publicvoidWrite（System.IO.BinaryWriterw）
{
w.Write（_result.ToString（））;
}
#endregion
}
第六步：
生成解决方案（DLL文件）
方法如下：
代码录入完毕，选择菜单栏的“生成”—>“生成解决方案“。
第七步：
找到生成的DLL文件。
方法如下：
生成解决方案后，在“解决方案资源管理器”的项目名“UnionSmsNo”上右击，选择“在windows资源管理中打开文件夹（x）”。
此时我们已经打开了项目所在的文件夹
接着打开项目文件夹“UnionSmsNo”的下层子目录:
\bin\debug。
即可看到已经生成的UnionSmsNo.dll文件。
第八步：
将自定义的聚合函数部署到数据库上。
方法一：
（1）启用CLR集成功能后直接在C#编译器上进行部署
默认情况下，MicrosoftSQLServer中禁用公共语言运行库（CLR）集成功能，部署前需启用CLR功能。
操作方法：
打开SQLServer2008的ManagementStudion工具，在查询窗口输入以下命令：
sp_configure'clrenabled',1
GO
RECONFIGURE
GO
执行情况见下图：
（2）回到C#编译器，在项目名“UnionSmsNo”上右击，选择“部署”。
此时状态栏会显示“已启动部署…”，耐心等待直到部署完成。
特别提示：
有时会提示连接超时，部署失败。
此时需将数据库服务器的timeout值设大一点，然后再进行部署。
操作如下：
在C#编译器中选择“视图”菜单下的“服务器资源管理器”打开服务器资源管理器。
然后在数据库服务器名称（本例为db_58）上单击右键，选“修改连接”
在打开的“修改连接”对话框中单击“高级”按钮
在高级属性对话框中找到ConnectTimeout一栏，将值设为3600（单位为秒，3600秒即1个小时）。
设置好Timeout属性后，再重新进行部署即可！
部署完成后，打开SQLServer2008的ManagementStudion工具，接着选择“视图”菜单下的“对象资源管理器”打开对象资源管理器（如下图所示）。
在对象资源管理器中展开数据库对象（如下图所示，图中p_sms为数据库名），我们可以看到“可编程性”->“函数”->“聚合函数”下多了个”dbo.StrJoin”的函数。
在“可编程性”->“程序集”下多了个“UnionSmsNO”程序集。
说明部署已经完成。
方法二：
（1）将生成的DLL拷贝到数据库服务器上（用C#开发聚合函数的电脑和数据库服务器可能不是同一台电脑，切记DLL文件要拷到服务上）
本例中，服务器目录为：
“E:
\Merge”，拷贝后如下图所示：
（2）打开SQLServer2008的ManagementStudion工具，在查询窗口输入以下命令注册聚合函数。
命令格式：
CREATE ASSEMBLY [程序集名称] FROM '文件完整路径'
CREATEAGGREGATE[函数名]（@inputnvarchar（200））RETURNSnvarchar（max）
EXTERNALNAME [程序集名称].[函数名]
本例所输命令如下：
CREATEASSEMBLYUnionSmsNoFROM'E:
\Merge\UnionSmsNo.dll'
CREATEAGGREGATEStrJoin（@inputnvarchar（200））RETURNSnvarchar（max）
EXTERNALNAMEUnionSmsNo.StrJoin
调用示例：
--新建测试表
createtableTest
（
namevarchar（10）,
bookvarchar（10）
）
--插入数据
insertintoTest
select'jack'name,'book1'book
union
select'jack'name,'book2'book
union
select'jack'name,'book3'book
union
select'Mary'name,'book4'book
union
select'Mary'name,'book5'book
union
select'Mike'name,'book1'book
union
select'Mike'name,'book5'book
union
select'Mike'name,'book7'book
union
select'Mike'name,'book9'book
go
Select*fromtest
先查看一下表记录，如下图：
聚合查询：
selectname,dbo.StrJoin（book）fromtestgroupbyname
结果如下图所示：
作者：
唐绍国QQ:
85207174
另一范例：
聚合时找长度最大的列值：
需求：
合并test表中ID相同的记录，全并时，bal汇总（求和），address取长度最大的。
合并后的结果如下图所示：
C#源码如下：
usingSystem;
usingSystem.Data;
usingSystem.Data.SqlClient;
usingSystem.Data.SqlTypes;
usingMicrosoft.SqlServer.Server;
usingSystem.Text;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate（Format.UserDefined,
IsInvariantToDuplicates=false,
IsInvariantToNulls=true,
IsInvariantToOrder=false,
IsNullIfEmpty=true,
MaxByteSize=8000
）]
publicstructMaxLen:
IBinarySerialize
{
privateStringBuilder_result;
privatestringtemp;
privateintn;
publicvoidInit（）
{
_result=newStringBuilder（）;
_result.Append（""）;
}
/*
*Accumulate说明Value是分组扫描到的当前行的聚合列值
*
*/
publicvoidAccumulate（SqlStringValue）
{
if（Value.Value.ToString（）.Length>_result.Length）
{
_result.Remove（0,_result.Length）;
_result.Append（Value.Value.ToString（））;
}
}
publicvoidMerge（MaxLenGroup）
{
//if（Group._result.Length>0）
//{
//_result.Append（Group._result）;
//}
}
publicSqlStringTerminate（）
{
if（_result.Length>0）
{
returnnewSqlString（_result.ToString（））;
}
returnnewSqlString（""）;
}
#regionIBinarySerializeMembers
publicvoidRead（System.IO.BinaryReaderr）
{
_result=newStringBuilder（r.ReadString（））;
}
publicvoidWrite（System.IO.BinaryWriterw）
{
w.Write（_result.ToString（））;
}
#endregion
}
部署后调用方法如下：
selectID,dbo.MaxLen（Address）,SUM（bal）fromtestgroupbyid
结果：
在SQLServer2005中添加了对CLR的支持，这使得我们可以使用C#，VB.NET等语言为SQLServer编写函数、存储过程以及触发器等对象。
如何创建这些对象就不多说了，在网上搜一搜都有很多。
这里就说一下在创建聚合函数的时候一些值得注意的问题。
自定义聚合函数是以一个值类型对象的形式来实现的，这个对象必须被序列化到数据库中。
如果该对象有int或double等值类型的字段，那么几乎不会产生问题。
但是如果有string等类类型的字段，在SQLServer中创建聚合函数的时候很有可能会出现类似下面的6225号错误消息；
对类型SqlServerProject.Aggregate做标记以进行本机序列化，但是类型Aggregate的字段result为string类型（它是非值类型）。
本机序列化类型只能有可直接复制到本机结构中的字段类型。
如果希望有任何其他类型的字段，请考虑使用其他的序列化格式，如用户定义序列化。
出现这个错误的原因是，对于int、double等类型的数据，它们直接对应操作系统使用的本机数据类型，例如int和double在C++中都有相应的类型，实际上在C#和C++中它们的结构都是一样的，因此在序列化的时候可以将这些类型的字段当作本机类型来处理。
而对于类类型的字段，例如string，在操作系统中没有对应的数据类型，因此这些字段不能直接序列化。
用户必须手动添加序列化代码，告诉SQLServer如何去序列化这些类型的字段。
我们所要做的工作非常简单，只需要为聚合函数对象实现IBinarySerialize接口即可。
例如：
public struct Aggregate :
IBinarySerialize {
private string result;
#region IBinarySerialize 成员
public void Read（System.IO.BinaryReader r） {
this.result = r.ReadString（）;
}
public void Write（System.IO.BinaryWriter w） {
w.Write（this.result）;
}
#endregion
}
IBinarySerialze接口有两个方法，Read（）方法从序列化流中还原字段，也就是把二进制数据转换成string，这里我们不必手动进行转换，因为该方法的BinaryReader类型的参数已经提供了一系列进行转换的方法，使用Read前缀的方法可以从二进制流中转换出不同类型的值。
而Write（）方法用于把字段写入序列化流，也就是将字段转换成二进制数据，同理，BinaryWriter参数也已经提供了一系列进行转换的方法，直接调用即可。
此时问题似乎解决了，不过再次创建聚合函数，又可能会出现下面的的6222号错误：
对类型SqlServerProject.Aggregate做标记以进行本机序列化，但是类型Aggregate的字段result对于本机序列化无效。
这段话让人摸不着头脑，我费了好大劲也搞不出个所以然。
不过最后原因还是找到了。
看到聚合函数对象的声明上面那句代码：
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate（Format.Native）]
这是VS自动为我们生成的，可是问题也恰恰是由这句代码引起的。
只要我们把Format后面的值改为这样：
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate（Format.UserDefined）]
问题就解决了。
原因跟上一个问题很相似，Format.Native的意思是使用本机数据类型来保存这个实现聚合函数的值对象，只有当该对象中的字段都有对应的本机数据类型时才有效，例如int，long等。
由于我在这个对象中使用了string类型的字段，导致问题发生。
Format.UserDefined表示对象中的字段使用的是用户定义类型，也就是没有相应本机类型的数据类型。
string满足该条件，所以要把Native改成UserDefined。
最近通过不断XX，才发现，其实不用自定义聚合函数，还可以直接这样写：
selectname,
（
selectbook+','fromtestastemp
wheretemp.name=test.nameforxmlpath（''）
）
fromtestgroupbyname
升级会员 