自定义多参数聚合函数

功能要求：

取分组内某个参数最大或最小时，取另外一个参数的值。

 

步骤：

1. 定义一个自定义的数据类型，

2. 定义一个构造该数据类型的函数

3. 定义一个接受自定义数据类型的聚合函数

 

类型定义： 

using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;using System.Runtime.InteropServices;[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedType( Format.UserDefined, MaxByteSize = 8000 )]public struct CData : INullable, IBinarySerialize{// 私有成员private bool m_Null;public string CmpType;public object CompareField;public string DataType;public object Data;/// <summary>/// 获取字符串/// </summary>/// <returns></returns>public override string ToString() { return CmpType + "/0" + CompareField.ToString() + "/0" + DataType + "/0" + Data.ToString(); }/// <summary>/// 是否为空/// </summary>public bool IsNull { get { return m_Null; } set { m_Null = value; } }/// <summary>/// 构造空对象/// </summary>public static CData Null{get{CData h = new CData();h.CmpType = "System.DBNull";h.DataType = "System.DBNull";h.CompareField = null;h.Data = null;h.m_Null = true;return h;}}/// <summary>/// 强制类型转换/// </summary>/// <param name="s"></param>/// <returns></returns>public static CData Parse( SqlString s ){if ( s.IsNull )return Null;CData u = CData.Null;string value = s.Value;if ( value == "null" || string.IsNullOrEmpty( value ) ) return Null;string[] parts = value.Split( '/0' );u.CmpType = parts[0];Type dt = Type.GetType( u.CmpType );if ( dt != null )u.CompareField = Convert.ChangeType( parts[1], dt );u.DataType = parts[2];dt = Type.GetType( u.DataType );if ( dt != null )u.Data = Convert.ChangeType( parts[3], dt );return u;}/// <summary>/// 构造函数/// </summary>/// <param name="Type"></param>/// <param name="GroupField"></param>/// <param name="value"></param>public CData( object CompareField, object Data ){this.CmpType = "System.DBNull";this.CompareField = null;this.DataType = "System.DBNull";this.Data = null;if ( CompareField != null ){this.CmpType = CompareField.GetType().ToString();this.CmpType = this.CmpType.Replace( "System.Data.SqlTypes.Sql", "System." );this.CompareField = CompareField;m_Null = false;}elsem_Null = true;if ( Data != null ){this.DataType = Data.GetType().ToString();this.DataType = this.DataType.Replace( "System.Data.SqlTypes.Sql", "System." );this.Data = Data;}}/// <summary>/// 自定义类型比较大小/// </summary>/// <param name="type"></param>/// <returns></returns>public int CompareTo( CData objr ){return CompareTo( ( this.CmpType == "System.DBNull" ? objr.CmpType : this.CmpType ), this.CompareField, objr.CompareField );}/// <summary>/// 数据库中与C#中的数据类型对照/// </summary>/// <param name="type"></param>/// <returns></returns>public static int CompareTo( string lType, object l, object r ){if ( lType == "System.DBNull" ) return 0;// 如果两个对象的比较字段的数据类型都为空，则认为他们相等if ( l == null && r != null )return -1;else if ( l == null && r == null )return 0;else if ( l != null && r == null )return 1;else{switch ( lType ){case "System.Byte":return ( Byte )l > ( Byte )r ? 1 : ( ( Byte )l == ( Byte )r ? 0 : -1 );case "System.Int16":return Convert.ToInt16( l ) > Convert.ToInt16( r ) ? 1 : ( Convert.ToInt16( l ) == Convert.ToInt16( r ) ? 0 : -1 );case "System.Int32":return ( int )( l ) > ( int )( r ) ? 1 : ( ( int )( l ) == ( int )( r ) ? 0 : -1 );case "System.Int64":return Convert.ToInt64( l ) > Convert.ToInt64( r ) ? 1 : ( Convert.ToInt16( l ) == Convert.ToInt64( r ) ? 0 : -1 );case "System.Byte[]":return ( ( System.Byte[] )l ).Length - ( ( System.Byte[] )r ).Length;case "System.Decimal":return Convert.ToDecimal( l ) > Convert.ToDecimal( r ) ? 1 : ( Convert.ToDecimal( l ) == Convert.ToDecimal( r ) ? 0 : -1 );case "System.DateTime":return Convert.ToDateTime( l ) > Convert.ToDateTime( r ) ? 1 : ( Convert.ToDateTime( l ) == Convert.ToDateTime( r ) ? 0 : -1 );case "System.Double":return Convert.ToDouble( l ) > Convert.ToDouble( r ) ? 1 : ( Convert.ToDouble( l ) == Convert.ToDouble( r ) ? 0 : -1 );case "System.Single":return Convert.ToSingle( l ) > Convert.ToSingle( r ) ? 1 : ( Convert.ToSingle( l ) == Convert.ToSingle( r ) ? 0 : -1 );case "System.Object":return 0;case "System.String":case "System.Guid":default:return l.ToString().CompareTo( r );}}}#region IBinarySerialize 自定义数据类型的序列化/反序列化/// <summary>/// 读取数据/// </summary>/// <param name="r"></param>void IBinarySerialize.Read( System.IO.BinaryReader r ){this.CmpType = r.ReadString();Type dType;string str = r.ReadString();dType = Type.GetType( this.CmpType );if ( str == "" || dType == null || dType == typeof( DBNull ) ){m_Null = true;this.CompareField = null;}elsethis.CompareField = Convert.ChangeType( str, dType );this.DataType = r.ReadString();str = r.ReadString();dType = Type.GetType( this.DataType );if ( str == "" || dType == null || dType == typeof( DBNull ) )this.Data = null;elsethis.Data = Convert.ChangeType( str, dType );}/// <summary>/// 持久化数据/// </summary>/// <param name="w"></param>void IBinarySerialize.Write( System.IO.BinaryWriter w ){w.Write( this.CmpType );string str = "";if ( this.CompareField != null )str = this.CompareField.ToString();w.Write( str );w.Write( this.DataType );str = "";if ( this.Data != null )str = this.Data.ToString();w.Write( str );}#endregion}

 

 

构造数据类型的方法定义： 

using System;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 CData MakeCDataArgs( object CompareField, object Data ){return new CData( CompareField, Data );}};

 

 

取大值聚合函数定义： 

using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate( Format.UserDefined,IsInvariantToDuplicates = false,IsInvariantToNulls = true,IsInvariantToOrder = false,IsNullIfEmpty = false,MaxByteSize = 8000)]public struct CMax : IBinarySerialize{private CData Max;public void Init() { Max = CData.Null; }public void Accumulate( CData Data ){if ( Max.IsNull || Max.CompareTo( Data ) < 0 ){string CmpType = Max.CmpType == "System.DBNull" ? Data.CmpType : Max.CmpType;string DataType = Max.DataType == "System.DBNull" ? Data.DataType : Max.DataType;Max = new CData( Data.CompareField, Data.Data );Max.CmpType = CmpType.Replace( "System.Data.SqlTypes.Sql", "System." ); ;Max.DataType = DataType.Replace( "System.Data.SqlTypes.Sql", "System." ); ;}}public void Merge( CMax CClass ){if ( Max.IsNull || Max.CompareTo( CClass.Max ) < 0 ){string CmpType = Max.CmpType == "System.DBNull" ? CClass.Max.CmpType : Max.CmpType;string DataType = Max.DataType == "System.DBNull" ? CClass.Max.DataType : Max.DataType;Max = new CData( CClass.Max.CompareField, CClass.Max.Data );Max.CmpType = CmpType.Replace( "System.Data.SqlTypes.Sql", "System." ); ;Max.DataType = DataType.Replace( "System.Data.SqlTypes.Sql", "System." ); ;}}public object Terminate(){return Max.Data;}#region IBinarySerialize 成员/// <summary>/// 读取数据/// </summary>/// <param name="r"></param>void IBinarySerialize.Read( System.IO.BinaryReader r ){Max.CmpType = r.ReadString();Type dType;string str = r.ReadString();dType = Type.GetType( Max.CmpType );if ( str == "" || dType == null || dType == typeof( DBNull ) ){Max.IsNull = true;Max.CompareField = null;}elseMax.CompareField = Convert.ChangeType( str, dType );Max.DataType = r.ReadString();str = r.ReadString();dType = Type.GetType( Max.DataType );if ( str == "" || dType == null || dType == typeof( DBNull ) )Max.Data = null;elseMax.Data = Convert.ChangeType( str, dType );}/// <summary>/// 持久化数据/// </summary>/// <param name="w"></param>void IBinarySerialize.Write( System.IO.BinaryWriter w ){w.Write( Max.CmpType );string str = "";if ( Max.CompareField != null )str = Max.CompareField.ToString();w.Write( str );w.Write( Max.DataType );str = "";if ( Max.Data != null )str = Max.Data.ToString();w.Write( str );}#endregion}

 

 

取小值聚合函数定义： 

using System;using System.Collections.Generic;using System.Text;using Microsoft.SqlServer.Server;[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate( Format.UserDefined,IsInvariantToDuplicates = false,IsInvariantToNulls = true,IsInvariantToOrder = false,IsNullIfEmpty = false,MaxByteSize = 8000)]public struct CMin : IBinarySerialize{private CData Min;public void Init() { Min = CData.Null; }/// <summary>/// 在这个函数中，字符串 ''与null是相同的大小/// </summary>/// <param name="Data"></param>public void Accumulate( CData Data ){if ( Data.IsNull || Min.CompareTo( Data ) > 0 ){string CmpType = Min.CmpType == "System.DBNull" ? Data.CmpType : Min.CmpType;string DataType = Min.DataType == "System.DBNull" ? Data.DataType : Min.DataType;Min = new CData( Data.CompareField, Data.Data );Min.CmpType = CmpType.Replace( "System.Data.SqlTypes.Sql", "System." ); ;Min.DataType = DataType.Replace( "System.Data.SqlTypes.Sql", "System." ); ;}}public void Merge( CMin CClass ){if ( CClass.Min.IsNull || Min.CompareTo( CClass.Min ) > 0 ){string CmpType = Min.CmpType == "System.DBNull" ? CClass.Min.CmpType : Min.CmpType;string DataType = Min.DataType == "System.DBNull" ? CClass.Min.DataType : Min.DataType;Min = new CData( CClass.Min.CompareField, CClass.Min.Data );Min.CmpType = CmpType.Replace( "System.Data.SqlTypes.Sql", "System." ); ;Min.DataType = DataType.Replace( "System.Data.SqlTypes.Sql", "System." ); ;}}public object Terminate(){return Min.Data;}#region IBinarySerialize 成员/// <summary>/// 读取数据/// </summary>/// <param name="r"></param>void IBinarySerialize.Read( System.IO.BinaryReader r ){Min.CmpType = r.ReadString();Type dType;string str = r.ReadString();dType = Type.GetType( Min.CmpType );if ( str == "" || dType == null || dType == typeof( DBNull ) ){Min.IsNull = true;Min.CompareField = null;}elseMin.CompareField = Convert.ChangeType( str, dType );Min.DataType = r.ReadString();str = r.ReadString();dType = Type.GetType( Min.DataType );if ( str == "" || dType == null || dType == typeof( DBNull ) )Min.Data = null;elseMin.Data = Convert.ChangeType( str, dType );}/// <summary>/// 持久化数据/// </summary>/// <param name="w"></param>void IBinarySerialize.Write( System.IO.BinaryWriter w ){w.Write( Min.CmpType );string str = "";if ( Min.CompareField != null )str = Min.CompareField.ToString();w.Write( str );w.Write( Min.DataType );str = "";if ( Min.Data != null )str = Min.Data.ToString();w.Write( str );}#endregion}