Implementing managed user defined types (UDT) for use in SQL Server 2005, as with stored procedures, is again relatively straight forward – you need to create a class library which implements the UDT.
A managed UDT takes the form of a C# class or struct, use of namespaces to enclose your UDTs is optional. For this example I’ll create a Coordinate UDT which has X and Y int properties.
First your class or struct must be serialisable and must implement the INullable interface, further you must decorate the type with the SqlUserDefinedType attribute, found in the Microsoft.SqlServer.Server namespace.
The Coordinate type is therefore declared as follows;
namespace MyProcsAndUDTs
{
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native, IsByteOrdered = true, ValidationMethodName = "ValidateCoord")]
public struct MyCoordType : INullable
{
private int _x;
private int _y;
private bool _isnull;
public bool IsNull
{
get { return _isnull; }
}
public static MyCoordType Null
{
get
{
MyCoordType coord = new MyCoordType();
coord._isnull = true;
return coord;
}
}
You must implement the IsNull property which indicates whether the UDT is NULL, and the static Null property which returns a new “Null” instance of the UDT.You also need to implement a static Parse method which works in tandem with the overriden ToString method. Together these to methods render the UDT’s value to a string readable form, and Parse a string readable form to a native UDT, as shown below;
[Microsoft.SqlServer.Server.SqlMethod(OnNullCall = false)]
public static MyCoordType Parse(SqlString sqlString)
{
if (sqlString.IsNull)
return Null;
string[] xy = sqlString.Value.Split(",".ToCharArray());
MyCoordType coord = new MyCoordType();
coord.X = Int32.Parse(xy[0]);
coord.Y = Int32.Parse(xy[1]);
if (!coord.ValidateCoord())
throw new ArgumentException(@"Invalid X & Y Coordinate Value");
return coord;
}
public override string ToString()
{
if (this.IsNull)
return "NULL";
StringBuilder sb = new StringBuilder();
sb.Append(_x);
sb.Append(",");
sb.Append(_y);
return sb.ToString();
}
The OnNullCall attribute is used to indicate that the method may be called with “Null” arguments and therefore that the method may return “Null”. The property methods are described below and are pretty standard;
public int X
{
get { return _x; }
set { _x = value; _isnull = false; }
}
public int Y
{
get { return _y; }
set { _y = value; _isnull = false; }
}
To introduce the UDT into SQL Server, you need to register the assembly with SQL Server and create a UDT which references the managed struct/class.
CREATE ASSEMBLY SqlServerProcs FROM N'C:\dev\SqlServerProcs\....\Debug\SqlServerProcs.dll'
WITH PERMISSION_SET = SAFECREATE TYPE XYCoord
EXTERNAL NAME SqlServerProcs.[MyProcsAndUDTs.MyCoordType]
Having done this, you can use the UDT almost as you would any native Sql type;
CREATE TABLE dbo.Coords (ID int IDENTITY(1,1) PRIMARY KEY, XY XYCoord)
INSERT INTO Coords ([XY]) VALUES (N'1,3')
SELECT ID, XY FROM Coords
SELECT ID, XY.ToString() AS XY FROM Coords
RSS - Posts
My 