自定义多参数聚合函数

功能要求：

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

 

步骤：

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; } else m_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; } else this.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; else this.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; } else Max.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; else Max.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; } else Min.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; else Min.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 }