Getting started with Sql Server 2005 User Defined Types (UDTs)
I have really tried to start with the simplest possible UDT I could come up with. As you can see, the simplest UDT is still quite a handful, and this is still not a complete example.
using System;
using System.Data.Sql; // Required by SqlUserDefinedType .
using System.Data.SqlTypes; // Required by INullable.
[SqlUserDefinedType(Format.Native)]
public struct Point : INullable
{
//<optional>
public int X;
public int Y;
//you can place interesting point related methods here.
//</optional>
//required by deriving from INullable
public bool IsNull
{
get
{
// Put your code here, something like if both X and Y are null then return true.
return true;
}
}
//required by Sql Server CREATE ASSEMBLY
public static Point Null
{
get
{
Point h = new Point();
//set your UDT to the null state here
return h;
}
}
//required by Sql Server CREATE ASSEMBLY
public override string ToString()
{
string s = "null";
// Put your code here, something like s=X.ToString() + " , " + Y.ToString()
return s;
}
//required by Sql Server CREATE ASSEMBLY
public static Point Parse(SqlString s)
{
if (s.IsNull || s.Value.ToLower().Equals("null"))
return Null;
Point u = new Point();
//Normally you would parse "s" here and assign the values to properties in Point "u". ex: s="11,22" I would split s on the comma
//delimiter and associate u.X=input[0]; u.Y=input[1];
return u;
}
}
Let’s go over this example in detail:
[SqlUserDefinedType(
To create a User Defined Type you must define this custom attribute. This attribute has the following properties:
· Format – this is the storage format of the udt on the Server, we support Native and UserDefined in this release.
· MaxByteSize – (Format=UserDefined only)the maximum size of an instance of this type, in bytes. The max value we accept is 8k.
· IsByteOrdered – (Format=UserDefined only) (optional, default is false). is the binary representation of this type ordered, i.e. can the binary representation be used to compare instances of this type?
· IsFixedLength – (optional, default is false) . are all instances of the type of the same length
· ValidationMethodName –(optional, default is empty) . the method used to validate instances of the UDT, when the udt has been deserialized from a binary value that is not trusted.
These are extremely confusing properties since changing the Format changes the behavior and default of some of the other properties.
Format.Native)]
We have come across our first earth shattering decision. By marking our User Defined Type as Native I am telling Sql Server to go ahead and do all the work of serializing this type for me. Native UDTs are stored in the most efficient manner and will likely outperform similar UserDefined UDTs. There are two downsides that I can think of off the top of my head, and one of them is a biggie:
You can only use blittable fields in Native UDTs. Effectively this means that you can ONLY have fields of the following types:
bool, byte, sbyte, short, ushort, int, uint, long, ulong, float, double, SqlByte, SqlInt16, SqlInt32, SqlInt64, SqlDateTime, SqlSingle, SqlDouble, SqlMoney, SqlBoolean, and other Native UDTs.
Yes you did read that list correctly; String or related types are not allowed for Native User Defined Types. Now you know why I did not do a Person or Contact UDT for this getting started blog. I will blog more on the other downside as I go over UserDefined UDTs.
public struct Point : INullable
If you are going to make a Format.Native User Defined Type I would recommend using a struct. There really aren’t that many differences, you can’t have initializers so no default constructor or initializing fields in the declaration. If you want to make it a class you need to mark it with LayoutKind.Sequential https://tinyurl.com/4szzc . You need to derive from INullable for all User Defined Types.
public bool IsNull
It is up to you to provide the code that decides whether this udt should be considered NULL.
public static Point Null
You need to return a null UDT. What is a null UDT in this case? whatever you have decided to call a Null UDT in the IsNull above. To give you an example if I decide that a null Point is one where X=100 and Y=100 I would do an IsNull check to see if both fields are set to 100 in the IsNull above and I would return a udt with both fields set to 100 in this method. It is really up to you, but be consistent, makes sense?
public override string ToString()
This method will be called any time we are trying to show the UDT to the user. So if I do something like ExecuteReader and bind to a datagrid this is what I will see as the text for the udt column.
public static Point Parse(SqlString s)
This method will be called any time we try to create a UDT with a string. In TSQL the way to enter data into a UDT Column will work something like this:
CREATE TABLE mypoints (id int primary key, mypoint Point)
Insert into mypoints values (1, CONVERT(Point, '200,300')
When you call the CONVERT function it will pass ‘200,300’ to Point.Parse(SqlString s). It is up to you to split ‘200,300’ into X=200; Y=300.
Rambling out,
Standard Disclaimer: This post is provided “AS IS” and confers no rights. It is very likely that I have made some mistakes along the way.