C#实现SQL Server2005的扩展聚合函数

今下午没什么事,想将以前费了好大力气写的一个对二维表查询的存储过程进行改进,存储过程用到了好几个游标动态生成SQL,且有两三百行,不但别人很难看懂,自已隔一段时间去看,都得老半天。当初也时没办法,为了实现功能就没考虑这么多了,今就想试试用case when 能不能把游标给去了,使变得更简洁,可读性强,还可提高性能。

  做到一半时遇到一个问题,动态生成case when 拼装成的sql查询出的结果,按instanceID合并成一行的问题,将下图一的结果合并成图二的结果,

各列值的类型是不确定的(表中各列是用户通过系统自定义添加进去),要是数据型好办,用Sum函数。

 

                             图一

 

 

 

 

 

 

                       图二

  想自定义一个类sum功能的对串进行拼接处理的聚合方法,在网上找了资料,发现可以用C#等语言开发自定义的聚合函数,因为.net是编译中 IL代码,理论上,只要是代码符合CTS,不同语言的代码是能互相调用。

     在vs2008用C#开发聚合函数过程:

 

首先用VS2008/VS2005建立一个SQL Server项目,如图6所示。


图6

点击“确定”按钮后,SQL Server项目会要求连接一个数据库,我们可以选择一个数据库,如图7所示。


图7

然后在工程中加入一个聚合类(joinstr.cs),如图8所示(可以开发Trigger 等)。

图8

joinstr.cs中的最终代码如下:

代码
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;


[Serializable]
[SqlUserDefinedAggregate(
    Format.UserDefined, 
//use custom serialization to serialize the intermediate result
    IsInvariantToNulls = true//optimizer property
    IsInvariantToDuplicates = false//optimizer property
    IsInvariantToOrder = false//optimizer property    
    MaxByteSize = 8000//maximum size in bytes of persisted value
]


public struct JionStr:IBinarySerialize
{
    
private StringBuilder sbIntermediate;
    
public void Init()
    {
       sbIntermediate 
= new StringBuilder();
    }

    
public void Accumulate(SqlString Value)
    {

        
if (Value == null || Value.ToString().ToLower().Equals("null"))
        {
            
return;
        }
        
else
        {
            sbIntermediate.Append(Value);
        }
    }

    
public void Merge(JionStr Group)
    {
        sbIntermediate.Append(Group.sbIntermediate);
    }

    
public SqlString Terminate()
    {
        
        
return new SqlString(sbIntermediate.ToString());
    }

    
// This is a place-holder member field
    private int var1;


    
#region IBinarySerialize Members

    
public void Read(System.IO.BinaryReader r)
    {
        sbIntermediate 
= new StringBuilder(r.ReadString());
    }

    
public void Write(System.IO.BinaryWriter w)
    {
        w.Write(
this.sbIntermediate.ToString());    
    }

    
#endregion
}

 

由于本例需要聚合字符串,而不是已经被序列化的类型,如int等,因此,需要实现 IBinarySerialize接口来手动序列化。使用C#实现SQL Server聚合函数,也会受到字符串最大长度为8000的限制。

在编写完上述代码后,可以使用Visual Studio来部署(右向工程,在弹出菜单上选“部署”即可)。

    也可以使用SQL语句来部署。假设上面的程序生成的dll为AggregateDemo.dll,可以使用下面的SQL语句来部署:


1 CREATE ASSEMBLY JoinStr FROM 'D:\DotNet\TestDemo\AggregateDemo\bin\Debug\AggregateDemo.dll'
2 
3 CREATE AGGREGATE JionStr (@input nvarchar(200)) RETURNS nvarchar(max)
4 EXTERNAL NAME JoinStr

 

要注意的是,字符串类型需要用nvarchar,而不能用varchar。

 

第一条SQL语句是装载dll,第二条SQL语句是注册joinstr聚合函数(每 一个C#类就是一个聚合函数)

在执行上面的SQL语句之前,需要将SQL Server2005的clr功能打开。(surface Area Configuration for Features)如图9所示。


图9

如果想删除上面建立的聚合函数,可以使用如下的SQL语句:



 

drop aggregate JoinStr


在删除聚合函数后,可以将AggregateDemo.dll卸载。


drop assembly JoinStr

 

现在可以使用joinstr来聚合字符串了。

 

  SELECT InstanceID,
      dbo.JionStr(case FieldID when '1' then FieldValue else null end) as [1]

  FROM  tb

 

  

posted @ 2010-03-19 18:01  Blues_  阅读(2369)  评论(0编辑  收藏  举报