This is the second article in a series covering the subject of Concurrency in ADO .NET. Concurrency is the method by which many users are able access and change the same data simultaneously without being concerned that they are overwriting each others work.

The architects of the .NET Framework made a design decision that the DataSet object assumes that Optimistic Concurrency as the default method of concurrency. The DataSet and the XxxDataAdapter classes do not directly support pessimistic concurrency. Further, for those of you who are interested, and ADO .NET does not directly support server-side cursors (for example, dynamic, keyset, static). The only "cursor" supported by ADO .NET is the firehose cursor (not strictly a cursor anyway) which can be used via the XxxDataReader classes.

This is not to say that other methods can not be used, but it does mean that by leveraging the .NET Framework, it is much less work to create applications that implement full optimistic concurrency - in fact, it is easy!

In the previous article, the basic concepts of concurrency were reviewed, as well as a discussion of the way that the ADO .NET DataSet stores state that allows it to manage concurrency.

How the DataSet object supports Optimistic Concurrency

Where previous article culminated with a console application that demonstrates the built-in concurrency features of the ADO .NET DataSet object, this article drills deeper, focusing on the DataViewRowState enumerations. These properties are what give a DataSet the inherent capability to track its own state as changes are made to it, as shown in the figure below:

Table 1 - DataViewRowState Enumeration

The fact that the ADO .NET DataSet supports these properties automatically and without the programmer having to write one line of code brings tremendous time savings to the development effort.

This article will also show the reader, step-by-step, how to build an application using Visual Studio .NET and leveraging the Windows Forms objects in the .NET Framework. These tools will be used to build an application that demonstrates the DataViewRowState properties of the DataSet. This should ensure a complete understanding of the functionality available, which is a prerequisite for the proper implementation of optimistic concurrency.

The finished product of this article will yield an application that looks like this:

What the Application Does

The application loads a set of data from a SQL Server database. Using DataView objects, the application tracks the following DataViewRowState properties in real time as changes are made:

• Current
• Original
• Unchanged
• Modified
• Added
• Deleted

Since the DataSet always knows the state of these various parameters, it can always take the appropriate action when the time comes to actually update the data source.

This application therefore provides a way for the developer to play with the features in the ADO .NET object model interactively so that these features can be fully appreciated.

What .NET Framework Objects are Used to Build it?

As you know, the .NET Framework is an object-oriented system. In .NET, applications are built by putting different objects together that the .NET Framework provides and extending these objects, where required.

The experience of building a .NET application involves the following general phases:

1. The composition of predefined .NET objects into a unique object model network;
2. The extension of those objects and creation of new objects to the desired level of customization;
3. The writing of event code which orchestrates the interactivity between all objects in an application.

It is helpful to review the objects that will be used beforehand in order to clarify the parts of the .NET Framework that will be used. Building this application requires the developer to work closely with following objects:

• System.Data.SqlClient
  • SqlConnection
  • SqlCommand
  • SqlDataAdapter
• System.Data
  • DataSet
  • DataView
• System.Windows.Forms
  • Form
  • DataGrid

The remainder of this article is a walkthrough of the process involved with building this application.

Walkthrough: Building a WinForms Application in C# with Visual Studio .NET

The following walkthrough assumes you have Visual Studio .NET beta 2 and the SQL test database Pubs installed on your computer.

Step I - Creating the Visual Studio .NET Project

1. Launch Visual Studio .NET;

2. When fully launched, select File | New Project. A "New Project" dialog box will appear. Select the "Windows Application" template icon and name it "RowState."

Visual Studio .NET will now create the necessary files and set up a project directory that supports a .NET WinForms application. When this process is complete, the Solution Explorer window (Ctrl + Alt + L) will display the following:

Note that Visual Studio .NET has created a System.Windows.Forms.Form object and named it Form1. The code that represents this object has been automatically created and places in a C# file named "Form1.cs". Further, references to the .NET namespaces System, System.Data, System.Drawing, System.Windows.Forms and System.XML. These namespaces provide easy access to the objects and the methods & properties of those objects and represent those namespaces required by and most commonly used in WinForms applications. Other references can be added easily as required.

Note also that Visual Studio .NET has opened the file 'Form1.cs' in design mode, providing a visual design surface for the form object. This will represent the main window of the application.

Step II - Working with ADO.NET - Creating a SqlConnection and SqlDataAdapter

1. Open the Server Explorer (Ctrl + Alt + S) window;

2. Right-click the "Data Connections" icon and choose "Add Connection…" from the context-sensitive menu:

A "Data Link Properties" dialog will appear - fill it out as follows:

Click OK.

The Server Explorer window should now look as follows:

3. Now open the Toolbox window (Ctrl + Alt + X) and click on the Data tab.

4. The next step is to create a SqlConnection and SqlDataAdapter, the objects responsible for connecting to the datasource and for the way in which data is retrieved from & updated to the datasource, respectively. From the Data section of the Toolbox, drag a SqlDataAdapter onto the design surface of Form1.

Dragging the SqlDataAdapter icon from the Data section of the Toolbox onto the design surface of Form1 causes Visual Studio .NET to place the code necessary to create a SqlDataAdapter object in the Form1.cs file.

However, in this case, before that process begins, Visual Studio .NET opens a "Data Adapter Configuration Wizard." In the first screen, click Next.

Note that in the next screen of the wizard, Visual Studio .NET already displays the existing connection created earlier in the Server Explorer.

Click Next.

Click Create new stored procedures.

Click Next.

Click "Query Builder…" button. The "Add Table" dialog will appear:

Click Add, then click Close. This leaves the "Query Builder" window remaining with the Authors table from the Pubs database displayed:

Click on au_lname and click OK. A confirmation screen appears:

Click Next.

The following window appears confirming the name of the stored procedures to be created:

Click Next.

At this point, Visual Studio .NET creates the stored procedures on the SQL database through a self-generated SQL script. When this process is complete, click Finish.

Note that a new section, called the "Design Tray" has been created in the Form1.cs design view:

In the Design Tray, two non-GUI objects have been placed by Visual Studio .NET which represent the sqlConnection and sqlDataAdapter objects created using the Data Adapter Configuration Wizard. Visual Studio .NET puts all non-GUI objects in the Design Tray that can be edited visually within Visual Studio .NET.

These two object now give the application the capability to connect to a specific datasource and perform basic data retrieval and updates against that source.

5. The next step is to create a container to hold that data - the DataSet. From the Data menu in Visual Studio .NET, select "Generate DataSet…". The following dialog will appear:

Click OK.

Like step 4, this step creates a DataSet object in the Design Tray named dataSet1, but in this case does much more. It also adds an XML schema of the DataSet, known as an XSD file to the Solution Explorer, which in turn, makes the methods and properties of this object available to the project and visible in the Class View (Ctrl + Shift + C), as shown in the following image:

The XSD file provides a means for this application to share its schema and the DataSet container with other applications. Note I did not qualify that by saying .NET applications because the XSD, being written in XML, can be consumed by any application that understands XML.

The XSD file actually has a C# file hidden and linked to it in which the .NET definition of the DataSet is found. Based on this definition, the methods and properties of the DataSet object become a part of the object model of this .NET application. That behavior is specific to .NET.

Another point to note here is that instead of placing the DataSet definition inside of the Form1.cs file, which is technically possible, Visual Studio .NET creates this object as a component in a separate file. This provides independence from the application itself and facilitates easy consumption of those functionality required in a specific application.

In contrast, by looking at the Form1 object in the Class View, the other methods and created in previous steps can be found:

Knowing when to create objects as components is very important in the development of .NET applications. Here, Visual Studio .NET lends a helping hand and makes the decision for the developer.

To test the DataSet, click once on the dataSet1 object in the Design Tray and choose Preview Data… from the Data menu, and the following appears:

Click Fill DataSet.

The following appears:

This is live data that tests the objects that have been placed in this application so far and shows that the objects work correctly.

To summarize, the DataSet has now been created and made a part of the object model of this application. The DataSet is filled through the use of the sqlConnection and sqlDataAdapter objects.

The next step is to begin isolating the optimistic concurrency functionality of the DataSet in preparation to bring this information to the User Interface. This is done by through the DataView object.

Save the project.

Step III - Working with WinForms - Creating the UI and connecting it to ADO.NET

1. DataView objects are used to present customized views of the data in a DataSet to UI objects. In this step, the following views will be created, corresponding to the various DataViewRowState properties which exist. One DataView object will be created for each of the properties the application will display, as follows:

Table 2 - DataViews to be created for Application

Drag a DataView onto the design surface as follows:

As in previous steps, this causes Visual Studio .NET to add the code to Form1.cs necessary to create a generic DataView.

Press F4 to bring up the Properties window. Set the Table to authors, which is underneath dataSet1 as follows:

Set the name of this DataView to dvCurrent as its RowStateFilter is set to dvCurrent.

Drag five (5) more DataViews onto the design surface.

Set the Table property of these five DataViews to Authors by selecting that table in the dropdown list.

Rename the DataViews as follows:

• dvAdded
• dvDel
• dvMod
• dvOrig
• dvUnch

Now, here is a bug in Visual Studio .NET beta 2 - the next step will be to set the DataViewRowState properties for these new DataViews, and the right way to do it is to set their properties in the Properties window as with, for example, the dvAdded object. However, due to a bug in the current beta version, these particular properties are reset to the default whenever the application is compiled. The solution to this is to set the properties programmatically later in this walkthrough.

In conclusion, were the beta not to have this bug, the current step would be made complete by setting the properties for each of these DataViews in the Properties window to match the name of the specific DataView.

The next step will be to build the user interface for this application using the following .NET Framework objects:

• Form
• DataGrid
• Button
• Label

2. Drag a DataGrid, Button and Label from the Toolbox onto the Form, as follows:

Now resize the form, copy and paste the DataGrid, Button and Label objects as follows:

In the Properties window, set the Text property of each Label so they display(in left to right, top to bottom order):

• Current
• Original
• Unchanged
• Added
• Modified
• Deleted

Now, set the DataSource property for each DataGrid as follows:

DataGrid Name	DataView Name
dataGrid1	dvCurrent
dataGrid2	dvOrig
dataGrid3	dvDel
dataGrid4	dvMod
dataGrid5	dvOrig
dataGrid6	dvUnch

Since all of the DataGrids, except the first one are supposed to be read-only, set the ReadOnly property of dataGrid2-6 to True.

And finally, change the Text property of the Buttons to Load and Save Data.

Save the project.

Step IV - Coding the Application Events

1. Double click on the Button whose text reads "Load Data". This will cause Visual Studio .NET to insert an OnClick event handlerinto the code of Form1.cs.

Place the following self-documented code between the brackets:

// Display a wait cursor while the dataset is loaded. Cursor.Current = Cursors.WaitCursor; //Load DataSet sqlDataAdapter1.Fill(dataSet1); //Initialize DataSet State dataSet1.AcceptChanges(); //Set DataView properties manually since the visual designer is not holding the property dvCurrent.RowStateFilter = DataViewRowState.CurrentRows; dvMod.RowStateFilter = DataViewRowState.ModifiedCurrent; dvAdd.RowStateFilter = DataViewRowState.Added; dvDel.RowStateFilter = DataViewRowState.Deleted; dvOrig.RowStateFilter = DataViewRowState.OriginalRows; dvUnch.RowStateFilter = DataViewRowState.Unchanged; //Reset Cursor Cursor.Current = Cursors.Default;

2. Now, double-click the the Button whose text reads "Save Data". As in step 1, this will cause Visual Studio .NET to insert an OnClick event into the code of Form1.cs.

Place the following self-documented code between the brackets:

if (MessageBox.Show("You are about to save the DataSet. This will clear all state management and commit all current values. Are you sure?", "Confirmation",MessageBoxButtons.OKCancel ,MessageBoxIcon.Warning) == DialogResult.OK) {   //All this actually does is reset the state of the DataSet -   // if we were really saving the data back to the source there would be more code here.   dataSet1.AcceptChanges(); }

Save the project.

Step V - Compiling and Running the Application

1. Press F5. If the application has been built to the specification this document provides, it will compile and momentarily, the following will appear onscreen:

Press Load Data.

The application will fire the event code associated with that button, load the DataSet, set the properties of the DataViews, and bind these to the DataGrid, as follows:

Delete the first record in the Current DataGrid.

Now change the Current first record in some way.

Now add a record to the Current list.

THe end result looks like this:

Conclusion

Through this Windows Forms application, the inherent DataViewRowState found in the ADO .NET model, provide a basis for supporting optimistic concurrency operations in the .NET Framework. This application provides great insight and understanding into that functionality, as well as introducing various parts of the .NET Framework and providing some useful experience in working with Visual Studio .NET.

I hope you have enjoyed this walkthrough and that is helps you to develop an appreciation of the features in the ADO .NET object model as well as the .NET Framework and Visual Studio .NET as a whole.