Data Storage in ADO.NET

Abstract: This article is the second in a series to look at ADO.NET. In this installment we take a look at the ADO.NET data storage classes.

The .NET framework provides a series of interfaces, types, and classes in the System.Data second-level namespace that serve as the basis of data access. These definitions are referred to as ADO.NET.

ADO.NET can be conceptually divided into two parts, the data access mechanism and the data storage layer. In the first article in this series I described the interfaces and some of the classes that implement them. These definitions are found in data-specific third-level namespaces under System.Data, such as System.Data.SqlClient, System.Data.OleDb, and System.Data.Odbc. You can read this article by clicking here.

In this article I will take the same high-level look at the classes that provide for data storage. In the next article in this series I will begin providing specific examples of how to access your data using ADO.NET.

The ADO.NET Data Storage Classes

Unlike the ADO.NET data access classes, which implement ADO.NET-specific interfaces, data storage in ADO.NET is provided for by concrete classes. These classes give you with the ability to store, edit, update, and control the view of data, regardless of how this data was obtained.

The most important class involved in the ADO.NET storage mechanism is the DataSet class. But this class relies on a number of other important classes. The primary ADO.NET data storage classes, and their relationships, are represented in the following figure.

As mentioned above, the central class associated with this mechanism is the DataSet. This class provides for an XML-based, in-memory storage of data obtained through the data access mechanism, independent of which data access mechanism you used to acquire the data.

In fairness, it should be noted that the preceding diagram is an oversimplification of the data storage classes. In particular, a number of classes are not shown here. For example, the DataSet, DataTable, and DataColumn classes each have an ExtendedProperties property, which is a property of type PropertyCollection. Furthermore, DataTable instances have ChildRelations and ParentRelations properties, which are collections of DataRelations.

These auxiliary properties, and others non-essential types, have been omitted in order to keep this diagram focused on the critical classes involved in data storage. For information on properties and classes not discussed here, see the .NET Framework documentation, which is part of the .NET framework 1.1 SDK (software development kit). This documentation can be installed from your C# Builder CD-ROM or the Delphi .NET preview compiler download, available to registered Delphi 7 developers. You can also get this documentation by download the SDK directly from Microsoft's Web site.

Each of the major classes involved in ADO.NET data storage are discussed in the following sections. The similarities between these classes and those found in Borland's VCL (visual component library) and CLX (component library cross-platform) are discussed.

DataSet

The DataSet class is the core class associated with ADO.NET data storage. It provides a high-speed, XML-based, in-memory data store capable of simultaneously storing data from numerous, and potentially unrelated, data sources.

These data sources may be result sets from SQL SELECT statements, or they may be data that is obtained programmatically. For example, a particular DataSet may include data from two separate SQL SELECT statements, some data read from an external file, as well as data input by an end user.

Data in a DataSet is stored in DataTable objects. When a single DataSet contains data from more than one domain, the DataSet contains one DataTable object for domain. For example, if a DataTable contains both customer data and employee data, the customer data is stored in one DataTable and the employee data is stored in another. The DataSet class has a Tables property, which is an ICollection implementing class that you can use to access the individual DataTables stored in a DataSet.

A DataSet can write its data to XML using one of a number of DataSet methods. This feature permits you to easily package data for either transfer to another application (possibly through a Web service) or to persist data on the local file system.

As mentioned earlier, the DataTables in a DataSet may contain unrelated data, but it is just as likely that two or more DataTables in a given DataSet contain related data, in a relational database sense. For example, one DataTable may contain information about customers, while another contains information about customer's purchases.

The relation between two DataTables is represented by an instance of the DataRelation class. The DataSet class has a Relations property, which is another ICollection implementing class used to manage DataRelation instances. DataRelations are described in greater detail later in this article.

The closest equivalent to a .NET DataSet in Delphi is a ClientDataSet that contains nested datasets, but the similarity is not great. In fact, there are far more differences between .NET DataSets and ClientDataSets. For example, a DataSet has no concept equivalent to a ClientDataSet's current record.

DataTable

The DataTable class provides for the storage of conceptual row/column structures within a DataSet. (The physical storage uses nested XML elements.) In most cases, this data is obtained through a SQL select statement, although DataTables can also be created dynamically and populated through code.

Two of the more important properties in the DataTable class are Rows and Columns. Rows is a DataRowCollection, which is an ICollection of DataRow objects. It is through the Rows property that you traverse navigate the data in a DataTable.

The Columns property is a DataColumnCollection, which is also an ICollection property, consisting of a collection of DataColumn instances. These DataColumns represent the metadata, or structure, of the DataTable. When creating a DataTable dynamically at runtime, you do so by defining the DataColumn objects associated with the DataColumnCollection property of the DataTable.

There is no equivalent of a .NET DataTable in Delphi, although a ClientDataSet comes pretty close.

DataRow

The DataRow class permits you to reference a specific row of data in a DataTable. This is the class that permits you to edit, accept, or reject changes to the individual DataColumns of the row.

DataRow objects have a RowState property that you can use to identify whether or not changes have been applied to the row, and an Items property that provides access to the individual DataColumn objects associated with that row. DataRow objects also have an RowError property that you can set or get to save or read error messages associated with operations on the row.

DataColumn

You use the DataColumn class for several purposes. First, you add DataColumn instances to the DataColumnCollection property of a DataTable to define the table's structure. It is also through DataColumn instances that you can define calculated and aggregate fields.

DataColumn instances are also the class of object that you use to read and write individual columns of a database. The Items property of a DataRow returns a DataColumn.

DataRelation

A DataRelation identifies that two or more of the DataTables in a DataSet contain data related in a one-to-one or one-to-many (parent-child) association. You define a relationship between two tables by adding a new DataRelation object to the DataSet's DataRelationCollection property. Once the relation has been added, the ChildRelations collection property of a DataTable includes the DataRelationCollection of DataRelations where the table is a parent table, and the ParentRelations property contains the DataRelationCollection of the DataRelations where the table is a child.

DataRelations are particularly valuable for creating master-detail views, as well as for performing aggregate calculations for groups of records within a DataTable. DataRelations also permit data from a DataSet to be written to an XML stream where the child data is nested within the parent data.

DataView

A DataView is an object that provides a custom view of data in a DataTable. DataViews provide sorting, filtering, and other types of customizations. Each DataTable object has a DefaultView property which holds the default DataView object for that DataTable. Modifying the DefaultView object sets the default display characteristics for the DataTable. Alternatively, you can create an instance of a DataView and associate it with a particular DataTable in a DataSet. This permits you to have two or more different views of the same DataTable simultaneously available.

Constraints

Constraints are objects that control how edits are applied to DataRows in one or more DataTables in a DataSet. There are two general types of Constraints, ForeignKeyConstraints and UniqueConstraints. You use ForeignKeyConstraints when two or more of your DataTables are related in a parent-child relationship.

ForeignKeyConstraints control two aspects of the relationship between related DataSets. The DeleteRule and UpdateRule properties of a ForeignKeyConstraint control what happens to the child foreign key when you attempt to change the primary key of a parent table. The AcceptReject rule controls whether posting or canceling parent DataTable records should be propagated to child tables.

UniqueConstraints are used to identify one or more fields as the primary key of a DataTable. When a DataTable has a primary key, attempts to post a new record to a DataSet that duplicates the values on the primary key raises an exception.

Instead of explicitly creating a UniqueConstraint to define a primary key, if your DataTable has a single field in its primary key, you can set the Unique property of the associated DataColumn to define a UniqueConstraint.

DataRowView

A DataRowView is a special object that represents a row in a DataView. Because each DataView can have a different RowStateFilter, the DataRowView obtained from a DataView will contain data consistent with the DataView's RowStateFilter, providing a custom view of the data.

About the Author

Cary Jensen is President of Jensen Data Systems, Inc., a Texas-based training and consulting company that won the 2002 and 2003 Delphi Informant Magazine Readers Choice Awards for Best Training. He is the author and presenter for Delphi Developer Days (www.DelphiDeveloperDays.com), an information-packed Delphi (TM) seminar series that tours North America and Europe, and Delphi Developer Days Power Workshops, focused Delphi (TM) training. Cary is also an award-winning, best-selling co-author of eighteen books, including Building Kylix Applications (2001, Osborne/McGraw-Hill), Oracle JDeveloper (1999, Oracle Press), JBuilder Essentials (1998, Osborne/McGraw-Hill), and Delphi In Depth (1996, Osborne/McGraw-Hill). For information about onsite training and consulting you can contact Cary at cjensen@jensendatasystems.com, or visit his Web site at www.JensenDataSystems.com.

New!: Stay informed, stay in touch. Register online to receive the free Developer Days ELetter: information, observations, and events for the Delphi and .NET developer by Cary Jensen. Each Developer Days ELetter includes Delphi tips and tricks, .NET information, links to recent articles posted to the Borland Developers Network site, and events in the Delphi community. Visit www.DeveloperDays.com.