Working with User Defined Types (UDT)

What is UDT

Microsoft SQL Server 2005 introduced a new possibility to extend the standard type set with data types created in CLR. These types can be used to define columns in tables and variables, in triggers, stored procedures, and functions. UDT is an assembly containing a class written on any programming language. The language must support .NET Framework version 2.0 or higher.

SQL Server requirements

User Defined Types are supported by SQL Server 2005 and higher versions. It is necessary to make sure that CLR integration of the server is enabled. By default in SQL Server it is disabled. You can check whether the CLR integration is currently enabled running the following system routine:

sp_configure 'clr enabled'

This routine returns a dataset. If the value of the run_value field equals to 0, you need to enable CLR running the same routine with one additional parameter:

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

Calling RECONFIGURE is required when applying a new value.

Client requirements

Here are the client requirements:

.NET Framework version 2.00 or higher;
Microsoft SQL Native Client;
the assembly implementing UDT in an accessible place;
the Devart.Sdac.UDTProxy.dll in an accessible place. This file can be found in the Bin subfolder of the SDAC installation directory.

An accessible place means one of the following locations:

The application directory (the preferable way);
Global Assembly Cache (GAC);
Any directory registered in the PATH system variable.

Creating UDT

You can create an assembly containing UDT using any programming language that supports .NET Framework version 2.00 and higher.

There is an example of a UDT implementation within SDAC demos. You can find it in the UDT demo directory of the SDAC General demo. The sample UDT is called Square. It is implemented with Microsoft Visual Studio 2005. There are both sources and the binary assembly in the Square directory.

Using UDT

UDT can be used only in Win32 application.

In order to use a UDT in SQL Server, first of all you should register the UDT. This can be done by executing CREATE ASSEMBLY command. After the assembly was registered, it is necessary to create a new type from the registered assembly using the CREATE TYPE command. Now you can use the name of the registered type in SQL commands like CREATE TABLE.

In order to provide native access to the UDT fields in your application, the client requirements should be fulfilled. Also make sure that TMSConnection is setup to use SQL Native Client as a provider ( TMSConnection.Options.Provider should be equal to prNativeClient). If all settings are applied correctly, UDT fields are described as mapped to the TMSUDTField class. Otherwise, UDT fields are mapped to TVarBytesField. You can access specific properties of UDT using the AsUDT property of TMSUDTField. It may look like the code below (this code is taken from the UDT demo project included in the SDAC General demo):

var
  Square: variant;
begin
  MSQuery.Edit;
  Square := (MSQuery.FieldByName('c_square') as TMSUDTField).AsUDT;
  Square.BaseX := StrToInt(edBaseX.Text);
  Square.BaseY := StrToInt(edBaseY.Text);
  Square.Side := StrToInt(edSide.Text);
  MSQuery.Post;

BaseX, BaseY, and Length are properties of the Square class.