User-defined Types in a DataSet
SQL Server 2005 hosts the .NET Common Language Runtime
(CLR), and so can execute managed code written in any CLR-compatible language
within the context of the database server. One area where this is useful is
when taking advantage of the new support in SQL Server 2005 for storing user-defined
types (UDTs) within columns of a table.
A second new column data type supported by SQL Server 2005
allows instances of an object that is a user-defined data type to be stored in
the database tables. The UDT is written as a class that follows some simple
rules, such as implementing the INullable interface (by exposing a public static method
named Null),
being marked as Serializable with a SqlUserDefinedTypeAttribute,
exposing a public static Parse method and a public ToString method, and having a default constructor that takes no parameters. The values
that the class will store are exposed as properties, and the class is then
compiled and registered with SQL Server 2005 where it can used in the
declaration of tables that will contain instances of this class.
Again, to be useful, this column type must be accessible
from the client or middle tier in ADO.NET. The assembly must be available on
the client or middle tier where it will be used so that ADO.NET can recognize
the class (the UDT). Thus, when a SqlDataReader or a DataSet is created
that includes a column containing serialized objects of this class, ADO.NET can
instantiate them on the client or middle tier and access the values that are
stored as properties. As this article is concerned with DataSet class, we'll confine our
discussions to this (as we did when we looked at the xml data type earlier).
The Sample Point UDT Type
The following examples use a UDT named Point, written in Visual Basic and compiled into an assembly called PointStuff.dll. The source project for the UDT is included with the sample files
(as PointStuff.zip). The UDT is a class that implements the INullable interface, and also exposes two properties named X and Y that accept numeric values. There is
also a ToString method that returns the two values separated by a colon, as "X:Y", and a Parse method that
accepts a String of the same format and uses the two values to populate the X and Y properties of
the UDT. You can check out the source code in the file Point.vb (within the PointStuff.zip archive).
Reading a UDT Column in a DataSet
The first UDT example shows how you can
extract a UDT from a column in a DataSet, and the access
it as an instance of that particular type. As we'll need to instantiate the Point class in our code, the page contains an Import directive that specifies the assembly name - the assembly is in the bin folder of the application:
<%@ Import Namespace="PointStuff"
%>
The code within the page starts by
declaring the connection string (stored in web.config) and a SQL
statement that extracts rows from the Points table - which
contain a column of type int and a column named Pnt that contains instances of the Point class UDT:
' connection and query details
Dim connect As String _
= ConfigurationManager.ConnectionStrings("adworks").ConnectionString
Dim xml_select As String = "SELECT ID,
Pnt FROM Points"
Populating the DataSet
In the Page_Load event, after
declaring a StringBuilder to hold the results, the code creates a new DataAdapter, sets the ReturnProviderSpecificTypes property to true so that the
values will be stored in the DataSet as instances of the type in the database table,
and then fills the DataSet:
Sub Page_Load()
' StringBuilder to hold messages for
display
Dim builder As New StringBuilder()
Try
' create a DataSet and fill with rows
using SQL statement
Dim da As New SqlDataAdapter(xml_select,
connect)
' specify that provider-specific types
are required in DataSet
da.ReturnProviderSpecificTypes = True
Dim ds As New DataSet()
da.Fill(ds, "Test")
Dim dt As DataTable = ds.Tables(0)
...
Displaying the DataType and Content
Now the code can display the value of the DataType property of the Pnt column, and then
extract the value from the column as a String. The column
contains a serialized representation of the original Point class instance (as stored in the database table), and calling ToString on the column returns the equivalent of the ToString method of the Point class - a String of the form "X:Y".
...
' display data type and contents of
"Pnt" column
builder.Append(String.Format("'Pnt'
column DataType = '{0}'", _
dt.Columns("Pnt").DataType.ToString()))
builder.Append(String.Format("'Pnt'
column contents using ToString: '{0}'", _
dt.Rows(0)("Pnt").ToString()))
...
The next step is to extract the contents of the Pnt column as an
instance of the Point class - this simply requires a cast to the appropriate type. Because the PointStuff DLL
that implements the Point class is stored in the bin folder of our application, and imported into the page using an @Import directive
at the top of the page, it is available within the code. One instantiated, we
can query and display the values of the X and Y properties:
' get contents of Pnt column as Point
instance
Dim pnt As Point = CType(dt.Rows(0)("Pnt"),
Point)
' display the Point column value using
its properties
builder.Append("Property values of
Point instance: ")
builder.Append(String.Format("Point.X
= {0}, ", pnt.X.ToString()))
builder.Append(String.Format("Point.Y
= {0}", pnt.Y.ToString()))
Catch ex As Exception
builder.Append("<p />*
ERROR: " + ex.Message)
End Try
' display results in Label
output.Text &= builder.ToString()
End Sub
Figure 7 shows the results from this code.
You can see that the DataType of the column is our UDT type - PointStuff.Point - and
the ToString method returns the values of the X and Y properties concatenated with a colon. The individual property values, obtained
after instantiating the Point class instance, are shown below
this.
Figure 7 - Accessing
a UDT in a table in a DataSet as a String and as an instance of the original
type
Updating a UDT Column with a DataSet
So, reading a UDT is easy. What about
updating one from a DataSet? In fact, this is also easy, and very similar to the techniques
used with an XML column in the earlier example. The next example page contains
two TextBox controls that will display the values of the Point class's X and Y properties. There is also a Button to initiate a
postback and call theUpdateUDTColumn routine that
will push the values in the TextBox controls back
into the database table. Finally, there is a Label to display errors
and messages. This is the declaration of the HTML and server controls in the
example page:
<form runat="server">
Point UDT property values:
X = <asp:TextBox runat="server"
ID="txtPointX" Columns="2" />
Y = <asp:TextBox runat="server"
ID="txtPointY" Columns="2" />
<p />
<asp:Button ID="Button1" runat="server"
Text="Update" OnClick="UpdateUDTColumn" />
<p />
<asp:Label runat="server"
ID="lblResult" enableviewstate="False" />
</form>
The code in the page starts with the
customary declaration of the connection string, and a SQL statement that
extracts a single row from the Points table in the
database. There is also an UPDATE statement that
will push the changed Point instance back into the database. It
contains a replaceable parameter named @NewPoint:
' connection and query details
Dim connect As String _
= ConfigurationManager.ConnectionStrings("adworks").ConnectionString
Dim udt_select As String = "SELECT ID,
Pnt FROM Points WHERE ID = 2"
Dim udt_update As String = "UPDATE
Points SET Pnt = @NewPoint WHERE ID = 2"
The Page_Load Event Handler
When the page first loads, but not after a
postback, we want to display the current values of the Point instance in the single row in the DataSet. The page
contains a routine named GetDataSet that is identical to the one
used in the previous XML example, except that it uses the SQL statement
declared above that extracts a row from the Points table.
So we can call this routine to get the DataSet we want, and then cast the content of the Pnt column to a Point instance - just as we did in the
previous example that reads a UDT from a DataSet. Once we have the Point instance, we can
extract the values of the X and Y properties, and
display them in the two TextBox controls on the page:
Sub Page_Load()
If Not Page.IsPostBack Then
' create DataSet containing a UDT
column
Dim ds As DataSet = GetDataSet()
' get contents of Pnt column as a Point
instance
Dim pnt As Point
= CType(ds.Tables(0).Rows(0)("Pnt"), Point)
' display UDT
property values in TextBoxes
txtPointX.Text = pnt.X.ToString()
txtPointY.Text = pnt.Y.ToString()
End If
End Sub
Updating the Point Instance in the DataSet
When the Update button is clicked
to initiate a postback, the code calls the GetDataSet routine again
to create a DataSet containing the same single row with its Pnt column that contains a Point instance. The next
step is to create a new Point instance to replace the one in the DataSet table. You can’t just reference the existing column contents and
update the UDT properties, even if you cast the value in the column it to a Point instance. You must create a new instance of the UDT and insert it
into the column.
Next, the code extracts the values from the
two TextBox controls, and verifies that they are numeric by parsing them into
integer (Int32) values before setting the properties of the new Point instance. If there is an error (i.e. they are not numeric values),
a message is displayed and execution of the routine ends. However, if the
values can be successfully converted to integers, the next line of code replaces
the contents of the Pnt column in the DataSet table with the new Point instance:
Sub UpdateUDTColumn(ByVal sender As Object,
ByVal e As EventArgs)
' create the DataSet containing the UDT
column
Dim ds As DataSet = GetDataSet()
' create a new Point instance and set
property values
Dim updatePoint As New Point()
Try
updatePoint.X = Int32.Parse(txtPointX.Text)
updatePoint.Y = Int32.Parse(txtPointY.Text)
Catch
lblResult.Text &= "* ERROR:
Cannot convert X and Y values into numbers"
Return
End Try
' update column in DataSet with new UDT
ds.Tables(0).Rows(0)("Pnt") = updatePoint
...
Creating the UDT Parameter
Now that the DataSet table row
contains the new value (the new Point instance), we can
use the Update method of the DataAdapter to push the changed row back
into the database in the usual way. The first step is to create the parameter
that is required for the SQL statement (or for a stored procedure if this is
the way you implement the update). The parameter type in this case is SqlDbType.Udt, and
the SourceColumn is the column named Pnt.
However, when using UDTs, there is one other step required
to generate the correct type of parameter. You have to tell the database what
type (i.e. what class) the UDT in your parameter actually represents. This is
done by setting the UdtTypeName property of the Parameter class - a new property added to the class to enable support for UDTs. The Command passes
this property of the parameter to the database as it executes an UPDATE, INSERT or DELETE statement.
This means that you have to set the value of this property to the
fully-qualified class name of the UDT as it is stored in the database.
In this case, the fully-qualified name is AdventureWorks.dbo.Point, because the UDT
class is stored in the AdventureWorks database, using the dbo schema, and
with the class name Point:
...
' create parameter to hold the updated
Point instance
' specify that it's a UDT for parameter
data type
Dim param As New SqlParameter("@NewPoint",
SqlDbType.Udt)
param.SourceColumn = "Pnt"
' must also specify type as stored in SQL
Server database
param.UdtTypeName = "AdventureWorks.dbo.Point"
...
Updating the Database Table
Having created a suitable parameter, all
that remains is to create the DataAdapter, Connection and Command we need, add the parameter to the Command, and call the Update method of the DataAdapter - just as we did in the
previous example that updated an XML column. The number of rows updated is
displayed in the Label control on the page, which also shows any error that occurs:
...
' create DataAdapter and push changed row
back into database
Dim da As New SqlDataAdapter(udt_select,
connect)
Dim con As New SqlConnection(connect)
Dim cmd As New SqlCommand(udt_update,
con)
cmd.Parameters.Add(param)
da.UpdateCommand = cmd
Try
lblResult.Text &= "Updated
" _
& da.Update(ds,
"TestTable").ToString _
& " row(s).<br
/>"
Catch ex As Exception
lblResult.Text = "* ERROR: "
+ ex.Message
End Try
End Sub
Figure 8 - Updating
the Point UDT in a row in the Points table
Figure 8 shows the result of this example.
You can see that we changed the values of the X and Y properties of the Point UDT, and this one row was updated in the database. If you enter a
non-numeric value for one or both of the properties, an error is generated and
displayed instead - as you can see in Figure 9.
Figure 9 - The
error when non-numeric values are entered for the Point properties
Summary
This and the preceding article have looked
in depth at the way that the DataSet class has been
enhanced and extended in version 2.0 of ADO.NET to provide better performance,
new features, and to make it easier to use. In the previous article we look at loading
a DataSet and the new LoadOption enumeration;
using stand-alone DataTable instances; streaming
data into and out of DataSet tables with a DataTableReader; performing batched updates from a DataSet; and the
performance and scalability improvements available.
In this article we concentrate instead on
two features that are designed to support the extended set of data types
provided by SQL Server 2005. You can now use SQL Server 2005 as a
fully-featured XML schema repository and XML data store. XML is stored in SQL
Server 2005 in columns of type xml, and ADO.NET has
been extended to allow you to read and interact with this type of column in the
middle-tier or on the client. This article describes how you can do this using
a DataSet to hold rows containing XML types, and how you can update xml columns using a DataSet.
The second major focus of this article is
on the use of user-defined types (UDTs), which are also now supported in SQL
Server 2005. UDTs are classes written in managed code that can be registered
with SQL Server 2005, and the used to create columns that hold instances of
that type. Again, ADO.NET has been extended so that you can access columns of
this type from the middle-tier or client. We show how you can read UDTs into a DataSet, access then, and push changes back into the database.
