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