User-Defined Types

Another important new feature in SQL Server 2005 that is enabled by the integration of the .NET CLR is the ability to create true user-defined types (UDTs). Using UDTs, you can extend the raw types provided by SQL Server and add data types that are specialized to your application or environment.

In the following example you'll see how to create a UDT that represents a gender code: either M for male or F for female. While you could store this data in a standard one-byte character field, using a UDT ensures that the field will accept only these two values with no additional need for triggers, constraints, or other data validation techniques.

To create a UDT using Visual Studio 2005, select the New | Project option, give your project a name, and click OK to create the project. For this project I used the name of Gender for the new UDT. After naming the project and clicking OK, I filled out the New Database Reference dialog using the required connection values to deploy the project to the appropriate SQL Server system and database. Next, I used the Project | Add User-Defined Type option to display the Add New Item dialog that you can see in Figure 3-11.

Figure 3-11: Creating a .NET SQL Server UDT

Table 3-1: Required UDT Methods

Select User-Defined Type from the list of SQL Server templates. Enter the name that you want to assign to the class and then click Open to have Visual Studio generate a starter project file for the UDT. The starter project file implements the four methods that SQL Server 2005 requires for all UDTs. These methods are needed to fulfill the SQL Server UDT contract requirements—it's up to you to add the code to make the UDT perform meaningful actions. The four required UDT methods are listed in Table 3-1. You can see the completed Gender class that is used to implement a UDT for M (male) and F (female) codes in this listing:

Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.IO


 
   _

  
    _
Public Structure Gender
      Implements INullable, IBinarySerialize

      Public Sub Read(ByVal r As BinaryReader) _
            Implements IBinarySerialize.Read
            m_value = r.ReadString.ToString()
End Sub

Public Overrides Function ToString() As String
     If m_value.IsNull = False Then
          Return m_value.Value
     Else
         Return Nothing
     End If
End Function

Public ReadOnly Property IsNull() As Boolean _
     Implements INullable.IsNull
         Get
               If m_value.IsNull = True Then
                      Return True
              Else
                      Return False
              End If
         End Get
End Property

Public Shared ReadOnly Property Null() As Gender
     Get
           Dim h As Gender = New Gender
           h.m_Null = True
           Return h
      End Get
End Property

Public Shared Function Parse(ByVal s As SqlString) As Gender
       If s.IsNull Then
           Return Null
       End If

       Dim u As Gender = New Gender
       u.Value = s
       Return u 
End Function

      ' Create a Value Property
      Public Property Value() As SqlString
            Get
                   Return m_value
            End Get

            Set(ByVal value As SqlString)
                   If (value = "M" Or value = "F") Then
                       m_value = value
                  Else
                       Throw New ArgumentException _
                          ("Gender data type must be M or F")
                  End If
             End Set
        End Property

        ' Private members
        Private m_Null As Boolean
        Private m_value As SqlString
End Structure
  
 

To create a UDT, the code must adhere to certain conventions. The class's attributes must be serializable, the class must implement the INullable interface, and the class name must be set to the name of the UDT. You can optionally add the IComparable interface. In this example, Gender is the class name. Near the bottom of the listing you can see where a private string variable named m_value is declared to hold the value of the data type.

Like the other CLR database objects, the Attribute plays an important part in the construction of the CLR UDT. The SQL Server UDT Attribute accepts the property values shown in Table 3-2.

The first thing to notice in the code is the use of the INullable and IBinarySerialize interfaces. The INullable interface is required for all UDTs. The IBinarySerialize interface is required for UDTs that use the Format.UserDefined attribute. Because this example uses a String data type, the Format.UserDefined attribute is required, which means that this UDT also needs code to handle the serialization of the UDT. In practical terms, this means that the class must implement the IBinarySerialize Read and Write methods, which you can see in the following section of code.

At first it may seem a bit intimidating to use the IBinarySerialize interfaces, but as you can see in the Read and Write subroutines, it's actually pretty simple. The Read subroutine simply uses the ReadString method to assign a value to the UDT's m_value variable (which contains the UDT's value). Likewise, the Write subroutine uses the Write method to serialize the contents of the m_value variable.

Table 3-2: UDT Attribute Properties

The ToString method checks to see if the contents of the m_value variable are null. If so, then the string "null" is returned. Otherwise, the m_value's ToString method returns the string value of the contents.

The next section of code defines the IsNull property. This property's get method checks the contents of the m_value variable and returns the value of true if m_value is null. Otherwise, the get method returns the value of false. Next, you can see the Null method, which was generated by the template to fulfill the UDT's requirement for nullability.

The Parse method accepts a string argument, which it stores in the object's Value property. You can see the definition for the Value property a bit lower down in the code. The Parse method must be declared as static, or if you're using VB.NET, it must be a Shared property.

The Value property is specific to this implementation. In this example, the Value property is used to store and retrieve the value of the UDT. It's also responsible for editing the allowable values. In the set method, you can see that only the values of M or F are permitted. Attempting to use any other values causes an exception to be thrown that informs the caller that the "Gender data type must be M or F".

Deploying the UDT

Very much like a CLR stored procedure or function, the UDT is compiled into a DLL after the code is completed. That DLL is then imported as a SQL Server assembly using the CREATE ASSEMBLY and CREATE TYPE statements or by simply using the Visual Studio 2005 Deploy option. You can see the T-SQL code to manually create the CLR UDT in the following listing:

create assembly Gender
from 'C:tempGender.dll'
go

CREATE TYPE Gender
EXTERNAL NAME Gender.[Gender.Gender]
go

This listing assumes that gender.dll has been copied into the c:temp that's on the SQL Server system. One thing to notice in the CREATE TYPE statement is the class parameter. As in the earlier CLR examples, the first part of the External Name clause specifies the assembly that will be used. In the case of a UDT, the second part of the name identifies the namespace and class. In the Gender example, the Namespace was Gender and the UDT's class was also named Gender.

Using the UDT

Once the UDT is created, you can use it in T-SQL much like SQL Server's native data types. However, since UDTs contain methods and properties, there are differences. The following example shows how the Gender UDT can be used as a variable and how its Value property can be accessed:

DECLARE @mf Gender
SET @mf='N'
PRINT @mf.Value

In this listing the UDT variable is declared using the standard T-SQL DECLARE statement, and the SET statement is used to attempt to assign the value of N to the UDT's Value property. Because N isn't a valid value, the following error is generated:

.Net SqlClient Data Provider: Msg 6522, Level 16, State 1, Line 2
A CLR error occurred during execution of 'Gender':
System.ArgumentException: Gender data type must be M or F
at Gender.set_Value(SqlString value)

Just as UDTs can be used as variables, they can also be used to create columns. The following listing illustrates creating a table that uses the Gender UDT:

CREATE TABLE MyContacts
(ContactID int,
FirstName varchar(25),
LastName varchar(25),
MaleFemale Gender)

While creating columns with the UDT type is the same as when using a native data type, assigning values to the UDT is a bit different than the standard column assignment. Complex UDTs can contain multiple values. In that case you need to assign the values to the UDT's members. You can access the UDT's members by prefixing them with the (.) symbol. In this case, since the UDT uses a simple value, you can assign values to it exactly as you can any of the built-in data types. This example shows how to insert a row into the example MyContacts table that contains the Gender UDT:

INSERT INTO MyContacts VALUES(1, 'Michael', 'Otey', 'M')

To retrieve the contents of the UDT using the SELECT statement, you need to use the UDT.Member notation as shown here when referencing a UDT column:

SELECT ContactID, LastName, MaleFemale.Value FROM MyContacts

To see the UDTs that have been created for a database, you can query the sys.Types view as shown here:

SELECT * FROM sys.Types

The previous tip is from "Developing CLR database objects: 10 tips in 10 minutes," excerpted from Chapter 3 of the book "Microsoft SQL Server 2005: A Developer's Guide" written by Michael Otey and Denielle Otey, courtesy of McGraw-Hill Publishing.

Developing CLR database objects

 Home: Introduction
 Tip 1: CLR architecture
 Tip 2: CLR assemblies in SQL Server 2005
 Tip 3: Creating CLR database objects
 Tip 4: CLR stored procedures
 Tip 5: User-Defined Functions
 Tip 6: CLR triggers
 Tip 7: User-Defined Types
 Tip 8: CLR aggregates
 Tip 9: Debugging CLR database objects
 Tip 10: .NET database object security