ADO.NET is more than new syntax to retrieve your data faster. It’s also a new way of looking at your data. With ADO.NET, you can create a DataSet, which is a disconnected object that can be configured to be very similar to a database. The similarities are apparent in the following diagram, which compares the SQL Server Northwind database to an ADO.NET DataSet of the same name.

Figure 1: Mapping of Northwind Database to ADO.NET.

Starting the Visual Studio.NET Project

Start a new project in Visual Studio using your preferred language and use the Windows Application template. Add a grid control to the form and set its Name property to grdNorthwind. Resize the grid as shown.

Class Level Declarations

Switch to the code view and start instantiating class level objects. Begin by creating a DataSet object:

Private dsNorthwind As New DataSet(“Northwind”)  ' VB

Next, create DataTable, DataColumn, and Constraint objects to define the table-like structures. Notice that both column names and data types are specified.

Private dtRegion As New DataTable("Region")
Private colRegionID As DataColumn = _
            dtRegion.Columns.Add("RegionID", GetType(System.Int32))
Private colRegionDescription As DataColumn = _
            dtRegion.Columns.Add("RegionDescription", _
                                 GetType(System.String))

It’s possible to define a primary key by setting the PrimaryKey property of a DataTable. The disadvantage is that it doesn’t allow you to specify the name of the primary key. By adding a constraint to a DataTable’s Constraint collection, the primary key can be assigned a name as shown below. It is the value of the third argument (True) that makes the constraint a primary key.

Private pkRegion As Constraint = dtRegion.Constraints.Add( _
            "pkRegion", dtRegion.Columns("RegionID"), True)

The child DataTable is defined next. It is assigned a name. All of its columns are assigned names and data types. A primary key is specified.

Private dtTerritories As New DataTable("Territories")
Private colTerritoryID As DataColumn = _
            dtTerritories.Columns.Add("TerritoryID", _
                                      GetType(System.String))    
Private colTerritoryDescription As DataColumn = _
            dtTerritories.Columns.Add("TerritoryDescription", _
                                      GetType(System.String))
Private colRegionIDFK As DataColumn = _
            dtTerritories.Columns.Add("RegionID", _
                                      GetType(System.Int32))
Private pkTerritories As Constraint = _
            dtTerritories.Constraints.Add( _
            "pkTerritories", dtTerritories.Columns("TerritoryID"), _
                             True)

A foreign key constraint must be defined to ensure referential integrity. The foreign key is assigned a name, then the parent column is specified, followed by the matching child column.

Private fkTerritories As Constraint = dtTerritories.Constraints.Add( _
            "fkRegionID", dtRegion.Columns("RegionID"), _
                          dtTerritories.Columns("RegionID"))

Initialization Code

The following code can be placed in the Form Load event handler. Properties must be set to make the Northwind DataSet as close to the Northwind database as possible. Other properties that could be of interest include AutoIncrement and DefaultValue.

colRegionDescription.AllowDBNull = False
colRegionDescription.MaxLength = 50

colTerritoryDescription.AllowDBNull = False
colTerritoryID.MaxLength = 20
colTerritoryDescription.MaxLength = 50

The DataTables must be added to the DataSet.

dsNorthwind.Tables.Add(dtRegion)
dsNorthwind.Tables.Add(dtTerritories)

Although Constraint objects were used to define constraints, those are really just definitions. To actually enforce the constraints requires setting a DataSet level property. If your DataSet is populated with data from an Oracle database, you might need to also set the DataSet’s CaseSensitive property to true.

dsNorthwind.EnforceConstraints = True

Adding a DataRelation to the Relations collections facilitates navigation between DataTables within a grid control. A DataRelation allows a parent DataTable to refer to its children or a child DataTable to refer back to its parent. It is easy to confuse a DataRelation with a constraint. Just remember that constraints ensure data integrity and DataRelations are navigational aids.

dsNorthwind.Relations.Add("fkRegionID", _
            dtRegion.Columns("RegionID"), _
            dtTerritories.Columns("RegionID"), True)

The DataSet must become the data source for the DataGrid.

grdNorthwind.DataSource = dsNorthwind.Tables("Region")

The project is now ready for first level testing.

Testing the DataSet

Run the project, click on the + sign, resize the Region Description column, enter 1 for the RegionID, and enter Southern for the RegionDescription as shown below.

The DataRelation provides the link to the child data. Click on the fkRegionID hyperlink and enter child data. Put valid data in the first row and an invalid RegionID in the second row.

When you leave the second row, this error message appears informing you of a foreign key violation:

Non-unique values for the primary key cause this message to be shown:

Data that exceeds the maximum length of a column results in this error message being displayed:

Without any connection to or awareness of the Northwind database, the Northwind DataSet is able to behave like a relational database.

Retrieving Data from the Northwind Database

To make the DataSet more useful, code can be added to populate the DataSet and the DataGrid from the Northwind database. Add a button named btnRetrieveNorthwind to the form as shown.

Retrieving data requires the addition of the SqlClient namespace before the form’s class definition.

Imports System.Data.SqlClient

Public Class Form1

In the click event handler for the button, begin by instantiating a SqlConnection object.

Dim sqlConn As SqlConnection = _
                New SqlConnection("server=(local);" & _
                    "Trusted_Connection=yes;database=Northwind")

Since DataSets are always disconnected, it’s necessary to use another object to marshal data between the database connection to Northwind and the Northwind dataset. DataAdapter objects allow data changes to be posted back to the data source. SqlDataAdapter is used instead of OleDbDataAdapter because it offers better performance for SQL Server databases. One SqlDataAdapter object is needed for each DataTable object.

Dim sqlRegion As SqlDataAdapter = _
            New SqlDataAdapter("select * from region", sqlConn)
Dim sqlTerritories As SqlDataAdapter = _
            New SqlDataAdapter("select * from territories", sqlConn)

The Fill method of a SqlDataAdapter is used to load the data into the DataTable.

sqlRegion.Fill(dtRegion)
sqlTerritories.Fill(dtTerritories)

Finally, the Close method of the SqlConnection object is used to release the connection to the Northwind database.

sqlConn.Close() 

Testing Data Retrieval from Northwind

The application is now ready for more incremental testing. Run the application and click the Retrieve Northwind button. The form now appears like this.

Persisting Changes to Disk

None of the code shown so far really takes advantage of the disconnected nature of DataSets. Mobile users can’t stay connected to the database but need to work with the data offline. DataSets allow mobile users to have the advantages of a database, such as referential integrity and constraint checking, without having a connection. By writing a DataSet to disk as XML, the DataSet and any changes made to the dataset can be persisted even when the client machine is turned off. When the client machine is turned on, the XML can be read from disk and read back into the DataSet. To see how this works, add two buttons to the form as shown below:

The click event handlers for the two new buttons are very simple. It’s very important to specify that the WriteXml method uses the DiffGram format. The next article on working with ADO.NET will use the DiffGram format to update the Northwind database.

Private Sub btnReadXml_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles btnReadXml.Click
    dsNorthwind.ReadXml("/sql/sql_articles/cook4/CDBAzine.xml")
End Sub

Private Sub btnWriteXML_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles btnWriteXML.Click
    dsNorthwind.WriteXml("/sql/sql_articles/cook4/CDBAzine.xml", XmlWriteMode.DiffGram)
End Sub

Testing Changes in a Disconnected Environment

Click Retrieve Northwind. Make changes and then click Write XML. Close the application. Start the application. Click Read XML. All of the changes you made before you clicked Write XML are visible in the grid control.

VB.NET Code

The VB.NET code is available here in DataSetVB.vb and DataSetVB.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 DatasetCS.cs and DatasetCS.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, the .NET Framework classes simplify the task of working with data in a disconnected paradigm. 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. In the next article on working with ADO.NET, you’ll learn how to save changes back to the database server.

--

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.