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
methods. After that, it exposes two public properties named FirstPart
, 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 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.|
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
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.