by Mark D. Sutton, Content Master Ltd.

Using ADO.NET to implement an in-memory data cache

When an application operates with intermittent network connectivity, one of the challenges is to create an efficient and functional local data cache. The local data cache must store data in a compact space, allow local updates, and track changes to enable subsequent synchronization with a remote data store. This paper shows how the ADO.NET* DataSet is an ideal choice for a local data cache, and outlines its structure and capabilities.

This paper describes the structure and capabilities of the ADO.NET DataSet to help you understand why it is particularly suitable as a local data cache for an application operating with intermittent network connectivity. To learn how the ADO.NET DataSet capabilities described can be implemented, Identifying and Extracting Data Changes from an ADO.NET DataSet.

Mobilized software - Mobilized software contains a set of features designed to insulate users from network dependencies by making data and applications available when the user is offline. Upon re-connection to the network, information is automatically updated through a data synchronization process. This application architecture allows users to work productively while enjoying a mobile work and life style.

Open Database Connectivity* (ODBC) - A Windows* library for connecting to many different remote data sources, retrieving records, and executing updates.

Data Access Objects* (DAO) - An object library for the Microsoft Access* database, capable of connecting to remote data sources using ODBC.

Remote Data Objects (RDO) - An object library based on ODBC.

OLE Database (OLE DB) - A Windows library for connecting to many different remote data sources, independent of ODBC.

ActiveX Data Objects (ADO)* - An object library based on OLE DB data access.

Extensible Markup Language (XML) - A standard text format for the transmission of data and its structure.

Since ODBC, Microsoft data access has developed through several technologies. Earlier examples like DAO and RDO provided an object layer over ODBC to facilitate access to remote data sources - data sources not located on the client machine. However, both DAO and RDO required constant connection to their data sources. More recently OLE DB, and its object layer ActiveX Data Objects, have allowed a route to remote data independent of ODBC, and included the ability to create a record set that can operate disconnected from its data source.

ADO.NET differs from its predecessors because its DataSet object is designed primarily for disconnected operation. While retaining the ability to download data from a remote source, it has more comprehensive local capabilities than ADO, and more advanced abilities to track locally made changes.

This document describes the ADO.NET DataSet and its contribution to the provision of an efficient and highly functional local data cache for an application that operates in a mobilized software environment.

DataSet Structure

The DataSet is a complex object that contains several other interrelated objects, all of which are implemented in the System.Data namespace. The DataSet can be thought of as an in-memory representation of some tables extracted from a relational database. It offers many of the same features as a relational database, such as tables consisting of rows and columns, with key data items and constraints on the values that are allowed in particular columns. It also stores relationships between items in different tables. This section outlines the main components of a DataSet as illustrated in Figure 1, while subsequent sections describe some of these components in more detail.

Figure 1 – Main Components of a DataSet

A DataSet can have zero or more DataTable objects where the data resides in memory. Within a DataTable, the format of the data is described by DataColumn objects, and the data itself is represented by DataRow objects.

A single DataSet object can hold multiple DataTable objects, thereby allowing a more advanced local data cache than in previous data access technologies. In addition, the DataSet can implement a relationship between two DataTable objects using a DataRelation object.

The DataRelation object can enforce relational integrity between two related DataTable objects. It can throw an exception if an inappropriate local change is attempted, and retrieve the DataRows in one table that are related to a DataRow in the other DataTable.

The Constraint object can enforce column based rules such as foreign keys, uniqueness, and default values. It is used, for example, by a DataRelation to enforce relational integrity.

An important contribution to the efficiency of a DataSet is provided by the DataView object. You can use multiple DataView objects to present differently sorted and filtered subsets of the data in a DataTable. The DataView does not copy the data, but instead provides your application with a filtered and sorted collection of DataRowView objects, which in turn provide access to the data in the underlying DataRows.

The DataViewManager object is available for collecting together multiple DataView objects. The collected DataView objects may be all onto a single DataTable, or may be a combination of DataViews onto two DataTables related by DataRelation objects.

The following sections describe the DataSet in more depth, each illustrating a programming task.

Creating a DataSet

Your application can create and fill the DataTables in a DataSet in a number of ways:

• Create from scratch in memory
• Create by downloading from an external data source
• Create by loading a (suitably formatted) XML file
• Create from a Web Service method

When you create a DataSet from scratch in memory, in outline, you build DataTable objects from DataColumns and DataRows and define any DataRelations and DataViews that are required.

To retrieve data from an external source to create the DataSet, use the objects in one of the .NET Data Provider components. The following example shows the .NET Data Provider for Microsoft SQL Server (which is implemented in the System.Data.SqlClient namespace). Here a SqlDataAdapter object is used to fill a DataSet from a central SQL Server:

using System.Data.SqlClient;

dsTitles = new DataSet();

string sConn = @"Server=MyServer;database=pubs;Integrated Security=SSPI";

string sSQL = @"SELECT title_id,royalty FROM titles";

SqlDataAdapter da = new SqlDataAdapter(sSQL,sConn);

da.Fill(dsTitles);

Your application could also load a DataSet—both data and structure—from a suitably formatted XML file. This is discussed further in the section ‘Local Persistence Using XML’ of this paper.

Another way of transferring a DataSet to a machine elsewhere on the network, such as a mobile client, is to copy it using a Web Service. DataSets have a close affinity with XML, and the Web Service client classes in the .NET Framework handle the serialization to XML for transfer in the Web Service payload, and the deserialization back to a DataSet object at its destination, without you having to write any special handling code.

Data Binding

One of the most powerful features of a DataSet is its ability to be bound to user interface controls for the display of data. Update of the control with the data from the DataSet is automatic and completely local, and the Data Bound control reflects any changes due to local data updates, sorting or filtering. For example, the following code binds a DataTable to a DataGrid control:

dataGrid1.DataSource = dsTitles.Tables[0];

This feature makes it easy to create forms driven applications that allow a user to modify data, with all data updates stored in one or more DataSet objects.

Searching the DataSet

After your application has a DataSet with one or more DataTable objects containing local data, you should implement a search of the data to retrieve a single row (or a set of rows) for display or retrieve a single row for local update. This can be achieved locally, without returning to the remote data source. The following table outlines the ways of searching a DataSet:

Creating a DataView, or altering the default DataView, is one of the most useful and powerful of all the ways of searching because you can bind user interface controls to a DataView; you cannot use data binding with an array of DataRows. The following example uses:

dsTitles.Tables[0].DefaultView.RowFilter = "royalty < 50";

Notice that a DataView filter is created by changing its RowFilter property to a string containing a filter expression; formatted like a SQL WHERE clause without the word ‘WHERE’. In fact some SQL functions like isnull() are supported.

dsTitles.Tables[0].DefaultView.RowFilter = "isnull(royalty,0) <=12";

You can cancel the filter again by setting the RowFilter property to the empty string.

dsTitles.Tables[0].DefaultView.RowFilter = "";

After changing the RowFilter property in this way, the DataView exposes a collection of DataRowView objects that match the filter expression. You can check the size of this collection using the DataView object’s Count property.

int iCount = dsTitles.Tables[0].DefaultView.Count;

Sorting the DataSet’s Data

Implement sorting when presenting the DataSet’s data to the user. As with filtering, there are several approaches:

You can set the Sort property of a DataView object to a string containing the column name(s) to be sorted, followed by an optional direction: "ASC" (for ascending—this is also the default direction if you do not give a direction) or "DESC" (for descending). You can specify multiple column names, for example:

dsTitles.Tables[0].DefaultView.Sort = "royalty ASC, price DESC";

Making Local Changes to the DataSet Data

This section outlines how the data operations are achieved locally on the DataSet:

• Insert row
• Update data in a row
• Delete selected row

To insert a row in a DataTable, create a DataRow object using the DataTable’s NewRow method. This gives you a DataRow object with the correct number of DataColumns in it. Then add the data to DataColumns, and finally add the DataRow to the DataTable’s Rows collection.

DataRow dr;

dr = MyDataSet.Tables[0].NewRow();

dr[0] = 123;

dr[1] = "New Name For Example";

MyDataSet.Tables[0].Rows.Add(dr);

To update the row data in a DataTable, retrieve a DataRow object by using one of the searching methods covered in the ‘Searching the DataSet’ section of this paper. Then change the value of the appropriate DataColumn. If you have a Typed DataSet, then there is a typed property to help you do this, otherwise access the correct column by name or index.

dr[2] = 99; //access by column index

dr["Column Name") = 88;

Use the DataRow’s BeginEdit method to suspend validation while you perform multiple updates on different columns of the same DataRow, for example, where there is a calculation performed on multiple columns. You would then call the EndEdit method to commit the group of updates (there is also a CancelEdit method).

dr.BeginEdit();

dr[2] = 99;

dr[3] = "New Value";

dr.EndEdit();

To delete a row on the DataTable, you retrieve a DataRow object (as described above) and then call its Delete method.

dr.Delete();

Tracking Changes

The insert, update, and delete changes mentioned earlier, are made locally in the DataSet, even if the data was originally downloaded from an exter nal data source, no changes are sent back to the server, and no connection to the server is required.

To track the changes, each DataRow maintains up to four versions of its data: Current, Default, Original, and Proposed. To represent these in code use the DataRowVersion enumeration. Also, the DataRow object has a RowState property which can be represented by one of the DataRowState enumeration values.

So, your application can display records filtered by their row state in two ways:

• Create an array of DataRows by using the DataViewRowState argument in the DataTable’s Select method.
• Create a filtered DataView by using its RowStateFilter property.

In either case you will need one of the values of a third enumeration: DataViewRowState. Each value in DataViewRowState specifies a separate combination of DataRowState and DataRowVersion to control what rows are included.

A common requirement in a mobilized software application is to create a DataSet containing only changed records for returning to the central server, perhaps a Web Service method.

dsChanges = dsTitles.GetChanges(DataRowState.Modified);

However, the DataSet in the .NET Compact Framework does not have the GetChanges method. As a work-around, you could use the overload of the Select method which accepts a DataViewRowState value, and then copy the DataRows into a new DataSet.

//clone the structure from the original

dsChanges = dsTitles.Clone();

DataRow[] drChanges; //array of selected data rows

DataRow drNew;

int iRow;

int iCol;

//make the selection

drChanges = dsTitles.Tables[0].Select("", "", DataViewRowState.ModifiedCurrent);

//copy array into dsChanges

for (iRow=0; iRow <= drChanges.GetUpperBound(0); iRow++)

{

drNew = dsChanges.Tables[0].NewRow();

//copy all cols in row

for(iCol=0;iCol<=drNew.ItemArray.GetUpperBound(0);iCol++)

{

drNew[iCol] = drChanges[iRow].ItemArray[iCol];

}

dsChanges.Tables[0].Rows.Add(drNew);

}

Local Persistence Using XML

A DataSet has a WriteXml method to persist it to a local XML file. You can write just the data, or both the data and the data schema using the WriteXml method. Alternatively, you can use the WriteXmlSchema method to write the schema on its own. For example, use the .NET Compact Framework to write a local XML file using the overload of WriteXml that takes a string argument for the file name.

string sFileName = @"My DocumentsLocalStore.xml";

dsChanges.WriteXml(sFileName);

The DataSet also has a ReadXml method to load data and its schema from a local XML file, and the ReadXmlSchema method that can be used to read a schema file. The following example uses the overload of ReadXml that takes a string argument for the file name:

string sFileName = @"My DocumentsLocalStore.xml";

DataSet dsRead = new DataSet();

dsRead.ReadXml(sFileName);

When a local file does not have schema information, use the InferXmlSchema method to construct a schema from data in an XML file.

This paper has demonstrated that the ADO.NET DataSet has structure and capabilities for:

• Storing multiple data tables
• Defining and controlling relations between data tables
• Filtering and sorting
• Data binding to user interface controls
• Local updating
• Identifying and extracting changed data rows
• Persisting to local XML files

These make the DataSet an ideal choice for a local data cache when an application operates in an intermittently connected network environment. You can download data into the DataSet, disconnect from a remote data source, then manipulate the data offline, perhaps temporarily save and re-load from a local file, before transmitting updates back to the server for processing.

[1] Microsoft .NET Compact Framework Core Reference, Microsoft Press (ISBN 0-7356-1725-2)

[2] Microsoft .NET Framework On-line documentation, Microsoft, http://msdn.microsoft.com/en-us/library/w0x726c2.aspx

[3] Identifying and Extracting Data Changes from an ADO.NET DataSet

[4] Direct Access to SQL Server Databases from Wireless Clients

[5] Intel® Mobile Developer Community

[6] Data Synchronization: Which Technology

Comments (0)

Trackbacks (0)

Leave a comment