Create datatypes for SQL Server 

We've been exploring the usefulness of one of the best features of the upcoming version of SQL Server 2005 codenamed 'Yukon'. We've seen how you can create UDIs (User Defined Functions), Stored Procedures and Aggregates, using your favorite .NET language within the SQL Server database engine itself. This month we conclude this mini-series with a look at creating and using your own custom data types for SQL Server 2005 using .NET.

Custom datatypes are useful when you want to store certain information as atomic objects instead of as individual fields. For instance, in an Employee database, the Address fields are usually stored as Address 1, Address 2, City, State, PIN and Country. This is because the database has no datatype object that can store all these fields together logically, since the datatypes in databases are always primitive types like numbers, dates and strings. 

To get around this limitation you can create UDTs (User Defined Types). This has always been possible, though, difficult to perform in SQL Server. With .NET 2.0 now built into the engine, this has now become almost trivial. Let's see what you need to do to get this working.

Open up VS.NET 2005 and create a new SQL Server Project. Now right click on the project and select Add>User-Defined Type. Give the new name as ContactInfo and press OK when prompted. VS creates a new template UDT with the given name and some properties already created for you. However, we will add a set of new properties to store certain contact information such as Address, City, Mobile and E-mail in it. The code below shows the complete set. This defines a new .NET class with the properties that we want in our datatype. Remember you can even add methods into the class if you wish.

Imports System

Imports System.Data.Sql

Imports System.Data.SqlTypes



_

_

Public Class ContactInfo

Implements INullable

The new type appears in the Data Type dropdown when adding a new field

Private m_isNull As Boolean

Private m_Address1 As String

Private m_Address2 As String

Private m_City As String

Private m_Mobile As String

Private m_Email As String



...

You can query on the individual properties of the type you created in SQL easily

Public Property Mobile() As String

Get

Return m_Mobile

End Get

Set(ByVal value As String)

m_Mobile = value

End Set

End Property



Public Property Email() As String

Get

Return m_Email

End Get

Set(ByVal value As String)

m_Email = value

End Set

End Property



End Class

Now compile the project to get the binary .NET DLL. Open up the SQL Server 2005 Management Studio, connect to the server you want and then open the database into which you wish to add the new datatype. Open a query window and enter the following.

CREATE TYPE ContactInfo EXTERNAL NAME SQLCLR.

When you execute the statement, the new datatype gets registered into the SQL Server database engine. You can now use this type as you would any other type. 

For instance, let's create a new table in both graphical and script mode. In the database you added the type, open the Tables folder and right click to select New Table. In this add a new column and click on the Data Type dropdown. At the end of the list you will see the datatype you just added. To add it using SQL statements, create a table using the following.

CREATE TABLE Contacts

(

ID INT NOT NULL,

sName VARCHAR(75) NOT NULL,

ciContact ContactInfo,

sCompany VARCHAR(50) NOT NULL

)

Now insert some values into the new table (and especially the field using the new datatype) like this.

DECLARE @ci ContactInfo

SET @ci.Address1 = 'MyAddr1'

SET @ci.Address2 = 'MyAddr2'

SET @ci.City = 'Gurgaon'

SET @ci.Mobile = '9810098100'

SET @ci.Email = 'vinod@enterpriseinfotech.com'

You can also query from the table and the field like this.

SELECT 

sName, 

ciContact.Address1, 

ciContact.Address2, 

ciContact.City, 

ciContact.Mobile, 

ciContact.Email 

FROM 

Contacts

As you can see, adding a new datatype that logically groups information is quite easy using .NET. Since the type now works within the SQL Server itself, it acts like any ordinary type and you can continue to use all the SQL functionality you are familiar with on this type as well.

We will cover some new features and XML integration in SQL Server 2005 in the coming issues.

Vinod Unny

Enterprise InfoTech