The ADO.NET DataSet Class

The ADO.NET DataSet Class

The DataSet class within the ADO.NET architecture is the core component for providing data access in a distributed and disconnected environment. One of the largest limitations in ADO is the lack of support for having multiple tables and such within a record set. You have to shape a single record set in ADO in order to build a hierarchical record set. However, ADO.NET is leaps and bounds ahead of Microsoft’s first attempt at universal data access. Within ADO.NET, you have a sort of miniature database called a data set. No longer are you limited to having one table—you can have as many as desired. Plus, you can create relations between those tables and, furthermore, those tables can have constraints. All this is available through the DataSet class in the ADO.NET architecture.

A data set within .NET contains zero or more tables and zero or more constraints, which are accessed via DataTableCollection and DataRelationCollection, respectively.

DataTableCollection contains zero or more DataTable objects, whereas

DataRelationCollection contains zero or more DataRelation objects.

The DataSet class has a series of public instance properties that influence the way the DataSet instance behaves. Two of the biggest improvements in ADO.NET are the Tables and Relations collections. Table 17.1 lists some of the public instance properties available for the DataSet class.

TABLE 17.1 Some of the Public Instance Properties for the DataSet Class and Their Descriptions

The DataSet class also includes a set of public instance methods to help the developer manipulate the data returned from the data store in a variety of ways. Table 17.2 lists some of the public instance methods available for the DataSet class.

TABLE 17.2 Some of the Public Instance Methods for the DataSet Class and Their Descriptions

The DataTable Class

The DataTable class within ADO.NET represents a single, in-memory representation of a relational result set. The DataTable class can be found and referenced from the System.Data namespace. Data tables can be created by using a data adapter to “fill” a data set, or they can be created manually.

Here’s the C# code for creating a data table manually:

System.Data.DataSet  oDS  =  new  DataSet();

System.Data.DataTable  oTable  =  oDS.Tables.Add(“Orders”);

oTable.Columns.Add(“OrderID”, typeof(System.Int32)); oTable.Columns.Add(“OrderQuantity”, typeof(System.Int32)); oTable.Columns.Add(“CustID”, typeof(System.Int32));

Here’s the code for VB .NET:

Dim  oDS  As  System.Data.DataSet  =  new  DataSet()

Dim  oTable  As  System.Data.DataTable  =  oDS.Tables.Add(“Orders”)

oTable.Columns.Add(“OrderID”, typeof(System.Int32)) oTable.Columns.Add(“OrderQuantity”, typeof(System.Int32)) oTable.Columns.Add(“CustID”, typeof(System.Int32))

The preceding code examples create a new DataSet object and manually add a new table called Orders to the data set. Then, three columns are added to the Orders data table. All in all, this is a rather simple example of how to manually create a DataTable object and add it to a DataSet object.

A DataTable object consists of a collection of columns, constraints, and rows. The columns represent the fields within the result set, whereas the rows represent the individ-ual rows of data within the result set. The constraints maintain a collection of the rules the result set must follow. Table 17.3 lists some the public instance properties for the

DataTable class.

TABLE 17.3    Some of the Public Instance Properties for the DataTable Class

Just as the DataSet class contains a series of public instance methods to help with the overall manipulation of data, the DataTable class contains a series of methods designed to help with the manipulation of data within a single table. Table 17.4 lists some of the public instance methods for the DataTable class.

TABLE 17.4 Some of the Public Instance Methods for the DataTable Class

The DataRelation Class

Most database engines use a series of relations to organize information contained in vari-ous tables. They allow the database to store the information that can be logically grouped together in one place, thus reducing the amount of duplicate information in the database. This ultimately speeds up data access and operations performed against the data. It also allows information stored in one table to be related to a series of other tables.

Since a DataSet object can be considered its own personal little database, it’s important for the DataSet object to support a key concept of relational databases: table relation-ships. The DataSet class can support multiple DataTable objects, and in some cases, you may need to relate two or more of those DataTable objects together. This is where the DataRelation class comes in. This class manages a DataTable object’s relationship with another DataTable object by creating that relationship between two DataColumn objects.

The following code shows a simple example of how to establish a relationship between two tables within a DataSet object using C#:

System.Data.DataColumn  oParentColumn;

System.Data.DataColumn  oChildColumn;

oParentColumn = oDS.Tables[“Customers”].Columns[“CustomerID”]; oChildColumn = oDS.Tables[“Orders”].Columns[“CustomerID”];

System.Data.DataRelation  oRelation  =

➥ new System.Data.DataRelation(“CustomerOrders”, ➥ oParentColumn, oChildColumn); oDS.Relations.Add(oRelation);

Here’s the code for VB .NET:

Dim  oParentColumn  As  System.Data.DataColumn

Dim  oChildColumn  As  System.Data.DataColumn

oParentColumn = oDS.Tables(“Customers”).Columns(“CustomerID”); oChildColumn = oDS.Tables(“Orders”).Columns(“CustomerID”);

Dim  oRelation  As  System.Data.DataRelation  =

➥ new System.Data.DataRelation(“CustomerOrders”, ➥ oParentColumn, oChildColumn) oDS.Relations.Add(oRelation)

The preceding code assumes the existence of a DataSet object that has two DataTable objects in it: one for the Customers table, which has a CustomerID field in it, and one for the Orders table, which also has a CustomerID field in it. The code then grabs a reference to the two DataColumn objects to be related, creates a new DataRelation object, and then adds the new DataRelation object to the Relations collection on

the DataSet object.

Alternatively, these examples could be written in C# as follows:

System.Data.DataRelation  oRelation  =  oDS.Relations.Add(

➥ ”CustomerOrders”,  oDS.Tables[“Customers”].Columns[

➥ ”CustomerID”],  oDS.Tables[“Orders”].Columns[“CustomerID”]);

Here’s the code for VB .NET:

Dim  oRelation  As  System.Data.DataRelation  =  oDS.Relations.Add(

➥ ”CustomerOrders”,  oDS.Tables(“Customers”).Columns(

➥ ”CustomerID”),  oDS.Tables(“Orders”).Columns(“CustomerID”));

In this case, we are using the Add() method of the Relations collection on the DataSet object to accomplish this task in a much shorter form.

Table 17.5 lists some public instance properties available in the DataRelation class.

TABLE 17.5    Some of the Public Instance Properties Available for the DataRelation Class

The only methods available on the DataRelation class are the ones available on every object within the .NET framework, although the ToString method has been overridden. Again, because every class in the .NET Framework inherits, ultimately, from Object, every class will have at a minimum the following four methods listed in Table 17.6. In the case of the DataRelation class, the ToString method returns the value of the RelationName property.

TABLE 17.6    Some of the Public Instance Methods Available for the DataRelation Class

The DataView Class

The DataView class is provided within the ADO.NET Framework to allow for the filter-ing and sorting of a data table by using a basic for SQL syntax. This provides an inter-face of Web Forms and Windows Forms for which controls may be bound. For instance, you could have a DataTable object that contains an entire set of data and a DataView object that provides a small subset of that data. This prevents having to make another roundtrip to the server to get that information.

The following C# code shows a simple method for creating a custom view of the data within the Customers data table for all customers within the city of Berlin.

System.Data.DataTable oTable = oDS.Tables[“Customers”]; System.Data.DataView oView = new System.Data.DataView(oTable);

oView.RowFilter  =  “City=’Berlin’”;

Here’s the code for VB .NET:

Dim  oTable  As  System.Data.DataTable  =  oDS.Tables(“Customers”)

Dim  oView  As  System.Data.DataView  =  new  System.Data.DataView(oTable)

oView.RowFilter  =  “City=’Berlin’”

Because the DataView class represents a subset of data contained within a DataTable class, the DataView has a set of properties, events, and methods specialized in dealing with subsets of data. Table 17.7 lists some of the public instance properties available on the DataView class.

TABLE 17.7    Some of the Public Instance Properties Available on the DataView Class

In addition to the inherited methods mentioned in Table 17.6, the DataView class has some methods for performing actions against a particular filtered or sorted subset of data contained in a DataTable class instance. Table 17.8 lists some of the public instance methods available on the DataView class.

TABLE 17.8    Some of the Public Instance Methods Available on the DataView Class