he integration of the common language runtime (CLR) into Microsoft SQL Server2005 gives developers much more flexibility than ever before. This new feature lets you author stored procedures, triggers, and user defined functions and types using managed languages such as VB.NET or C#. The managed-code approach provides a number of benefits such as increased productivity, significant performance gains, and the ability to leverage the features of .NET Code Access Security to prevent assembles from performing certain operations. This article shows how to use the new CLR integration feature to create user defined types (UDTs) in SQL Server using a managed language and use them from ADO.NET.

UDTs and the .NET CLR
A user-defined type is a custom collection of data values and methods. By default, SQL Server recognizes a large number of individual data types, but it’s often useful to group these into a custom type with specific capabilities. Like other type structures in .NET, your SQL Server UDTs can have methods that you can call directly on the database server or on the client. User-defined data types are by far the most complex of all the database object types you can implement with the CLR. UDTs are not simple methods or types that have only predefined methods and attributes, but rather a very flexible way of extending the type system in SQL Server 2005.

In previous versions of SQL Server, database programmers were limited to using Transact-SQL when creating server side objects such as user defined types, stored procedures, triggers, user defined functions and so on. But the integration of SQL Server with .NET CLR opens up a whole new avenue of opportunities. Here are the important characteristics of UDTs. The following list summarizes some of the important characteristics of UDTs.

• UDTs behave just like any other data type on the server. You can create tables with UDT columns, use UDTs as variables to stored procedures, pass them as parameters, and so on. You can define UDTs using any .NET language.
• They expose properties and methods that can be invoked from T-SQL on the SQL Server.
• On the client side, you can use a UDT as you would any other object. After referencing the assembly containing the UDT class, you can use that class from within your data access layer ADO.NET code.

Requirements for Managed-Code UDTs
You implement UDTs in SQL Server 2005 by designing a public class that has several well-known methods, properties, and attributes. The following code shows a skeleton definition of a UDT class with the methods and attributes that you must specify as part of the UDT implementation.

   Namespace UDTNamespace         Public class UDTClass      Implements INullable            Public Overrides Function _          ToString() As String         '--Add your code here       End Function          Public Shared Function Parse _         (ByVal data as SqlString) _         As UDTClass         '---Add your code here       End Function          Public ReadOnly Property IsNull() _         as Boolean _         Implements INullable.IsNull         Get           '---Add your code here         End Get       End Property          Public Shared ReadOnly Property _         Null as UDTClass          Get           '--Add your code here         End Get       End Property        End Class   End Namespace

The SqlUserDefinedType attribute in the preceding code is a marker showing that the UDTClass implements a UDT. The ToString and Parse methods implement the string representation. Finally, the IsNull and Null properties implement the null representation. A class that implements a UDT must:

• Be a public class that has its SqlUserDefinedType and Serializable attributes set to appropriate values.
• Override the ToString method and return a string representation of the UDT.
• Have a public Parse method that interprets the string representation and returns an instance of the UDT type.
• Implement the IsNull property of the INullable interface.
• Implement the public static Null property to produce a null instance.

Here are the steps to create a UDT using a managed language such as C# or VB.NET.

• Create a .NET class and implement the functionality of the UDT within that class.
• Compile the class to produce a .NET assembly.
• Register that assembly in SQL Server using the Create Assembly statement.
• Create UDT definitions using the Create Type statement. As part of this, you also associate the UDT with the actual class in the assembly.

After you complete these steps, you can use the UDT just like any other pre-defined type in SQL Server.

Build a .NET UDT Class
To begin, create a new SQL Server Project named UDTExample using Visual Studio 2005. While creating the project, you also need to specify the connection string to the database using the connection string dialog box. This example uses a database called Test to demonstrate UDTs.

Create a VB.NET structure named ZipCode containing the code shown in Listing 1. The ZIP (Zone Improvement Plan) code is a two-part, 9-digit code (e.g. 85225-2248) that not only marks a specific geographical region but also provides information on the delivery sector, and delivery segments in the U.S. The combined five and four digit code is often referred to as ZIP+4, but for simplicity, this article will refer to the ZIP+4 format as “ZipCode.” The sample code uses the ZipCode UDT shown in Listing 1 in a table to hold ZipCode information.

Note that the ZipCode structure shown above is decorated with the Serializable attribute, which tells .NET that this UDT can be serialized. The Format property value in the SqlUserDefinedType attribute determines the actual serialization format (Format.Native in this case). The format property is the only required property for this attribute, and can take any of the following three values.

• Format.SerializedDataWithMetadata. This is simply standard .NET serialization. It’s the most flexible of the serialization formats because .NET handles all serialization automatically for many different data types, including strings and reference types.
• Format.Native. This is the fastest of the serialization formats because it uses native SQL Server binary serialization. However you can use this format only if the public properties of the structure are fixed length value-type data types.
• Format.UserDefined. This format has the flexibility of the SerializedDataWithMetadata format but without the negative performance considerations. It even has the potential to perform as well as the Native type. The only downside of this format is that you have to implement the serialization yourself.

After the initial attribute declarations, the code then declares the required Parse and ToString methods. After that, it exposes two public properties named FirstPart and SecondPart, which are used to store the first and second parts of a zip code, respectively.

Building the Assembly
Now that you’ve created the VB.NET ZipCode structure, compile the solution by selecting Build?>Build UDTExample from the menu. This will build the project and create an assembly, which you can then register with SQL Server by selecting Build?>Deploy UDTExample from the menu. When you deploy the assembly, Visual Studio not only registers the assembly with SQL Server but also creates the UDT in SQL Server. Once the UDT is created, you can now start using it in a table, which will be the topic of discussion in the next section.

Using the UDT in a SQL Server Table Column
Using Visual Studio, create a new table named ZipCodes in the Test database through the Server Explorer. In the New Table window, create two columns named ID and ZipCode. For the first column, specify the data type as integer and for the second column specify the data type as ZipCode by selecting dbo.ZipCode from the data type dropdown list.

Accessing the UDT from ADO.NET
Now create a Windows Forms application named UDTClientExample in Visual Studio 2005 to test the UDT that you created earlier. From within this new Windows application, you will

• Save values into the ZipCodes table using a SqlCommand object.
• Read values from the table using a SqlDataReader object.

Because you need to reference the UDT from the client application, add a reference to the UDTExample assembly using the Project?>Add Reference menu option. Rename the default form file to SaveUDTDemo.vb. After that, add two text boxes (named txtNumber and txtZipCode) and a command button (named btnInvoke) to the form. Add the code shown in Listing 2 to the Click event of the command button.

Figure 1. Contents of the ZipCodes Table: The figure shows the contents of the ZipCodes table as displayed in the Server Explorer.

Listing 2 first assigns the connection string to a local variable. Then it creates a new SqlConnection object using that connection string. Next, it creates a SqlCommand object by invoking the SqlConnection.CreateCommand method. The command takes two SqlParameter objects. Note that the code sets the data type of the ZipCode parameter to SqlDbType.Udt and sets the UdtTypeName property to Test.dbo.ZipCode. Finally, it sets the FirstPart and SecondPart properties of the ZipCode object before assigning it to the Value property of the SqlParameter object.

Run the application and enter values for the textboxes. Now, when you click on the command button, the code not only saves the values into the database but also displays a message box indicating that the values have been saved into the database. Figure 1 shows how these values are stored in SQL Server.

Reading UDT Columns into a Table
For the purposes of this example, add a new Windows Form named RetrieveUDTDemo.vb to the project. Add a command button (named btnInvoke) and a ListBox (named lstItems). Modify the code of the button’s Click event to look like the code shown below.

   Private Sub btnInvoke_Click( _     ByVal sender As System.Object, _     ByVal e As System.EventArgs) _     Handles btnInvoke.Click     Dim ConnectionString As String = _         "server=(local)SqlExpress;" & _       "Integrated Security=True;" & _       "Database=Test;"     Using connection As New _       SqlConnection(ConnectionString)       connection.Open()       Dim cmd As New SqlCommand _         ("SELECT * FROM dbo.ZipCodes", _         connection)       Dim reader As SqlDataReader       reader = cmd.ExecuteReader       While reader.Read()         'Retrieve the value of the          'Primary Key column         Dim id As Integer = _           reader.GetInt32(0)         'Retrieve the value of the UDT         Dim zip As ZipCode = _           CType(reader(1), ZipCode)         'Print values         lstItems.Items.Add("ID="+ _           id.ToString() & _           "   : ZipCode(1)=" & _           zip.FirstPart.ToString() & _           "   : ZipCode(2)=" +  & _           zip.SecondPart.ToString())       End While       reader.Close()     End Using   End Sub

Figure 2. Retrieving UDT Values: The figure shows the sample RetrieveUDTDemo form with data retrieved from the ZipCodes displayed in a ListBox.

As you can see, the code shown above is similar to that in Listing 2, except that it uses a SqlDataReader object to read values from the ZipCodes table. The code uses the SqlCommand.ExecuteReader method to execute the select query. After that, it loops through all the returned rows, displaying them one at a time in the lstItems ListBox. The example reads the UDT value into a local ZipCode object simply by specifying the column index; however, you can also use the GetSqlValue or GetValue method to retrieve the UDT column value. The code concatenates the retrieved values and adds the resulting string to the ListBox by calling the Add method. Run the application and click on the Invoke button and you will see the output shown in Figure 2.

Because UDTs can be managed by the CLR, you can represent a wide variety of data structures to create types that were not possible with previous versions of SQL Server. With UDTs you also gain precise control over the data your type accepts, resulting in better data management and type safety.