In my previous article, “Understanding and Working with ADO.NET DataSets,” a DataSet was constructed in code in a step-by-step, object-by-object manner. It required a thorough understanding of the data and took a fair amount of coding effort. If you’re not familiar with ADO.NET base classes, you should probably familiarize yourself with the previous article before continuing with this one.

Is There an Easier Way?

The .NET Framework offers many different ways to solve the same problem. When working with data, things are usually easier if there’s a schema to describe the data. If you’re going to work with data, you’ve got to understand it. The best way to understand data is to have the metadata that describes and explains the raw data. In the .NET environment, an XML schema definition file (xsd) provides the metadata and simplifies the task of working with the data. Most of the code from the previous article can be eliminated by reading in a schema that defines the data structures and their relationships. But before eliminating any code from the previous article, a schema can easily be created by temporarily adding the following lines of code:

dsNorthwind.WriteXmlSchema("C:\Northwind.xsd") ' VB.NET

dsNorthwind.WriteXmlSchema("C:/Northwind.xsd"); // C#

If you prefer, you can download the Northwind.xsd. The XML schema definition file looks like this in Visual Studio.NET:

Class-level Declarations

Switch to the code view and start initializing class-level objects. All that’s needed is a DataSet object:

Private dsNorthwind As New DataSet("Northwind") ' VB.NET
private DataSet dsNorthwind = new DataSet("Northwind"); // C#

Initialization Code

The following code can be placed in the Form Load event handler. Most of the initializations that were done in code are automatically set when the xsd file is read. The initialization code is simplified to only two lines:

dsNorthwind.ReadXmlSchema("C:\Northwind.xsd") ' VB.NET
grdNorthwind.DataSource = dsNorthwind.Tables(0) ' Region table

dsNorthwind.ReadXmlSchema("C:/Northwind.xsd"); // C#
grdNorthwind.DataSource = dsNorthwind.Tables[0]; // Region table

This DataSet is a typed DataSet, one in which the schema is included with the data. The project is now ready for initial testing of the DataSet. If you perform the same initial tests as in my previous article, you’ll find the constraints are enforced without writing code. When a schema is available, strongly-typed DataSets are preferred.

Retrieving Data from the Northwind Database

The code in btnRetrieveNorthwind click event handler is unchanged except for the two lines that use the Fill method of the SqlDataAdapter objects. Notice that the following two lines refer to the tables by ordinals instead of names, unlike the previous article.

sqlRegion.Fill(dsNorthwind.Tables(0)) ' Region table
sqlTerritories.Fill(dsNorthwind.Tables(1)) ' Territories table

sqlRegion.Fill(dsNorthwind.Tables[0]); // Region table
sqlTerritories.Fill(dsNorthwind.Tables[1]); // Territories table

Updating SQL Server

Add another button to the form as shown below and name it btnModifyNorthwind.

In the click event handler for the new button, add code to connect to the Northwind database. This code is identical to the code used to retrieve data from Northwind.

Dim sqlConn As SqlConnection = _
            New SqlConnection("server=(local);" & _
                    "Trusted_Connection=yes;database=Northwind")
Dim sqlRegion As SqlDataAdapter = _
              New SqlDataAdapter("select * from region", sqlConn)
Dim sqlTerritories As SqlDataAdapter = _
              New SqlDataAdapter("select * from territories", sqlConn)

Next, code is needed to process the changes that are sent back to Northwind. You can write your own statements or have them generated for you by using SqlCommandBuilder objects as shown below:

Dim regionCommandBuilder As SqlCommandBuilder = _         ' VB.NET
                         New SqlCommandBuilder(sqlRegion)
Dim territoriesCommandBuilder As SqlCommandBuilder = _
                              New SqlCommandBuilder(sqlTerritories)

SqlCommandBuilder regionCommandBuilder = new              // C#
SqlCommandBuilder(sqlRegion);
SqlCommandBuilder territoriesCommandBuilder = new 
SqlCommandBuilder(sqlTerritories);

Use the Update method of the SqlDataAdapter objects to modify Northwind:

sqlRegion.Update(dsNorthwind, "Region")             ' VB.NET
sqlTerritories.Update(dsNorthwind, "Territories")
sqlConn.Close()
sqlConn.Dispose()

sqlRegion.Update(dsNorthwind, "Region");            // C#
sqlTerritories.Update(dsNorthwind, "Territories");
sqlConnection.Close();
sqlConnection.Dispose();

ADO.NET Behind the Scenes

The ADO.NET disconnected paradigm maximizes scalability by minimizing expensive database connections. Because there are no persistent connections, rows aren’t locked on the database server, so optimistic concurrency must be assumed. The SqlCommandBuilder creates SQL statements used by the Update method of a SqlDataAdapter.

Begin testing by adding a row as shown below:

Click the Write XML button. The saved XML DiffGram indicates the DataSet recognizes the insert.

Click the Modify Northwind button to send the insert to the database. A SQL Profiler trace clearly shows what is actually executed on the database server:

exec sp_executesql
N'INSERT INTO region( RegionID , RegionDescription ) VALUES ( @p1 , @p2 )'
, N'@p1">N'@p1 int,@p2 nchar(4000)'
, @p1 = 5, @p2 = N'Central'

Now delete a row from the DataSet. Select the new row just created.

Press the Delete key on the keyboard, then click the Write XML button. The DiffGram indicates a delete by having a before image of the row and nothing else.

Click the Modify Northwind button and check SQL Profiler.

exec sp_executesql 
N'DELETE FROM region WHERE ( (RegionID = @p1) AND ((RegionDescription IS
NULL AND @p2 IS NULL) OR (RegionDescription = @p3)) )'
, N'@p1">N'@p1 int,@p2 nchar(4000),@p3 nchar(4000)
, @p1 = 5, @p2 = N'Central'
, @p3 = N'Central'

The delete from Northwind succeeds. Next, reenter the data in the grid and insert the data back into Northwind. Use the Query Analyzer to delete the new row from Northwind. Delete the row from the grid and click the Modify Northwind button to create a concurrency violation. An exception is thrown.

Because no rows were affected, ADO.NET detects the concurrency violation.
Now consider a different type of problem. Once again, add the row to the grid and update Northwind from the DataSet. Use the Query Analyzer to change the RegionDescription from Central to Local. The DataSet and the database do not agree at this point. The DataSet is not aware of the changes made to Northwind, so clicking Modify Northwind at this point would not cause any changes to Northwind. Go to the grid and change Central to Local. Use the Write XML button to generate a new DiffGram. The relevant parts are shown below:

The DataSet now has changes, but the changes match what is currently in the database. So now the question is, did the SqlCommandBuilder build a statement smart enough to detect the concurrency problem on the update?

The SQL Profiler reveals exactly why the concurrency violation was detected.

exec sp_executesql 
N'UPDATE region SET RegionDescription = @p1 WHERE ( (RegionID = @p2) AND 
((RegionDescription IS NULL AND @p3 IS NULL) OR (RegionDescription = 
@p4)) )'
, N'@p1 nchar(4000),@p2 int,@p3 nchar(4000),@p4 nchar(4000)'
, @p1 = N'Local'
, @p2 = 5
, @p3 = N'Central'
, @p4 = N'Central'

The WHERE clause generated by the SqlCommandBuilder detects that a concurrency violation has occurred. The DataSet has Central as the original value of the RegionDescription, but the database contains Local. This difference causes the WHERE clause to fail and the exception to be thrown.

Instead of relying on the SqlCommandBuilder to generate the necessary SQL statements or stored procedures, you can write your own.

VB.NET Code

The VB.NET code is available here in UpdDataSetVB.vb and UpdDataSetVB.resx. To use this code, create a Windows Application project. Delete the existing Form1.vb from the project. Copy the two files to your project’s folder. Add the two new files to the project as existing items. You can add both files with one invocation of Add Existing Item.

C# Code

The C# code is available here in UpdDataSet.cs and UpdDataSet.resx. To use this code, create a Windows Application project. Delete the existing Form1.cs from the project. Add the two new files to your project as existing items. You can add both files with one invocation of Add Existing Item.

Working with DataSets

As you have seen, using ADO.NET with XML schemas offers an easy but powerful programming paradigm. Although ADO.NET assumes optimistic concurrency, it can’t prevent concurrency violations. That’s why testing for concurrency violations is important. If a DataSet is large and typically has few changes, it’s prudent to create a new DataSet consisting of only the changed data. This approach reduces network traffic by sending a smaller DataSet back to the database server. DataSets and DataTables have a GetChanges method that can be used to build a DataSet of only changed rows. Individual rows are found in a DataRowCollection object. The DataRowState, DataViewRowState, and DataRowVersion enumerations can be used to find out about changes to individual rows.

The tasks discussed in this article could have been accomplished differently. Code enhancements such as error handling are left as an exercise for the reader.

--

John Paul Cook is a database and systems architect in Houston, Texas. His primary focus is helping large enterprise customers succeed with SQL Server, Oracle, and the .NET framework.