by Mark D. Sutton, Content Master Ltd

Identifying and Extracting Data Changes in the ADO.NET DataSet

An application that operates in the mobilized software environment requires local data cache to support offline operation. The advantage of using the ADO.NET DataSet (DataSet) object as a local data cache is the rich functionality it offers while disconnected from the source database and after it has been transferred to a machine on the network, such as a mobile client. You’ll find the DataSet’s ability to identify and extract data changes made locally particularly useful. Also useful is its ability to remember original values of data so that data changes can be rolled back.

The ability to extract data rows that have changed from one DataSet into a new and probably smaller DataSet is important for the transmission of data changes back to a central server because sending a smaller DataSet reduces network traffic and boost performance.

This paper describes how to program the ADO.NET DataSet to identify, extract, and accept or rollback locally made changes.

This paper describes how to program identification and extraction of changed rows from an ADO.NET DataSet as well as how to program accept or rollback of local changes. It presents solutions in both the full Microsoft .NET* Framework and the Microsoft .NET Compact Framework where necessary.

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

Microsoft .NET* Framework - A powerful runtime environment and set of class libraries that developers use to build applications for Microsoft Windows* computers.

.NET Compact Framework - An implementation of the .NET Framework for handheld computers such as Pocket PCs.

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.

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

Previous data access technology, such as ActiveX Data Objects (ADO)s, allowed a limited ability to create a record set capable of operating disconnected from its data source. Although such ADO recordsets have the ability to conduct transactions, and rollback edits made to the current row, they have no built-in mechanism for identifying and extracting changes.

In contrast, the ADO.NET DataSet (DataSet) object has far more powerful local capabilities, including the ability to track and extract locally made changes, as well as the ability to acc ept or reject single or multiple row changes.

The balance of this article guides you through the implementation of:

• Getting some data to start
• Making local changes
• Tracking local changes
• Presenting a view of the changes only
• Extracting local changes into a second DataSet
• Accepting or Rejecting changes

When different implementations are required for the full .NET Framework and the .NET Compact Framework, then a separate solution is presented for each.

Getting Some Data to Start

To demonstrate the rest of the solution, you need to get some data. The example code below shows how to get data from the Microsoft SQL Server* 2000 ‘pubs’ sample database. It puts data from the ‘titles’ table into a local DataTable object with the same name inside a DataSet, then it binds the resulting DataTable to a DataGrid control to display the data.

using System.Data.SqlClient;

using System.Data.Common;


dsTitles = new DataSet();

string sConn = @"Server=MyServer;" +

"database=pubs;User ID=MyLogin;PWD=MyPassword;";

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

SqlDataAdapter da = new SqlDataAdapter(sSQL,sConn);

da.Fill(dsTitles, "titles");

dataGrid1.DataSource = dsTitles.Tables[0];

This example works with both the full .NET Framework and the .NET Compact Framework. You need to replace MyServer, MyLogin, and MyPassword with the correct information for your SQL Server. Note that the Fill method of the SqlDataAdapter object opens a connection to the database, extracts the data into the DataSet, and then immediately closes the connection again. The mobile computer where this code runs may now be disconnected from the network, while the data remains stored inside the DataSet object. Alternatively, the code shown here can run inside a Web Service server, with the DataSet transferred to the mobile client as the response to a Web method call from the mobile client to the Web service.

The detail of the ADO.NET objects being used here is beyond the scope of this paper. For more information see the white paper Direct Access to SQL Server Databases from Wireless Clients. 

Making Local Changes

The next step is to update, insert, and delete changes to the data in the DataSet.

As an example, increment the ‘royalty’ column that corresponds to the currently selected row in the DataGrid control. There are two stages to this update:

• Locate the DataRow to change
• Update the data in the relevant column

To find the DataRow to change, use the Select method of the DataTable. This method takes a parameter that is like the WHERE clause of a SQL query, and returns an array of DataRow objects. Construct the parameter from the first column of the current row in the grid. For example:

string sFind = "title_id = '"

+ dataGrid1[dataGrid1.CurrentRowIndex,0].ToString() + "'";

DataRow[] drFound = dsTitles.Tables[0].Select(sFind);

if (drFound.GetLength(0) > 0)

{

//act on the DataRow

}

dr[1] = 12;

dr["royalty"] = 12;

DataRow dr = drFound[0];

dr[1] = Convert.ToInt32(dr[1]) + 1;

drFound[0].Delete();

DataRow dr = dsTitles.Tables[0].NewRow();

dr[0] = "NEW23"; //new id

dr[1] = 0;  //new royalty

dsTitles.Tables[0].Add(dr);

Tracking Local Changes

To allow your application to track the update status of any given row, the DataRow object has a RowState property that displays one of these (DataRowState enumeration) values.

So, for example, it is possible to loop through a DataTable examining the state of each row in turn:

for(int i=0; i < dsTitles.Tables[0].Rows.Count; i++)

{

MessageBox.Show(dsTitles.Tables[0].Rows[i].RowState.ToString());

}

As the above example is not a practical way to show the changes in a DataSet, the next section describes how to display changes in a DataView that is bound to a DataGrid control.

Presenting a View of the Changes

Your application can display records filtered by their row state by creating a filtered DataView that is bound to a DataGrid control. Note that even if you have setup data to a DataTable using code like this:

dataGrid1.DataSource = dsTitles.Tables[0];

dataGrid1.DataSource = dsTitles.Tables[0].DefaultView;

dsTitles.Tables[0].DefaultView.RowStateFilter = DataViewRowState.ModifiedCurrent;

Extracting the Changes

Creating a ‘changes only’ DataSet can be of great benefit if your application needs to transmit changed records over the network. This section shows you how to create a “changes only” data set in both the full .NET Framework and the .NET Compact Framework, because the implementations need to be slightly different.

If your application is using the full .NET Framework then you use the DataSet’s GetChanges method. The GetChanges method can take a parameter of one of the values from the DataRowState enumeration; it creates and fills a new DataSet with the rows that are in the given DataRowState. For example:

dsChanges = dsTitles.GetChanges(DataRowState.Modified);

dtChanges = dsTitles.Tables["titles"].GetChanges(DataRowState.Added);

dsChanges = dsTitles.Clone();

DataRow[] drChanges; //array of data rows

DataRow drNew;

int iRow; int iCol;

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

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

{

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

//copy all the cols in the row

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

{

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

}

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

}

If your DataSet contains more than one DataTable, then you need to add an outer loop to step through each DataTable in the DataSet.

Accepting or Rejecting Changes

To track changes, each DataRow maintains up to four versions of its data as shown in the table below.

You can implement a final accept or reject on the changes made locally in a DataSet. The DataRow, DataTable, and the DataSet objects all have AcceptChanges and RejectChanges methods. The scope of action of these methods depends on the object on which they are called.

When you call AcceptChanges on a DataRow its Original row version is updated to match its Current row version. In a similar way, the RejectChanges method rejects changes on the object on which it is called. It causes the Current row version to revert to the Original row version. In both cases, after the call, the DataRow’s RowState property is set back to ‘DataRowState.Unchanged’.

This paper demonstrated how the ADO.NET DataSet tracks DataRow state to allow filtering or extraction of ‘changes only’ data. It also detailed how the different versions of data kept by a DataRow allow a commit or roll-back of locally made changes.

You have seen in detail how to implement:

• Tracking local changes
• Presenting a view of the changes only
• Extracting local changes into a second DataSet
• Accepting or Rejecting changes

You should now be aware that implementation has to be different for the full .NET Framework and the .NET Compact Framework, and in this case both solutions have been given.

Use the techniques in this paper to extract ‘changes only’ data rows into a smaller DataSet and optimize performance in an application that must transmit data changes back to a central server. Further, use the direction in this paper to provide your application with an ‘undo’ function to reverse locally made changes if your application’s user decides they have made a mistake.

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

[2] Microsoft .NET Framework, Microsoft*

[3] Data Synchronization with the ADO.NET* DataSet

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

• Data Synchronization with the ADO.NET* DataSet
• Manipulate Data in an ADO.NET DataSet in a Mobilized Application
• Data Synchronization: Which Technology

Comments (0)

Trackbacks (0)

Leave a comment