Questions That Should Be Asked More Frequently

Reading and Writing XML Data

First let's look at the different ways we can read and write XML data using the DataSet object.

The DataSet Object's XML Methods

The DataSet object has a series of methods that let you examine its contents as XML as well as load XML data into the DataSet. Let's take a look at these methods.

GetXml Method

The simplest of these XML methods is the GetXml method, which you can use to extract the contents of your DataSet into a string. The GetXml method is simple almost to a fault. It is not overloaded and accepts no parameters.

Figure 12-1 shows the contents of a DataSet in the Console window. The code that generates and displays this DataSet follows.

Figure 12-1

Using the GetXml method to view the contents of a DataSet as XML

Visual Basic .NET

Dim ds As New DataSet() FillMyDataSet(ds) Console.WriteLine(ds.GetXml) Public Sub FillMyDataSet(ByVal ds As DataSet)     Dim strConn, strSQL As String     strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _               "Initial Catalog=Northwind;Trusted_Connection=Yes;"     strSQL = "SELECT OrderID, CustomerID, OrderDate FROM Orders " & _              "WHERE CustomerID = 'GROSR'"     Dim daOrders, daDetails As OleDbDataAdapter     daOrders = New OleDbDataAdapter(strSQL, strConn)     strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " & _              "FROM [Order Details] WHERE OrderID IN (SELECT " & _              "OrderID FROM Orders WHERE CustomerID = 'GROSR')"     daDetails = New OleDbDataAdapter(strSQL, strConn)     daOrders.Fill(ds, "Orders")     daDetails.Fill(ds, "Order Details") End Sub

Visual C# .NET

DataSet ds = new DataSet(); FillMyDataSet(ds); Console.WriteLine(ds.GetXml()); static void FillMyDataSet(DataSet ds) {     string strConn, strSQL;     strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +               "Initial Catalog=Northwind;Trusted_Connection=Yes;";     strSQL = "SELECT OrderID, CustomerID, OrderDate FROM Orders " +              "WHERE CustomerID = 'GROSR'";     OleDbDataAdapter daOrders, daDetails;     daOrders = new OleDbDataAdapter(strSQL, strConn);     strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " +              "FROM [Order Details] WHERE OrderID IN (SELECT " +              "OrderID FROM Orders WHERE CustomerID = 'GROSR')";     daDetails = new OleDbDataAdapter(strSQL, strConn);     daOrders.Fill(ds, "Orders");     daDetails.Fill(ds, "Order Details"); }

WriteXml and ReadXml Methods

As I noted earlier, the GetXml method is rather limited. The DataSet object's WriteXml method is more robust. It is overloaded so that you can write the contents of your DataSet to a file or to an object that implements the Stream, TextWriter, or XmlWriter interfaces.

The WriteXml method also lets you specify values from the XmlWriteMode enumeration for added control over the output. You can use this enumeration to choose whether to include the schema information for the DataSet and whether to write the contents of the DataSet in diffgram format.

I'd rather examine the contents of an XML document in Microsoft Internet Explorer than in the Console window because Internet Explorer will format the data nicely. The code snippet that follows shows how to use the WriteXml method to write the contents of a DataSet (including its schema) to a file and display that file in Internet Explorer, as shown in Figure 12-2. The code snippet relies on the FillMyDataSet procedure from the previous code snippet. It also requires a reference to the Microsoft Internet Controls library, which is available on the COM tab of the Add Reference dialog box.

Figure 12-2

Viewing the DataSet and its schema in Internet Explorer

Visual Basic .NET

Dim ds As New DataSet() FillMyDataSet(ds) Dim strPathToXml As String = "C:\MyData.XML" ds.WriteXml(strPathToXml, XmlWriteMode.WriteSchema) ShowXmlInIE(strPathToXml) Public Sub ShowXmlInIE(ByVal strPathToXml As String)     Dim ie As New SHDocVw.InternetExplorer()     ie.Navigate(strPathToXml)     ie.Visible = True End Sub

Visual C# .NET

DataSet ds = new DataSet(); FillMyDataSet(ds); string strPathToXml = "C:\\MyData.XML"; ds.WriteXml(strPathToXml, XmlWriteMode.WriteSchema); ShowXmlInIE(strPathToXml); static void ShowXmlInIE(string strPathToXml) {     SHDocVw.InternetExplorer ie = new SHDocVw.InternetExplorerClass();     object objEmpty = Type.Missing;     ie.Navigate(strPathToXml, ref objEmpty, ref objEmpty,                 ref objEmpty, ref objEmpty);     ie.Visible = true; }

	The InternetExplorer class's Navigate method has many optional parameters. The C# language does not support optional parameters with COM interop calls. You can supply Type.Missing for the parameters that you want to omit.

The DataSet object has an overloaded ReadXml method that you can use to load data into your DataSet. The ReadXml method is basically the inverse of the WriteXml method. It can read XML data from a file or from an object that implements the Stream, TextReader, or XmlReader interfaces. You can also control how the method reads the contents of the XML data by supplying values from the XmlReadMode enumeration.

WriteXmlSchema, ReadXmlSchema, and InferXmlSchema Methods

The DataSet object also exposes ReadXmlSchema and WriteXmlSchema methods that allow you to read and write just the schema information for your DataSet. Each method supports working with files and objects that implement the Stream, TextReader, or XmlReader interface.

The ReadXmlSchema method can load schema information from an XML schema document using the XML Schema Definition (XSD) or XML Data Reduced (XDR) standard. It can also read an inline schema from an XML document.

The DataSet object also exposes an InferXmlSchema method, which works just like the ReadXmlSchema method except that InferXmlSchema has a second parameter. You can supply an array of strings in the second parameter to tell ADO.NET which namespaces you want to ignore in the XML document.

Inferring Schemas

In previous chapters, I've supplied metadata or schema information in code to provide better performance than when you generate this information programmatically at run time. The same holds true for inferring XML schemas, and the ReadXml method is a prime example.

Say that you use the ReadXml method to load data into a DataSet and that neither the XML document nor the DataSet has any schema information. You can't add rows of data to the DataSet if it has no schema information. The ReadXml method must first scan the entire XML document to add the appropriate schema information to the DataSet before it adds the contents of the document to the DataSet. The larger the XML document, the greater the performance penalty incurred by inferring the schema from the document.

This approach can lead to another problem: You might not get the schema you want. ADO.NET will assume that all data types are strings and won't create any constraints. Why? Imagine that your XML document contains a list of contacts and addresses (XML tags omitted) in the following format:

<MailingLabel>   <First_Name>Randal</First_Name>   <Last_Name>Stephens</Last_Name>   <Address>123 Main St.</Address>   <City>Sometown</City>   <Region>MA</Region>   <PostalCode>01234</PostalCode> </MailingLabel>

Let's say that the contents of the document represent a small sampling of the actual data in your database. In other entries in your database, the contact might have a second address line or an address outside of the United States with a postal code in a different format. You must remember that if you ask ADO.NET to infer a schema from the contents of an XML document that contains no schema information, it will do its best to build an appropriate schema. You should therefore supply a schema whenever possible, either by using straight code or by supplying an XML document that contains the desired schema information. In this way, you can improve the performance of your application and avoid headaches.

ADO.NET Properties That Affect the Schema of Your XML Document

You can format an XML document in more than one way. As the saying goes, the devil is in the details. Look at the XML documents in Figures 12-3 and 12-4. They contain the same information, but they differ in their schema.

If you try to load data into a DataSet that already contains schema information, ADO.NET will ignore data that does not match up with the DataSet object's schema. It's therefore important that you match your DataSet object's schema to that of the data you want to load.

Figure 12-3

An XML document that contains the order history for a customer

Figure 12-4

An XML document that contains the same order history in a different format

You can use properties of the objects within your DataSet to control the format that ADO.NET uses to read and write XML documents for your DataSet. In fact, I used the same DataSet object to generate both Figure 12-3 and Figure 12-4. I simply changed the values of these properties.

Names of Elements and Attributes

Notice that the names of the elements in the two documents are different. ADO.NET uses the name property for each object as the name of the corresponding element or attribute. The DataSet object's DataSetName property controls the name of the root element. Similarly, the DataTable object's TableName property and the DataColumn object's ColumnName property control the names that ADO.NET will use for the elements and attributes that correspond to those tables and columns.

Choosing Elements or Attributes

The two documents also differ in how they represent the order and detail data. Figure 12-3 uses elements to store this information, and Figure 12-4 uses attributes.

You can use the DataColumn object's ColumnMapping property to control this behavior. By default, the ColumnMapping property is set to Element. You can set the property to Attribute if you want to store the column's data in an attribute rather than an element. You can also set the ColumnMapping property to Hidden if you don't want the contents of the column to appear in your XML document.

Nesting Relational Data

In Figure 12-3, all of the line items for the orders appear at the end of the document, whereas in Figure 12-4, the line items appear within the order. You can control whether the relational data is nested by setting the Nested property of the DataRelation object. By default, the property is set to False, which results in the format shown in Figure 12-3. Setting the Nested property to True will cause ADO.NET to nest the relational data as shown in Figure 12-4.

Namespaces and Prefixes

The DataSet, DataTable, and DataColumn objects all expose Namespace and Prefix properties. Both properties contain strings and are empty by default. The DataSet used in Figure 12-4 has each object's Namespace property set to http://www.microsoft.com/MyNamespace and the Prefix property set to MyNs.

Caching Changes and XML Documents

Shortly after ADO 2.1 added the ability to read and write Recordset objects in XML format, I spoke to a number of developers who used the Recordset object as a middleman, pulling data from the database and storing the data as XML. They would then modify the contents of the XML document and expect that they could just read the data back into an ADO Recordset, which would somehow be able to submit the changes back to the database. It didn't work. Here's why.

In Chapter 6 and Chapter 10, I discussed how the ADO.NET DataRow object stores the current and original contents of the row so that you can submit the changes back to the database. If you change the contents of an element or an attribute in an XML document, the document will not retain the original value for that object. If you then read that modified document into an ADO.NET DataSet, ADO.NET will not be able to tell whether any of the rows have been modified, let alone how they've been modified.

In fact, if you modify the contents of a DataSet and use the WriteXml method to save that data in an XML document using the code shown earlier in the chapter, you'll lose the changes. By default, the WriteXml method writes just the current contents of the rows to the document.

ADO.NET Diffgrams

As I discussed earlier, you can supply values from the XmlWriteMode enumeration when calling the WriteXml method. One of the entries in the enumeration is DiffGram. If you supply this value in the call to the XmlWriteMode method, ADO.NET will write both the current and original contents of the DataSet to the document in a diffgram. Figure 12-5 shows an example of this format. You can later read this document back into your DataSet and submit the pending changes to your database using your DataAdapter objects.

Figure 12-5

The contents of a DataSet stored in diffgram format

The following code snippet generates the XML document shown in Figure 12-5. The code modifies the DataSet—modifying one row, deleting another, and adding a third—and then displays the contents of the DataSet as an XML diffgram. By looking at the document in the figure, you can see how the changes that the code makes to the DataSet correspond to the entries in the diffgram. The code uses the ShowXmlInIE procedure defined in an earlier code snippet and thus requires a reference to the Microsoft Internet Controls library.

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT TOP 3 CustomerID, CompanyName FROM Customers" Dim da As New OleDbDataAdapter(strSQL, strConn) Dim ds As New DataSet() da.Fill(ds, "Customers") Dim tbl As DataTable = ds.Tables("Customers") 'Leave the first customer unchanged. 'Modify the second customer. tbl.Rows(1)("CompanyName") = "Modified Company Name" 'Delete the third customer. tbl.Rows(2).Delete() 'Add a new customer. tbl.Rows.Add(New Object() {"ANEW1", "New Company Name"}) 'Write the contents to an XML document in diffgram format 'and display the document in Internet Explorer. Dim strPathToXml As String = "C:\MyData.XML" ds.WriteXml(strPathToXml, XmlWriteMode.DiffGram) ShowXmlInIE(strPathToXml)

Visual C# .NET

string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +           "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT TOP 3 CustomerID, CompanyName FROM Customers"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); DataSet ds = new DataSet(); da.Fill(ds, "Customers"); DataTable tbl = ds.Tables["Customers"]; //Leave the first customer unchanged. //Modify the second customer. tbl.Rows[1]["CompanyName"] = "Modified Company Name"; //Delete the third customer. tbl.Rows[2].Delete(); //Add a new customer. tbl.Rows.Add(new object[] {"ANEW1", "New Company Name"}); //Write the contents to an XML document in diffgram format //and display the document in Internet Explorer. string strPathToXml = "C:\\MyData.XML"; ds.WriteXml(strPathToXml, XmlWriteMode.DiffGram); ShowXmlInIE(strPathToXml);