Updating SQL Server with the XmlAdapter Class

Updating SQL Server with the XmlAdapter Class

Developers are already familiar with the Fill method that the ADO.NET DataAdapter uses to retrieve relational data from a DBMS data source. In principle, the Fill method exposed by the XmlAdapter class is identical, except that rather than filling a DataSet it fills an XPathDocument2. There are, however, differences in the way it is used. The query language we use is XQuery, and the three-part mapping that shapes the XML as an XML view is also required. In the examples that follow we use the mapping files that we examined earlier in this chapter for the Customers, Orders, and related Products tables from the Northwind database, using them to retrieve data from and update data to SQL Server.

The map:view Command and XQuery

In Chapter 8, we'll look in more detail at the XQuery language. For now, we can just consider this as an XPath. In order to identify which XML view to use when the query is issued to SQL Server, the map:view extension function has been added to the XQuery. This takes as a parameter the name of the XML view or XSD schema.

Since this is an extension function, it must be identified by the namespace: http://schemas.microsoft.com/xml/2002/09/28/mappingfunctions. For example, the XQuery shown below uses the nwind XML view to return all the customers with their orders as a well-formed XML document that can be displayed in Internet Explorer:

declare namespace
map='http://schemas.microsoft.com/xml/2002/09/28/mappingfunctions'
map:view('nwind')/Customers

Using the Fill Method with an XPathDocument2

To use the Fill method, you just specify the connection string and an XQuery statement when creating the XmlAdapter. The code is shown in Listing 7.5.

Using the Fill Method with an XPathDocument2

Dim myConn As New SqlConnection("your-connection-string")
myConn.Open()

' create an XQueryProcessor to compile the XQuery statement
Dim xq As New XQueryProcessor()
xq.XmlViewSchemaDictionary.Add("nwind", New XmlTextReader("msd-file"))

xq.Compile("declare namespace map='http://schemas.microsoft.com/xml" _
   & "/2002/09/28/mappingfunctions' map:view('nwind')/Customers")

Dim datasource As New XmlDataSourceResolver()
datasource.Add("Northwind", myConn)

' instantiate an XmlAdapter
Dim adapter As New XmlAdapter()

' assign the XmlResolver to the adapter's DataSources property
' to resolve the SQL data source when the query is executed
adapter.DataSources = datasource

' instantiate an XPathDocument2 object to fill
Dim doc As New XPathDocument2()

' execute Fill method of XmlAdapter to populate XPathDocument2
' XmlCommand property of the XQueryProcessor is used to specify
' the command representing the compiled XQuery
adapter.Fill(doc, xq.XmlCommand)

' instantiate an XPathNavigator2 object to navigate the results
Dim nav As XPathNavigator2 = doc.CreateXPathNavigator2()

' an XmlDocument is returned to use in the <asp:xml> control
xmldoc.LoadXml(nav.OuterXml)

myConn.Close()   ' close connection

To display the results, we've included an ASP.NET <asp:xml> control on the page, so the final line of code shown above returns an XmlDocument that can be used to populate the control and display the results (see Figure).

How It Works

First a SqlConnection is opened to the Northwind database. Next an instance of an XQueryProcessor class is created and used to compile the XQuery that will be executed. We will be discussing this class in detail in the next chapter, but it is analogous to a SqlCommand class in that it lets you issue a SQL statement to a database.

Next the Add method of the XmlViewSchemaDictionary is used to load the nwind.msd mapping file, which in turn loads the nwind.rsd and nwind.xsd schema files and compiles these ready to issue queries against SQL Server. The XQuery map:view('nwind')/Customers, which references the nwind XML view, is then compiled. This improves performance, allows reuse, and occurs because it is composed with the XML view. Note that it's important to ensure that the names used in the map:view function are the same as the names added to the XmlViewSchemaDictionary; otherwise the query will fail at runtime with an error because it cannot find the data source.

The XmlDataSourceResolver is then used to associate a connection with a data source, in this case the Northwind data source that represents the nwind.rsd schema. This is the name of the data source in the MSD file, as shown below:

<m:DataSource Name="Northwind" Direction="Source" Type="SQL Server">

This must match the name of the data source that is added to the XmlDataSourceResolver:

datasource.Add("Northwind", myConn)

The XmlDataSourceResolver is then set on the XmlAdapter via the DataSources property, and we are ready to issue a query to return results as XML from relational tables. The Fill method is called with an XmlCommand parameter (the XQuery to execute) and the XPathDocument2 to fill with the results.

On return, we have successfully retrieved relational data and built this into our chosen XML structure, via the XML view. The XPathNavigator2 is used to retrieve the XML from the document and display the results. In the screenshot in Figure, you can see that it returns all the customers and all their orders from the map:view('nwind')/Customers query.

Using Parameters with the Fill Method

You may have noticed that the Fill method can take an XmlQueryArgument List class, which allows parameters to be passed to the query. At the time of this writing, the W3C XQuery language specification does not have a syntax defined for passing external parameters to a query. It will be implemented once the syntax is agreed upon and becomes a recommendation.

Using the Update Method with an XPathDocument2

The Update method of the XmlAdapter makes it easy to push changes made to the XPathDocument2 class back into the SQL Server data source, using an XML view schema to define the mapping:

XmlAdapter.Update(doc, schema)

The Update method automatically generates the SQL updates, based on the XML view schema. This is analogous to the CommandBuilder in ADO.NET, but the update always happens behind the scenes, and you don't actually have to do anything to make it work. OK, so you have to define the XML to relational mapping via the XML view, but since this is explicit it means that it is straightforward for the XmlAdapter to generate all the required SQL statements to translate the XML changes into table and row updates.

As before, let's look at the code for an example that uses the Update method, then see the results and walk through how it works. The code for this example is shown in Listing 7.6. The first part is the same as the example in Listing 7.5, creating an XPathDocument2 and then filling it using the query map:view('nwind')/Customers. We've omitted this repeated code for clarity. Then the code updates the Country column for customers in Germany to Nigeria, using the Fill and Update methods of the XmlAdapter.

Using the Update Method with an XPathDocument2

...
...  create and fill an XPathDocument2 here as in Listing 7.5
...

' create XPathEditor object to change orders for Germany to Nigeria
Dim xmleditor As XPathEditor = doc.CreateXPathEditor()
Dim iter As IEnumerable = _
    xmleditor.Select("/Customers/Customer[@Country='Germany']")
For Each editor As XPathEditor In iter
  editor.MoveToAttribute("Country", "")
  editor.SetValue("Nigeria")
Next

' execute the Update method to update SQL Server.
' same MappingSchema can be used as for the Fill method when
' the destination of the updates is the same data source
Dim schema As New MappingSchema("msd-file")
adapter.Update(doc, schema)

The screenshots in Figures 7.2 and 7.3 on the next page show the "before" and "after" views.

How It Works

The section of code we omitted in Listing 7.6 is identical to the previous Fill example. After this, the code updates the XPathDocument2 by first using an XPathEditor to find all the customers that have the value Germany for the Country attribute and then changing the value of this attribute to Nigeria with the SetValue method.

The Update method requires a mapping schema to perform the update, but this can be the same one as used for the Fill method (the schema of the XML document and the data source have not changed). We recompile the nwind.msd file and provide this along with the changed document to the Update method that updates the database.

Using the Update Method with an XPathChangeNavigator

We can also use an XPathChangeNavigator to perform the update. This has two advantages. First, we can be selective about exactly what is updated, perhaps by choosing to update only deleted values or changes made to a certain set of nodes. This provides a very flexible approach.

The second advantage is that, like the XPathNavigator, it's possible to implement the XPathChangeNavigator over any store of data that has the ability to track changes and push these changes back into the data source. For example, you may choose to implement an ArrayList that tracks changes. By building an XPathChangeNavigator implementation for the ArrayList, it could be used for updates to SQL Server. The code in Listing 7.7 creates an XPathChangeNavigator and uses it to update SQL Server. In this case, all the document changes are updated because the XPathChange Navigator was created and located at the root of the document.

Using the Update Method with an XPathChangeNavigator

Dim changenav As XPathChangeNavigator _
              = doc.CreateXPathChangeNavigator()

' write all the changes to the console
IEnumerable iter = changenav.SelectChanges()
For Each navigator As XPathChangeNavigator in iter
  Console.WriteLine(navigator.InnerXml)
Next
' perform the update with the XPathChangeNavigator
adapter.Update(changenav, schema)

Handling Update Errors

The XmlAdapter can fail to perform updates from an XML document in the same way that an ADO.NET DataAdapter can fail to perform updates from a DataSet, for example, through violation of a primary key constraint by trying to insert a duplicate entry. These failures are reported via the On UpdateError event handler, which is registered with the XmlAdapter before calling the Update method. The code in Visual Basic .NET is:

AddHandler adapter.OnUpdateError, _
          New UpdateEventHandler(AddressOf MyHandler)

Or in C#:

adapter.OnUpdateError += new UpdateEventHandler(MyHandler);

The event handler for UpdateError is a callback method with a typical .NET signature. The event handler used in the next example is shown in Listing 7.8.

The Event Handler for the UpdateError Event

' the XmlAdapter.OnUpdateError event handler
Sub OnUpdateError(src As Object, args As XmlUpdateEventArgs)
  lblError.Text &= "Update Error  : " _
                & args.InnerException.Source & "<p />"
  lblError.Text &= "Error Details : " _
                & args.InnerException.Message & "<p />"

  ' iterate the set of ErrorItems that failed the update
  For Each navigator As XPathChangeNavigator In args.ErrorItems
    ' write name and value of each element or attribute in error
    lblError.Text &= "Item in error: " & navigator.LocalName _
                  & ": " & navigator.InnerXml & "<p />"
  Next navigator
End Sub

An Example of Handling the UpdateError Event

The code in Listing 7.9 uses an XPathEditor to add a customer that already exists to the XML document, in this case, the customer with ID ALFKI. When the Update method is called, SQL statements are generated to insert the new customer. The duplicate value for the CustomerID column causes the ADO.NET SqlClient class to fail the insert with a primary key constraint.

This error is propagated back to the XmlAdapter, which calls the Update Error event handler attached to the adapter. As well as reporting the exception message, the code in the error handler displays details of the items that failed the update; these details are reported via an XPathChange Navigator. The precise failure location within the XML document can be identified.

The complete code for the example is shown in Listing 7.9, with the exception of the event handler itself, which appears in Listing 7.8.

Demonstrating the UpdateError Event

...
...  create and fill an XPathDocument2 here as in Listing 7.5
...

' create XPathEditor object and use to add illegal new element
' that will fail to update because of key restriction in database
Dim xmleditor As XPathEditor = doc.CreateXPathEditor()
Dim iter As IEnumerable = _
    xmleditor.Select("/Customers/Customer[@Country='Germany']")
Dim editor As XPathEditor = doc.CreateXPathEditor()
editor.MoveToFirstChild()
editor.CreateFirstChild("<Customer name='Alfreds Futterkiste' " _
                       & "CustomerID='ALFKI' />")

' register event handler for the XmlAdapter.OnUpdateError event
AddHandler adapter.OnUpdateError, AddressOf OnUpdateError

' execute the Update method to update SQL Server.
' same MappingSchema can used as for the Fill method when
' the destination of the updates is the same data source
Dim schema As New MappingSchema("msd-file")
adapter.Update(doc, schema)

Executing the example in Listing 7.9 results in an error during the update. The error details are reported from the OnUpdateError event handler, as shown in Figure.

Continuing after an Update Error

By default, the first update error that occurs terminates the whole update and rolls back any changes made to the DBMS. Alternatively, the Continue UpdateOnError property can be set to True, which causes the Update method to continue with the rest of the updates. Each subsequent failure is reported via the UpdateError event handler.

This technique is useful if there have been a large number of changes to the document that are independent of one another. For example, when five new customers have been added and fifty orders have been updated, you can choose in the XPathChangeNavigator to apply all the changes at once, just the changes for new customers, or just the updates to the orders, and report failures for each to the user.

The code in Listing 7.10 shows two new customers being added to an XPathDocument2. The first has CustomerID='ALFKI' and, as we saw in the previous example, will result in a primary key constraint violation. However, in this example a second addition, with CustomerID='ACME', succeeds because this customer is a new entry (i.e., there is no existing record with that CustomerID in the Customers table of the Northwind database).

Using the ContinueUpdateOnError Property

' create XPathEditor and add 2 records, 1 duplicate and 1 new
Dim editor As XPathEditor = doc.CreateXPathEditor()
editor.MoveToFirstChild()

' add a duplicate record
editor.CreateFirstChild("<Customer name='Alfreds Futterkiste' " _
                       & "CustomerID='ALFKI' />")

' add a new record
editor.CreateFirstChild("<Customer name='Acme Corporation' " _
                       & "CustomerID='ACME' />")

' set the ContinueUpdateOnError property to True
adapter.ContinueUpdateOnError = True

' execute the Update. OnUpdateError event handler will report an
' error for the first insert. The update process will continue
' after error is reported and the second record will be inserted.
Dim schema As New MappingSchema("msd-file")
adapter.Update(doc, schema)

Tips and Tricks When Working with the XmlAdapter

This section provides some useful tips when working with the XmlAdapter and SQL Server.

Use SetValue for Updating Values

When updating the value of an element, use the SetValue method of the XPathEditor on a Text node, rather than deleting the element and inserting a new one. This is because the XmlAdapter understands changes that are based on operations, rather than on snapshots of the data. Hence a deletion and an insertion are treated as two separate operations, and in the Technology Preview release there is no support for multiple operations on the same row or column in the same update.

Call CheckValidity Before Calling Update

XML views are not used to validate an XPathDocument2 when the Update method is called on the XmlAdapter. If you need to validate the XPath Document2 before updating SQL Server, call the CheckValidity method first.

Provide an OnUpdateError Handler

Always provide an OnUpdateError event handler and register it with the XmlAdapter before calling the Update method so that it will report update errors. Typically set the ContinueUpdateOnError property to True so that multiple update errors are reported.

Note the Limitations in the Technology Preview Release

The XmlAdapter does not support updates to multiple SQL Server databases.

Tips and Tricks When Working with XML Views

This section provides some useful tips when working with XML views and mappings.

Use a Top-Level Node in the Mapping

Generally it is desirable to have a top-level node in the mapping in order to generate a well-formed XML document. Although this is not a requirement (e.g., you can choose to have just a list of customers), it is often useful so that you can display and work with the XML. Unlike the XmlDocument, the XPathDocument2 class can load XML fragments and does not require a top-level document element.

Avoid Constructing Nodes in the Query Unless They Fit the Mapping

XQuery enables you to construct nodes in the XML result that is returned. If you construct nodes that are not in the XML view, the XmlAdapter does not know how to map the XPathDocument2 back to the SQL Server DBMS. For example, given the Customers XML view in the previous section, the following XQuery is valid and can be used to wrap the returned Customer nodes:

declare namespace map='http://schemas.microsoft.com/xml/2002/09/28/mappingfunctions'
<Customers>
  {map:view('nwind')/Customers/Customer[@CustomerID='ALFKI']}
</Customers>

However, the following queries would then fail on update because neither the <root> nor <Orders> elements are mapped to the XML view:

declare namespace map='http://schemas.microsoft.com/xml/2002/09/28/mappingfunctions'
<root>
  {map:view('nwind')/Customers/Customer[@City='London']}
</root>

declare namespace map='http://schemas.microsoft.com/xml/2002/09/28/mappingfunctions'
<Orders>
  {map:view('nwind')/Customers/Customer[@CustomerID='ALFKI']/Order} </Orders>

Map Only What Is Needed and Create Multiple Views If Necessary

You need to map only what you want to query over. This applies to each file format of the three-part mapping. You have to create an RSD file only for the tables you want to expose. Your XSD can contain additional elements and attributes in the XSD that are not mapped, but they are there for validation purposes only. Any elements or attributes that are not mapped are simply ignored during the query and update.

For example, if we rework part of the Customer XML view we used earlier to add a new Description element, but we choose not to map the Description element, this does not result in an error. Let's say the XML schema defines Customer as:

<xsd:element name="Customer">
  <xsd:complexType>
    <xsd:sequence>
      <xsd:element name="CustomerID" xsd:type="xsd:string" />
      <xsd:element name="CName" xsd:type="xsd:string" />
      <xsd:element name="Description" xsd:type="xsd:string" />
    </xsd:sequence>
  </xsd:complexType>
</xsd:element>

And the element-centric MSD mapping is defined as:

<m:Map SourceVariable="Customer" TargetSelect="/Customer" />
<m:Map SourceVariable="Customer" TargetSelect="/Customer/CustomerID">
  <m:FieldMap SourceField="CustomerID" TargetSelect="text()" />
</m:Map>
<m:Map SourceVariable="Customer" TargetSelect="/Customer/CName">
  <m:FieldMap SourceField="CustomerName" TargetSelect="text()" />
</m:Map>
<m:Map SourceVariable="Customer" TargetSelect="/Customer" />

Then if the new Customer shown below is inserted, the Description element is discarded and does not result in an error.

<Customer>
  <CustomerID>AW</CustomerID >
  <CName>Addison-Wesley</CName>
  <Description>Book Publisher</Description>
</Customer>

Finally, you can define multiple XML views and MSD mappings for any given RSD file, depending on how you want to shape the XML. We will see this in Chapter 8 when we use XQuery to retrieve XML through a number of different XML views.