多SQL_VARIANT参数SQLCLR自定义聚合 [英] SQLCLR custom aggregate with multiple sql_variant parameters
问题描述
海兰,
我有张贴有关CLR用户定义的聚合问题,一个月前几本OON的帖子。
I have post a question about CLR User-Defined Aggregates few month ago oon this post.
这就像一个魅力。但现在,我想相当与sql_variant的类型两个参数相同的功能。
This works like a charm. But now I would like to quite the same functions with the two parameters in sql_variant type.
就像在我以前的帖子,这两个功能是SMAX和SMIN并返回根据第二次的第一个值。
Like in my previous post, the two function would be sMax and sMin and will return the first value depending on the second.
我发现sql_variant的类型是C#中的对象类型。但我有困难的积累和比较的对象。
I found that the sql_variant type is a object type in C#. But I having difficulties to accumulate and compare the object.
什么是不知道的类型比较这两个目标的最佳选择吗?
What is the best option to compare this two object without knowing the type?
推荐答案
感谢您的答复。我用这个理念来完成我的聚合函数。
Thanks for your response. I use this philosophy to complete my aggregate function.
到目前为止,它的工作,但不是everythinks精...
So far, it's working but not everythinks fine...
- 使用isnull不工作
- 如果不使用isnull,使用Convert.ToString是不好的,它取代空字符串空值。但是,如果没有它具有空值
- 在读取功能崩溃:使用ReadString功能。的ReadBytes比较好?
- 要执行的CompareTo,使用转换和的toString,是它的好办法?
- the isNull not working
- without the isNull, using the Convert.ToString is not good, it replace null value by empty string. But without it crash with empty values
- in the Read function : use the ReadString function. ReadBytes is better?
- To perform the CompareTo, using the Convert and the toString, is it the good approach ?
该代码:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Diagnostics.Eventing.Reader;
using System.Globalization;
using Microsoft.SqlServer.Server;
using System.Text;
using System.Collections;
using System.IO;
[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined,
IsInvariantToOrder = true,
IsInvariantToNulls = true,
IsInvariantToDuplicates = true,
MaxByteSize = -1)]
public struct sMax : IBinarySerialize, INullable
{
#region Helpers
private struct MyData
{
public object Data { get; set; }
public InType DataType { get; set; }
public object Group { get; set; }
public InType GroupType { get; set; }
public int CompareTo(MyData other)
{
if (Group == null)
return other.Group == null ? 0 : -1;
if (other.Group == null)
return 1;
if (GroupType == InType.Int)
return Convert.ToInt32(Group).CompareTo(Convert.ToInt32(other.Group));
if (GroupType == InType.BigInt)
return Convert.ToInt64(Group).CompareTo(Convert.ToInt64(other.Group));
if (GroupType == InType.Double)
return Convert.ToDouble(Group).CompareTo(Convert.ToDouble(other.Group));
if (GroupType == InType.Date)
return Convert.ToDateTime(Group.ToString()).CompareTo(Convert.ToDateTime(other.Group.ToString()));
if (GroupType == InType.String)
return Convert.ToString(Group).CompareTo(Convert.ToString(other.Group));
else
return 0;
}
public static bool operator < (MyData left, MyData right)
{
return left.CompareTo(right) == -1;
}
public static bool operator > (MyData left, MyData right)
{
return left.CompareTo(right) == 1;
}
}
private enum InType
{
String,
Int,
BigInt,
Date,
Double,
Unknow
}
private InType GetType(object value)
{
if (value.GetType() == typeof(SqlInt32))
return InType.Int;
else if (value.GetType() == typeof(SqlInt64))
return InType.BigInt;
else if (value.GetType() == typeof(SqlString))
return InType.String;
else if (value.GetType() == typeof(SqlDateTime))
return InType.Date;
else if (value.GetType() == typeof(SqlDouble))
return InType.Double;
else
return InType.Unknow;
}
#endregion
private MyData _maxItem;
public void Init()
{
_maxItem = default(MyData);
this.IsNull = true;
}
public void Accumulate(object data, object group)
{
if (data != null && group != null)
{
var current = new MyData
{
Data = data,
Group = group,
DataType = GetType(data),
GroupType = GetType(group)
};
if (current > _maxItem)
{
_maxItem = current;
}
}
}
public void Merge(sMax other)
{
if (other._maxItem > _maxItem)
{
_maxItem = other._maxItem;
}
}
public SqlString Terminate()
{
return this.IsNull ? SqlString.Null : new SqlString(_maxItem.Data.ToString());
}
public void Read(BinaryReader reader)
{
IsNull = reader.ReadBoolean();
_maxItem.Group = reader.ReadString();
_maxItem.Data = reader.ReadString();
if (_maxItem.Data != null)
this.IsNull = false;
}
public void Write(BinaryWriter writer)
{
writer.Write(this.IsNull);
writer.Write(_maxItem.Group.ToString());
writer.Write(_maxItem.Data.ToString());
}
public Boolean IsNull { get; private set; }
}
这篇关于多SQL_VARIANT参数SQLCLR自定义聚合的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!