Part 2 - XML and UDT Integration with SQL Server 2005
Note: the code described in this article
is based on the Beta 2 release of the .NET Framework v 2.0. All the examples
can be downloaded from our Web site at http://www.daveandal.net/articles/datasetv2/. You'll need to edit the web.config file to
specify a database server running SQL Server 2005 with the AdvnetureWorks
sample database installed. Other notes regarding setting up the samples are
included within the article.
In the previous part of this article, we looked in detail at the major changes to the DataSet class in version 2.0 of the .NET Framework. In this part, we pick
up the story to look at another two features that provide new opportunities for
developers to work with data in a more structured and efficient way when using
a DataSet and accessing a SQL Server 2005 database server. Note that these
techniques apply only to data accessed in SQL Server 2005, and
not to any other database server.
SQL Server 2005 (formerly code-named "Yukon") supports many new and
exciting features. However, as far as the ADO.NET developer is concerned when
using a DataSet to persist and manipulate data, the two fundamental changes are the
provision of direct support within the database for XML documents and XML
schemas, and support for user-defined data types (UDTs). So, the topics we'll
be discussing here are:
- Using the XML data type in a DataSet.
- How the DataSet supports user-defined types.
The XML Data Type in a DataSet
SQL Server 2005 can be used to store and manipulate XML
documents and schemas. XML schemas registered with SQL Server 2005 are stored
in a schema repository within the database. These schemas can then be used to
validate XML documents, shred them into the constituent data types, and store
the values internally in an efficient way that provides high performance access
to the XML content.
Alternatively, you can store XML documents in a SQL Server
2005 table using the new xml column type, but without registering a schema first. In this case, because it
cannot determine the structure or data types within the document, SQL Server
2005 stores them as text within a column. So, irrespective of whether you
provide a schema for the XML documents, they can be stored in a table in columns
of the new type called xml.
But to be of any use, there must be ways of accessing this
XML data from the client or middle tier. ADO.NET provides access to this column
type through new features in version 2.0. You can access xml columns using
a SqlDataReader or a DataSet.
This article is concerned with DataSet class, and so in general we'll confine our
discussions to this.
The DataAdapter.ReturnProviderSpecificTypes Property
Before getting involved in code examples, the important
feature to grasp first is that - by default - a DataAdapter does not populate a DataSet with
columns of type xml.
When it encounters an xml column in the rowset coming from the database (as returned by the SelectCommand that
the DataAdapter is using), it creates a column in the DataSet of type String, and places the incoming XML
content into that column as a String value. This maintains backward compatibility with
existing code.
To generate an xml column in the DataSet, you set the ReturnProviderSpecificTypes property of the DataAdapter to true before calling the Fill method. In the Beta 2 release of the .NET Framework, this forces the DataSet to create
a column of type System.Data.SqlTypes.SqlString (setting this property to true in Beta 1 causes an error in the Fill method).
Reading an XML Column in a DataSet
The first example allows you to read a
single row from the Sales.Store table in the SQL Server 2005 AdventureWorks sample database, which
contains an xml column named Demographics. The code in the example
ASP.NET page starts by importing the required namespaces - we're using the SqlClient classes to access the database, and an XmlDocument and XPathNavigator to read the contents of the Demographics column and
so we need the corresponding namespaces. We'll also, later in the code, be
casting the returned value to a System.Data.SqlTypes.SqlString instance,
and so we need the corresponding namespace for this as well:
<%@Import Namespace="System.Data"
%>
<%@Import Namespace="System.Data.SqlClient"
%>
<%@Import Namespace="System.Data.SqlTypes"
%>
<%@Import
Namespace="System.Xml" %>
<%@Import Namespace="System.Xml.XPath"
%>
The HTML and Controls in the Example Page
The page contains a RadioButtonList where you can select the type of access to the XML column that you
want to attempt - with the ReturnProviderSpecificTypes property
either true or false, and through an XPathDocument or an XmlReader. There
is also a Label to display the results, and a GridView control to which we'll bind the returned DataSet to show
the values in the single row that is returned:
<form runat="server">
<asp:RadioButtonList runat="server"
ID="optSpecificType" AutoPostBack="True">
<asp:ListItem Value="0" Selected="True"
Text="DataAdapter.ReturnProviderSpecificTypes
= False (default)" />
<asp:ListItem Value="1"
Text="DataAdapter.ReturnProviderSpecificTypes
= True" />
<asp:ListItem Value="2"
Text="DataAdapter.ReturnProviderSpecificTypes
= True using an XmlReader" />
</asp:RadioButtonList>
<asp:Label runat="server"
ID="lblResult" enableviewstate="False" />
<asp:GridView runat="server"
ID="gridResult" enableviewstate="False"
AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="CustomerID"
HeaderText="CustomerID" />
<asp:BoundField DataField="Name"
HeaderText="Name" />
<asp:BoundField DataField="Demographics"
HeaderText="Demographics"
HeaderStyle-HorizontalAlign="Left"
/>
</Columns>
</asp:GridView>
</form>
The Page_Load Event Handler
In the code itself, we start by declaring
the connection string (obtained from web.config) and a SQL
statement to extract one row. We also declare a StringBuilder that will
store the results as the code executes, so that we can display them in the Label control afterwards. Then, in the Page_Load event, we
examine the current selection in the RadioButtonList and
execute one of three routines declared elsewhere in the page:
' connection and query details
Dim connect As String _
= ConfigurationManager.ConnectionStrings("adworks").ConnectionString
Dim xml_select As String = "SELECT *
FROM Sales.Store WHERE CustomerID = 7"
' StringBuilder to hold output for display
Dim builder As New StringBuilder()
Sub Page_Load()
Select Case optSpecificType.SelectedValue
Case 0 : FillDataSetDefault()
Case 1 : FillDataSetSpecificTypes()
Case 2 : ReadWithXmlReader()
End Select
End Sub
Filling a DataSet When ReturnProviderSpecificTypes is
False
The first of the three routines to fill a DataSet does so with the default value (false) for the ReturnProviderSpecificTypes property of the DataAdapter. It creates
a DataAdapter, a DataSet, and declares a variable to hold a DataTable instance.
Then, within a Try..Catch construct, it fills the DataSet with a single
table named "Test", and gets a reference to this table:
Sub FillDataSetDefault()
Dim da As New SqlDataAdapter(xml_select,
connect)
Dim ds As New DataSet()
Dim dt As DataTable = Nothing
Try
' fill DataSet with rows using SQL
statement
da.Fill(ds, "Test")
dt = ds.Tables(0)
...
Displaying the DataType and the Value as a String
The next step is to display details of the xml-type column named Demographics. The data type can be
obtained from the DataType property of the column, and in this case it is a String. So the value (as a String) can be extracted
directly from the column - just as you would if the source column in the
database was a char, varchar, nchar or nvarchar column.
We're inserting extra spaces into the resulting string to force it to wrap in
the page and make it easier to see the contents:
...
' display data type and contents of
"Demographics" column
' putting spaces between elements to
make result easier to read
builder.Append(String.Format("Demographics
column DataType = '{0}'<p />", _
dt.Columns("Demographics").DataType.ToString()))
Dim sXMLContent As String = dt.Rows(0)("Demographics")
builder.Append(String.Format("Demographics
column contents " _
& "as a String:<br
/>{0}<p />", _
Server.HtmlEncode(sXMLContent.Replace("><",
"> <"))))
...
Figure 1 shows the results that this part
of the code generates. You can see that the ReturnProviderSpecificTypes property is false in this case, and that the DataType of the column is returned as System.String.
Underneath this is the content of the column as a String value, with the extra spaces inserted into it:
Figure 1 - The DataType
and contents of an xml column when ReturnProviderSpecificTypes is false
Extracting the XML Content using an XmlDocument
In many cases you'll want to use the XML in
a column as though it was an XML document, and not as a String. However, it is only available as a String instance, so you
must load it into a suitable XML document object. We chose the XmlDocument, and the following code shows how the page creates a new XmlDocument instance and loads the XML into it using the LoadXml method (which takes String). Then we can
create an XPathNavigator over the XmlDocument to display the XML contents. For this, a separate recursive routine
(described shortly) is used to iterate through the document extracting the name
and value of each node:
...
' load String value into an XmlDocument
and display nodes
Dim xd As New XmlDocument()
xd.LoadXml(dt.Rows(0)("Demographics"))
' create an XPathNavigator to access
the content
Dim nav As XPathNavigator = xd.CreateNavigator()
' iterate through the nodes in the XML
document displaying details
IterateChildNodes(nav, "Nodes
found in XML document using an XPathNavigator:")
...
The remaining code binds the table in the DataSet to the GridView control on the page to display the single row it contains, catches
any exception that might be raised, and displays the contents of the StringBuilder in the Label control on the page:
...
' display rows in GridView
gridResult.DataSource = dt
gridResult.DataBind()
Catch ex As Exception
builder.Append("<p />*
ERROR: " + ex.Message)
End Try
' display results in Label
lblResult.Text = builder.ToString()
End Sub
Iterating Through the XML Document with an XPathNavigator
The previous code uses a routine named IterateChildNodes to display the name and value of the nodes in the XML document. For
completeness, though it is not really part of the current discussion, this
routine is listed below. It displays details of the current node (this will be
the root node when it is first called), and any attributes it may contain. Then
it looks for any child nodes for the current node, and if found iterates
through them. Finally it iterates through any sibling nodes (i.e. any nodes at
the same level in the document as the current node), recursively calling itself
on each one of these:
Private Sub IterateChildNodes(ByVal xn As XPathNavigator,
ByVal caption As String)
builder.Append(caption)
If xn.NodeType = XPathNodeType.Element
Then
builder.Append(String.Format("Element
'{0}' = '{1}'<br />", xn.Name, xn.Value))
If xn.HasAttributes Then
xn.MoveToFirstAttribute()
builder.Append(String.Format("-
Attribute '{0}' = {1}<br />", _
xn.Name,
xn.Value))
While xn.MoveToNextAttribute()
builder.Append(String.Format("-
Attribute '{0}' = {1}<br />", _
xn.Name,
xn.Value))
End While
xn.MoveToParent()
End If
End If
If xn.HasChildren Then
xn.MoveToFirstChild()
IterateChildNodes(xn, String.Empty)
xn.MoveToParent()
End If
While xn.MoveToNext()
IterateChildNodes(xn, String.Empty)
End While
End Sub
Figure 2 shows the results form this
section of the code. You can see the individual nodes that are found in the XML
document. Notice that the xmlns attribute does not actually show up
as an attribute of the root node because it is a namespace declaration rather
than a true attribute of this element. After the list of elements you can see
how the GridView control displays the row contents. Much of the content of the Demographics column wraps off-screen in this figure, but it's clear that it is
treated as a String (which, of course, it is). In fact, and for this reason, this part
of the example will work fine with AutoGenerateColumns="True" on the GridView control.
Figure 2 - The
XML elements in the Demographics column, and the GridView displaying the row
Filling a DataSet When ReturnProviderSpecificTypes is True
If you select the second option in the RadioButtonList, the example page executes the routine named FillDataSetSpecificTypes, which loads the same row into a DataSet but this time
after setting the ReturnProviderSpecificTypes property of the DataAdapter to true.
Other than this, the code to fill the DataSet is the same as
in the FillDataSetDefault routine described earlier:
' specify that provider-specific types are
required in DataSet
da.ReturnProviderSpecificTypes = True
Displaying the DataType and the Value as a String
Next, the code uses the same approach as
the previous routine to display the data type and the contents of the Demographics column. The only difference is that we can take advantage this time
of the fact that the value is returned as a SqlString instance by
casting it to this type and then querying the Value property - which
returns a String:
...
' display data type and contents of
"Demographics" column
' putting spaces between elements to make
result easier to read
builder.Append(String.Format("Demographics
column DataType = '{0}'<p />", _
dt.Columns("Demographics").DataType.ToString()))
Dim sXMLContent As String = CType(dt.Rows(0)("Demographics"),
SqlString).Value
builder.Append(String.Format("Demographics
column contents " _
& " from SqlString.Value:<br
/>{0}<p />", _
Server.HtmlEncode(sXMLContent.Replace("><",
"> <"))))
...
Remember that referencing a column in a DataRow by name or index returns an Object type, so we have
to cast it to the correct type before we can access the class-specific Value property. However, there is little to actually gain from this when
we can get the column value as a String directly, or just
call ToString on the SqlString instance. It's possible that the column type will be exposed as a SqlXml type (as in the SqlDataReader) in
future, which is much more useful - as you'll see in the third routine in this
example.
Extracting and Displaying the XML Content
The remaining code in the FillDataSetSpecificTypes routine we're looking at here displays the value of the Demographics column as XML elements, and populates
the GridView control at the foot of the page. One point is that we can’t pass a SqlString instance
to the LoadXml method of the XmlDocument,
and so we have to call ToString on it first. Then the elements within the XML document are displayed using the
same IterateChildNodes routine as before:
' load String value into an XmlDocument and
display nodes
Dim xd As New XmlDocument()
xd.LoadXml(dt.Rows(0)("Demographics").ToString())
' create an XPathNavigator to access the
content
' iterate through the nodes in the XML
document displaying details
' ... all as in previous routine ...
Finally, the code binds the table in the DataSet to the GridView control. You can see that the code contains a note about the
problem with AutoGenerateColumns in Beta 2 when the DataRow contains a SqlString instance:
...
' display rows in GridView
'
*************************************************************
' following code fails when trying to bind
row to the GridView
' "The data source for GridView did
not have any properties or
' attributes from which to to generate
columns" if using a
' GridView with AutoGenerateColumns="True".
This example uses
' a GridView with BoundColumns specified
for required columns
'
*************************************************************
gridResult.DataSource = dt
gridResult.DataBind()
...
Figure 3 shows the results when the ReturnProviderSpecificTypes property is true, and you can see that the DataType of the column is returned as System.Data.SqlTypes.SqlString. Underneath (as before) is the content of the column as a String value with the extra spaces inserted into it, the list of elements
found by the XPathNavigator, and the GridView displaying the single row.
Figure 3 - The DataType
and contents of an xml column when ReturnProviderSpecificTypes is true
Using an XmlReader When ReturnProviderSpecificTypes is True
Ideally the DataSet would expose a SqlXml instance for a column of type xml (as discussed
earlier), and this may be the case in future (post-Beta 2) releases. The third
routine in the example page attempts to use the same techniques as you can use
in a SqlDataReader to access the contents of an XML column -
where it is returned as a SqlXml instance.
The ReadWithXmlReader routine starts by
creating a DataSet with the ReturnProviderSpecificTypes property of the DataAdapter set to true, as in the FillDataSetSpecificTypes routine we just
looked at. It also displays the DataType of the column, and then extracts the value as a String using the ToString method.
The next section of code then attempts to cast the value in
the Demographics column to a SqlXml type, as you can do in a SqlDataReader.
The SqlXml class exposes the CreateReader method, which returns an XmlReader.
The code then iterates through the nodes in the XmlReader displaying their names and
values. You can see the notes in the code that describe the problem, however:
...
' *******************************************************
' following code fails when trying to
create a SqlXml
' instance over the 'xml' column because it
cannot be
' converted from a SqlString to a SqlXml
type.
'
*******************************************************
' get contents of Demographics column into SqlXml
variable
Dim xml As SqlXml = CType(dt.Rows(0)("Demographics"),
SqlXml)
' create an XmlTextReader to access the
content
Dim xr As XmlReader = CType(xml.CreateReader(),
XmlReader)
' iterate through the nodes in the XML
document displaying details
builder.Append("Nodes found in XML
document using an XmlReader:<br />")
While xr.Read()
Select Case xr.NodeType
Case XmlNodeType.Element
builder.Append(String.Format("Element
'{0}'<br />", xr.Name))
Case XmlNodeType.Text
builder.Append(String.Format("Text
value '{0}'<br />", xr.Value))
End Select
If xr.HasAttributes Then
While xr.MoveToNextAttribute()
builder.Append(String.Format("-
Attribute value '{0}'<br />", xr.Value))
End While
End If
End While
' close XmlTextReader
xr.Close()
...
Figure 4 shows the results of selecting
this option. The data type of the column is returned as SqlString, as before, and the value is displayed as a String. However, an exception is raised when trying to convert the SqlString instance into a SqlXml instance.
Figure 4 - An
exception is raised when trying to convert a SqlString into a SqlXml instance
Updating an XML Column with a DataSet
The next example demonstrates how you can
use the Fill and Update methods of the DataSet to read and
update an xml-typed column in a SQL Server 2005 database. The code in the
page declares the connection string for the database, and two SQL statements.
The first extracts the CustomerID (the key for the table) and the Demographics columns for the row with CustomerID = 7.
The second is a SQL UPDATE statement that updates the value of the Demographics column in the same row:
' connection and query details
Dim connect As String _
= ConfigurationManager.ConnectionStrings("adworks").ConnectionString
Dim xml_select As String = "SELECT CustomerID,
Demographics FROM Sales.Store " _
& "WHERE CustomerID
= 7"
Dim xml_update As String = "UPDATE Sales.Store
SET Demographics = @NewValue " _
& "WHERE CustomerID
= 7"
Fetching and Displaying the Data
The page contains a multi-line TextBox that displays the current content
of the Demographics column from one row of the AdventureWorks database, a Label to display errors and messages, and a Button to update
the column contents. The Page_Load event handler
calls a separate routine named GetDataSet (declared
elsewhere in the page) and then extracts the value of the Demographics column and inserts it into the TextBox on the page.
We haven’t listed the GetDataSet routine here - it uses exactly the same techniques as the previous
example. It sets the ReturnProviderSpecificTypes property of the DataAdapter to true, and so the value in the Demographics column of the DataSet table is the serialized
equivalent of a SqlString instance. This means that the content can be retrieved (as in the previous
example) by casting it to a SqlString type and referencing the Value property. Notice that we only perform these actions when this is the first time
the page is loaded, and not if it is a postback (i.e. not when the Update button is
clicked):
Sub Page_Load()
If Not Page.IsPostBack Then
' create DataSet containing XML column
Dim ds As DataSet = GetDataSet()
' display contents of
"Demographics" column in TextBox
' note that this requires ValidateRequest="False"
in
' the @Page directive as it contains
XML markup
txtXML.Text = CType(ds.Tables(0).Rows(0)("Demographics"),
_
System.Data.SqlTypes.SqlString).Value
End If
End Sub
Updating the XML Column Value
The Update button on this
page causes a postback to the server, but there is no event handler specified
for it (i.e. no OnClick attribute). However, we've attached an event handler to the OnTextChanged event of the multi-line TextBox within the page:
<asp:TextBox runat="server"
ID="txtXML" TextMode="MultiLine"
Rows="8"
Columns="120" OnTextChanged="UpdateXMLColumn" />
This event will be raised if the contents
of the TextBox have changed since the page was last generated, and it will execute
the routine named UpdateXMLColumn.
It's at this point we want to update the database with the new
XML content from the TextBox. The UpdateXMLColumn routine is listed below, and you can see that the first step is to get the DataSet containing
the Demographics XML column by calling the GetDataSet routine. Then the code grabs the XML string from the Text property of the TextBox and
inserts it into the Demographics column - replacing the existing value. This works even though the column is of
type SqlString,
as you can assign a String to a SqlString instance:
Sub UpdateXMLColumn(ByVal sender As Object,
ByVal e As EventArgs)
' create DataSet containing XML column
Dim ds As DataSet = GetDataSet()
' update column in DataSet with new XML
value from TextBox
ds.Tables(0).Rows(0)("Demographics")
= txtXML.Text
...
Creating a Parameter to Reference the XML Column
To use the DataSet to update the
database, we must create a DataAdapter using the
same SQL statement as we did when we filled the DataSet so that the
table and column mappings are the same. We could, of course, use the same DataAdapter as we did to fill the DataSet, but here we
demonstrate how you can create a new one and set up the appropriate UpdateCommand.
The first stage is to create a parameter that will pass the
values in the XML column of the DataSet to the
placeholder declared in the SQL statement (or to a stored procedure if you are
using this approach instead of a SQL statement). The SQL statement in this
example has a placeholder named @NewValue for the new
XML content. However, the interesting part is selecting the correct data type
for the parameter. As the value from the TextBox is a String, one of the character-based types is the obvious solution - we
chose SqlDbType.NVarChar with a length enough to hold XML strings we might encounter in this
table.
Note that, even though there is an Xml type available in the SqlDbType enumeration,
this will not work here - it reports an error that the correct interfaces are
not implemented. If the column type in the DataSet was a SqlXml instance instead of a SqlString instance, you
would use SqlDbType.Xml here instead (as is the case with the SqlDataReader). You also have to specify
the SourceColumn property of the parameter so that it knows which column in the table contains
the value to pass to this parameter. You can specify the SourceColumn using
the fourth parameter of the SqlParameter constructor, as shown here:
...
' create parameter to hold the updated
XML String
' note: cannot use SqlDbType.Xml as it
does not
' implement the IConvertable interface
Dim param As New SqlParameter("@NewValue",
SqlDbType.NVarChar, 2048, "Demographics")
...
Creating the Update Command and Updating the Database
The final section of code, shown below,
creates a new DataAdapter, Connection and Command, adds the parameter we just created to the Command, and assigns this Command to the UpdateCommand property of the DataAdapter in the usual
way. Then is calls the Update method, and displays the number of
rows that were updated:
...
' create DataAdapter and push changed row
back into database
Dim da As New SqlDataAdapter(xml_select,
connect)
Dim con As New SqlConnection(connect)
Dim cmd As New SqlCommand(xml_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 5 shows the results of running this
example, and updating the value of one of the elements in the XML document. You
can see that one row was updated, and if you close and re-open the page you'll
see that the new value is stored in the database.
Figure 5 - Updating
an XML document in SQL Server 2005 using a DataSet
One interesting point to note is that you
cannot insert an XML document that is not well-formed, or which does not comply
with the schema registered in SQL Server 2005 for the target column. For
example, Figure 6 shows the result of trying to insert a character string into
an element that - according to the schema - is a numeric type. SQL Server 2005
reports an XML validation error, and the update fails.
Figure 6 - Attempting
to insert invalid content into an XML document in SQL Server 2005
