sql2005自定义聚合函数—多字段合并

背景：

在sql2005环境下，需求是将数据分组聚合，能同时对多个字段进行合并，字段的属性是字符。

 

工程目录为：

 

代码：

long_merge.cs

=====================

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;

[Serializable]
[SqlUserDefinedAggregate(
    Format.UserDefined, //use clr 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 class LongMerge : IBinarySerialize
{
    /// <summary>
    /// The variable that holds the intermediate result of the concatenation
    /// </summary>
    private StringBuilder aritst;
    private StringBuilder property;
    private StringBuilder result;

    /// <summary>
    /// Initialize the internal data structures
    /// </summary>
    public void Init()
    {
        this.aritst = new StringBuilder();
        this.property = new StringBuilder();
        this.result = new StringBuilder();

    }

    /// <summary>
    /// Accumulate the next value, not if the value is null
    /// </summary>
    /// <param name="value"></param>
    public void Accumulate(LongMergeData longMergeData)
    {
        /*if (aritst.IsNull && property.IsNull) return;
        if (property.Value.Trim().Length == 0) return;
        if (aritst.Value.Trim().Length == 0)
            this.result.Append("大家传说：" + property.Value.Trim()).Append("/r/n");
        else
            this.result.Append(aritst.Value.Trim() + "说：" + property.Value.Trim()).Append("/r/n");
         */

        this.result.Append(longMergeData.ToString());
    }

    /// <summary>
    /// Merge the partially computed aggregate with this aggregate.
    /// </summary>
    /// <param name="other"></param>
    public void Merge(LongMerge other)
    {
        this.result.Append(other.result);
    }

    /// <summary>
    /// Called at the end of aggregation, to return the results of the aggregation.
    /// </summary>
    /// <returns></returns>
    public SqlString Terminate()
    {
        string output = string.Empty;
        //delete the trailing comma, if any
        if (this.result != null
            && this.result.Length > 0)
        {
            output = this.result.ToString(0, this.result.Length - 1);
        }

        return new SqlString(output);
    }

    public void Read(BinaryReader r)
    {
        result = new StringBuilder(r.ReadString());
    }

    public void Write(BinaryWriter w)
    {
        w.Write(this.result.ToString());
    }
}

 

 

 

long_merge_data.cs

/*UserDefinedType:
=======================

 */
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, MaxByteSize = 8000)]
public struct LongMergeData : INullable, IBinarySerialize
{
   public override string ToString()
    {
        if (aritst.IsNull && property.IsNull) return "";
        if (property.Value.Trim().Length == 0) return "";
        if (aritst.Value.Trim().Length == 0)
            return "大家传说：" + property.Value.Trim()+"/r/n";
        else
            return aritst.Value.Trim() + "说：" + property.Value.Trim()+"/r/n";
    }

    public bool IsNull
    {
        get
        {
            return false;
        }
    }

    public static LongMergeData Null
    {
        get
        {
             // LongMergeData o = new LongMergeData();
             // o.aritst = null;
             // o.property = null;
             // return o;
       
            throw new Exception("The method or operation is not implemented.");
        }
    }

    public static LongMergeData Parse(SqlString s)
    {
        throw new Exception("The method or operation is not implemented.");
    }

    public SqlString aritst;
    public SqlString property;
      #region IBinarySerialize Members
   

    void IBinarySerialize.Read(System.IO.BinaryReader r)
    {
        aritst = r.ReadString();
        property = r.ReadString();
    }

    void IBinarySerialize.Write(System.IO.BinaryWriter w)
    {

        if (!aritst.IsNull) w.Write(aritst.Value.ToString());
        else w.Write("");
        if (!property.IsNull) w.Write(property.Value.ToString());
        else w.Write("");
    }

    #endregion
}

UserDefinedFunctions

=============================

using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
    public static LongMergeData MakeLongMergeData(SqlString aritst, SqlString property)
{
    LongMergeData o = new LongMergeData();
    o.aritst = aritst;
    o.property = property;
    return o;
}
};