Здравствуйте. Определил свой UDT(User Defined Type) Point: | Код |
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native,
IsByteOrdered = true, ValidationMethodName = "ValidatePoint")]
public struct Point : INullable
{
private bool is_Null;
private Int32 _x;
private Int32 _y;
public bool IsNull
{
get
{
return (is_Null);
}
}
public static Point Null
{
get
{
Point pt = new Point();
pt.is_Null = true;
return pt;
}
}
public Point(Int32 x, Int32 y)
{
_x = x;
_y = y;
is_Null = false;
}
// Use StringBuilder to provide string representation of UDT.
public override string ToString()
{
// Since InvokeIfReceiverIsNull defaults to 'true'
// this test is unneccesary if Point is only being called
// from SQL.
if (this.IsNull)
return "NULL";
else
{
StringBuilder builder = new StringBuilder();
builder.Append(_x);
builder.Append(",");
builder.Append(_y);
return builder.ToString();
}
}
[SqlMethod(OnNullCall = false)]
public static Point Parse(SqlString s)
{
// With OnNullCall=false, this check is unnecessary if
// Point only called from SQL.
if (s.IsNull)
return Null;
// Parse input string to separate out Points.
Point pt = new Point();
string[] xy = s.Value.Split(",".ToCharArray());
pt.X = Int32.Parse(xy[0]);
pt.Y = Int32.Parse(xy[1]);
// Call ValidatePoint to enforce validation
// for string conversions.
if (!pt.ValidatePoint())
throw new ArgumentException("Invalid XY Pointinate values.");
return pt;
}
// X and Y Pointinates exposed as properties.
public Int32 X
{
get
{
return this._x;
}
// Call ValidatePoint to ensure valid range of Point values.
set
{
Int32 temp = _x;
_x = value;
if (!ValidatePoint())
{
_x = temp;
throw new ArgumentException("Invalid X Pointinate value.");
}
}
}
public Int32 Y
{
get
{
return this._y;
}
set
{
Int32 temp = _y;
_y = value;
if (!ValidatePoint())
{
_y = temp;
throw new ArgumentException("Invalid Y Pointinate value.");
}
}
}
// Validation method to enforce valid X and Y values.
private bool ValidatePoint()
{
// Allow only zero or positive integers for X and Y Pointinates.
if ((_x >= 0) && (_y >= 0))
{
return true;
}
else
{
return false;
}
}
// Distance from 0 to Point method.
[SqlMethod(OnNullCall = false)]
public Double Distance()
{
return DistanceFromXY(0, 0);
}
// Distance from Point to the specified Point method.
[SqlMethod(OnNullCall = false)]
public Double DistanceFrom(Point pFrom)
{
return DistanceFromXY(pFrom.X, pFrom.Y);
}
// Distance from Point to the specified x and y values method.
[SqlMethod(OnNullCall = false)]
public Double DistanceFromXY(Int32 iX, Int32 iY)
{
return Math.Sqrt(Math.Pow(iX - _x, 2.0) + Math.Pow(iY - _y, 2.0));
}
}
|
создал таже таблицу в MSSQL Server: СityID - int Location - Point написал агрегат, который выбирает из таблицы город, с наиболее удаленными от (0,0) координатами: | Код |
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]
public struct MostDistant
{
Point searched;
double distance;
public void Init()
{
distance = 0;
searched = new Point(0, 0);
}
public void Accumulate(Point Value)
{
double newdist = Math.Sqrt((Value.X) * (Value.X) + (Value.Y) * (Value.Y));
if (distance < newdist)
{
searched = Value;
}
}
public void Merge(MostDistant Group)
{
searched = Group.searched;
}
public SqlString Terminate()
{
// Put your code here
return new Point(searched.X,searched.Y).ToString();
}
}
Написал хранимую процедуру, что использует этот агрегат:
|
| Код |
CREATE PROCEDURE sp_mostdistant AS
SELECT dbo.MostDistant(Location)
FROM [NorthWind].[dbo].[Сity]
GO
|
Проверил - работает. Проблема в том, что я не могу ее вызвать из кода с# - возвращает пустой ридер. Помогите, пожалуйста. |
Вызов агрегата из Visual Studio 
Загрузка ...
