Working with Relational Data

As .NET developers are aware, the programming model in the desktop Framework used to manipulate relational data is referred to as ADO.NET and encompasses the classes in the System.Data namespace.^[8] These classes are substantially supported in the Compact Framework as well and provide a simple and powerful means for developers to work with relational data locally on the device via XML. The primary class in this regard is DataSet, which can be used for working with data locally, persisting it on the device, and binding it to controls on a form.

^[8] For an in-depth look at ADO.NET, see Dan Fox's Teach Yourself ADO.NET in 21 Days (Sams, June 2002).

ADO.NET in the Compact Framework

ADO.NET is fundamentally divided between the DataSet and its related classes, such as the DataTable, and what are called .NET Data Providers. The data set is used to hold a set of relational data in one or more DataTable objects, each of which is fully disconnected from the source of its data and, therefore, is ideal for disconnected and occasionally connected mobile scenarios discussed in Chapter 1. A diagram of the DataSet class and its child classes can be seen in Figure 3-2.

As a result of the importance of using relational data, the DataSet and its associated classes are fully supported in the Compact Framework.

The Compact Framework also ships with two .NET Data Providers, the SqlClient provider in the System.Data.SqlClient namespace for accessing a remote SQL Server, as discussed more fully in the next chapter, and SqlServerCe, found in the System.Data.SqlServerCe namespace for accessing SQL Server 2000 CE directly on the device, as explicated in Chapter 5. As mentioned in Chapter 2, the Compact Framework does not support the OleDb provider for accessing data. These providers contain all of the classes necessary to communicate with a back-end data store in order to execute commands and retrieve results. For example, the providers support data readers to provide a connected, forward-only, read-only, cursor-style data-access method, which will be more fully discussed in the next chapter. In addition, each provider relies on base classes and interfaces (such as IDbConnection and DbDataAdapter) found in the System.Data and System.Data.Common namespaces to provide consistency and polymorphism to developers.

.NET Data Providers, through their data adapters, provide the functionality for retrieving data into, and updating data from, a DataSet. In this way, the DataSet can be used to work with both remote and local data, and, in fact, the same DataSet object can hold data from multiple data sources simultaneously, as shown in Figure 3-3.

Reading and Writing Data

Once data has been populated in a DataSet by using a data adapter, that is, an XML Web Service, or even by programmatically loading the DataSet, it can easily be saved and reloaded on the device. To save the DataSet contents, developers can use the WriteXml method, passing it either the filename or the XmlWriter to which to write the contents. For example, to write the contents of the DataSet to a file, the developer can simply execute the following statement:

Dim ds As New DataSet()
'Populate the DataSet
ds.WriteXml(fileName)

However, it is important to note that WriteXml used in this way will create an XML document that contains only the current values in each column of each row in its DataTable objects. In other words, although the individual DataTable objects can track three different versions of each column value (proposed, original, and current) and four different row states (added, deleted, modified, and unchanged), the XML produced by the previous snippet will not reflect this richness. This is the case regardless of whether the AcceptChanges method of the DataSet or DataTable has been called.^[9] As a result, this approach would be useful only when read-only data is being stored on the device, for example, a DataSet that contains lookup data later used to populate dropdown list controls and list boxes on a form.

^[9] This method sets the DataSet or DataTable as if all the current values are the original values and all the rows are unchanged. This method is used to provide a clean slate upon which to continue editing data in a DataSet.

A better approach that can be used to preserve the changes made to a data set involves using the overloaded signature of WriteXml and passing in an XmlTextWriter, as follows:

Dim xlr As New XmlTextWriter(fileName, System.Text.Encoding.Default)
ds.WriteXml(xlr, XmlWriteMode.DiffGram)
xlr.Close()

In this case, the WriteXml method accepts both the XmlTextWriter and a value from the XmlWriteMode enumerated type. Using the value DiffGram instructs the DataSet to write its contents in the Microsoft DiffGram format,^[10] which includes both the unchanged data, as well as the before and after versions of any changed values. For example, a very simple DataSet that contains just one table with Name and Position columns would look as follows when saved as a DiffGram after changing the position of one of the players:

^[10] This format was first introduced as a Web update to SQL Server 2000 to extend SQL Server's XML processing capabilities.

<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
  xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
  <NewDataSet>
    <Table diffgr:id="Table1" msdata:roworder="0"
       diffgr:hasChanges="modified">
      <Name>Sammy Sosa</Name>
      <Position>RF</Position>
    </Table>
    <Table diffgr:id="Table1" msdata:roworder="1" >
      <Name>Mark Bellhorn</Name>
      <Position>2B</Position>
    </Table>
  </NewDataSet>
  <diffgr:before>
    <Table diffgr:id="Table1" msdata:roworder="1" >
       <Position>SS</Position>
    </Table>
  </diffgr:before>
</diffgr:diffgram>

It should also be noted that if a developer wishes to create a DiffGram with only the changed rows and values, he or she can call the GetChanges method of the DataSet first, creating a DataSet with only the modified data prior to WriteXml.

To load XML into a DataSet, developers can use the ReadXml method and pass it either a filename or an XmlReader. In either case, if the XML was produced with the WriteXml method discussed previously, even if the DataSet does not already contain the appropriate schema, the schema will be created automatically. If the XML was not produced with WriteXml, the schema is inferred according to rules documented in the VS .NET 2003 online help and may throw an exception.

However, if the file or XmlReader contains a DiffGram like that previously shown, the DataSet must already contain the appropriate schema, or the schema must be included in the XML document being read. As a result, the technique that developers can use to persist a DataSet on a device while preserving its changes is illustrated in the following snippet:

Dim xtrData As New XmlTextWriter(fileName, System.Text.Encoding.Default)
ds.WriteXml(xtrData, XmlWriteMode.DiffGram And XmlWriteMode.WriteSchema)
xtrData.Close()

' Close the app and come back later; ds is a new DataSet

ds.ReadXml(fileName)

In this snippet the DataSet (ds) is saved as a DiffGram as discussed previously. However, the WriteSchema value of the XmlWriteMode enumerated type is also passed in order to write out the XSD along with the DiffGram. Then, when the application is reloaded, it can simply use the ReadXml method to read in both the schema and the DiffGram.