Browse DevX
Sign up for e-mail newsletters from DevX


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

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

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.

Thiru Thangarathinam works at Intel Corporation in Chandler, Arizona. He's a Microsoft MVP who specializes in architecting, designing, and developing distributed enterprise-class applications using .NET-related technologies. He is the author of the books "Professional ASP.NET 2.0 XML" and "Professional ASP.NET 2.0 Databases" from Wrox press and has coauthored a number of books on .NET-related technologies. He is a frequent contributor to leading technology-related online publications.
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