Browse DevX
Sign up for e-mail newsletters from DevX


Breaking Type Barriers with UDTs in SQL Server 2005 : Page 2

Using SQL Server 2005 and the .NET 2.0 framework you can go beyond the standard data types recognized by SQL Server and create your own User Defined Types (UDTs) using any .NET Language—and then use them exactly the same way you use the built-in types.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date