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.
Comments
- Anonymous
July 29, 2004
ALTER ASSEMBLY - Take Two - Anonymous
July 29, 2004
ALTER ASSEMBLY - Take Two - Anonymous
August 02, 2004
useless example sorry to say
for a start 0,0 is a valid point and so wouldn;t be deemed null i any natural sense. also x or y would never be null as they are value types so you should use SqlInt32 or int? (i.e the nullable version). - Anonymous
August 02, 2004
Jasons,
I agree, the example is not particularily usefull in defining a point UDT, it is (as stated) not even a complete example. I am just trying to give an idea of how to get started defining your own User defined Types.
That said, why would a null Point udt have to point to x=null, y=null? You can define what a Null Point is in the IsNull property, for example I could decide that a Point with x=100 and y=100 is a Null point. All I need to do is have IsNull return true when x=y=100 and have the static Null return a Point udt with x=y=100. - Anonymous
August 11, 2004
The comment has been removed - Anonymous
August 12, 2004
I have a few comments on the UDT itself:
You don't need to specify IsByteOrdered for format=Native udt.
You don't need to implement IBinarySerialize for format=Native udt. there is nothing wrong with it if you wan't to use this behavior on the client side, but the UDT will not be serialized on the server using your Read and Write methods.
IsNull is not going to work the way you expect it to. Once you create a Null UDT no matter what changes you do to it it will continue being IsNull=true, very bad. Instead of returning a property you should have some logic that returns true when this.udt = the udt created when calling Null. In this case when this udt = new Point.
The easiest way to properly register your UDT is using Visual Studio 2005 beta. If you don't want to use VS then the syntax for registering a UDT should be in the following format:
Register the assembly (you mention you got this working)
CREATE ASSEMBLY TypeTest FROM <local driveTypeTest.dll>
CREATE TYPE Point EXTERNAL NAME TypeTest:Point
Let me know if this works for you.