Getting started with Sql Server 2005 User Defined Types (UDTs)

SQL Server 2005 Beta 2 on MSDN

MrDave's Blog! — Tue, 27 Jul 2004 10:02:00 GMT

ALTER ASSEMBLY - Take Two

Niels SQL Server Blog — Thu, 29 Jul 2004 15:54:00 GMT

ALTER ASSEMBLY - Take Two

Niels SQL Server Blog — Thu, 29 Jul 2004 16:10:00 GMT

ALTER ASSEMBLY - Take Two

re: Getting started with Sql Server 2005 User Defined Types (UDTs)

jasons — Mon, 02 Aug 2004 16:30:00 GMT

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).

re: Getting started with Sql Server 2005 User Defined Types (UDTs)

Angel — Mon, 02 Aug 2004 17:01:00 GMT

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.

Help...

Derrick — Thu, 12 Aug 2004 04:33:00 GMT

I tried to create a udt with this code.

#region Using directives

using System;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.IO;
#endregion

namespace TypeTest
{
[Serializable]
[SqlUserDefinedType(Format.Native, IsByteOrdered = true)]
public struct Point : INullable, IBinarySerialize
{
bool isNull;
private int _X;
private int _Y;
public bool IsNull
{
get{return isNull;}
}

public override string ToString()
{
return X + ":" + Y;
}

public static Point Parse(SqlString s)
{
string[] ls = Convert.ToString(s).Split(char.Parse(":"));
Point p = new Point();
p.X = Convert.ToInt32(ls[0]);
p.Y = Convert.ToInt32(ls[1]);
return p;
}

public void Read(BinaryReader r)
{
X = r.ReadInt32();
Y = r.ReadInt32();
isNull = BitConverter.ToBoolean(r.ReadBytes(1), 0);
}
public void Write(BinaryWriter w)
{
w.Write(X);
w.Write(Y);
w.Write(isNull);
}

public static Point Null
{
get
{
Point point = new Point();
point.isNull = true;
return point;
}
}

public int X
{
get { return _X; }
set { _X = value; }
}

public int Y
{
get { return _Y; }
set { _Y = value; }
}

}
}

I'm able to add the assembly to SQL but when I try to create type.

CREATE TYPE point external name TypeTest.Point

I get.

Msg 6556, Level 16, State 1, Line 1
CREATE TYPE failed because it could not find type 'Point' in assembly 'TypeTest'.
Msg 6597, Level 16, State 1, Line 1
CREATE TYPE failed.

Can anyone help me with this?

re: Getting started with Sql Server 2005 User Defined Types (UDTs)

Angel — Thu, 12 Aug 2004 17:22:00 GMT

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 drive\TypeTest.dll>
CREATE TYPE Point EXTERNAL NAME TypeTest:Point

Let me know if this works for you.