Up until recently, nearly all data access in Borland's RAD products was supplied by the Borland Database Engine. Beginning with Delphi 3, additional data access mechanisms, including ClientDataSet and DataSnap (originally known as MIDAS) were added. As of Delphi 7, Delphi natively supports no less than 6 distinct data access mechanisms (not counting third-party solutions). The most recent of these, and arguably most important for developers as .NET continues to evolve, is the support for ADO.NET provided by Borland's new .NET development tools, including C# Builder and the Delphi for .NET preview compiler.

This article is designed to provide you with an overview of ADO.NET's data access mechanism, as it is defined in the .NET 1.1 Framework. The next article in this series will continue this discussion with a detailed look at the data storage classes, including the DataSet class.

Throughout this series there are a couple of points that you should keep in mind. First, these articles, and the accompanying code (when appropriate), are based on the Delphi 7 preview compiler (update version 3, first made available in Spring 2003). At the time of this writing, Borland does not yet have an integrated development environment (IDE) for Delphi for .NET. Consequently, the example projects were not built using visual design tools. Instead, all visual components (when a project calls for them) were created and configured entirely in code.

The second point is that Borland developers may not end up using ADO.NET using the specific techniques described in this series. It is likely that Borland will provide some sort of .NET ClientDataSet that will greatly simplify your use of data in managed assemblies, just as the VCL's ADODataSet classes simplify the use of ADO (activeX data object). However, if Borland develops this .NET ClientDataSet using an approach similar to its ADODataSet classes, it is likely that you will have the option of using both the ClientDataSet interface, as well as the more low-level techniques described in this series.

ADO.NET: Data Access and Data Storage

ADO.NET is the name for the data layer provided by the .NET framework class library (FCL). Conceptually, ADO.NET can be divided into two distinct parts: the data access mechanism and the data storage system.

All of the principle classes, interfaces, and types used in ADO.NET are defined under the System.Data second-level namespace. The classes associated with the data storage system are standalone classes that you can employ in any ADO.NET application. These classes include DataColumn, DataRelation, DataRow, DataSet, and DataTable. Of these, the most important is the DataSet class.

Unlike the storage mechanism, which is defined around classes, the data access mechanism is defined around interfaces. These interfaces are implemented by concrete classes associated with a particular data access mechanism. In the .Net Framework version 1.1 there are five data access mechanisms. These are associated with the System.Data.SqlClient, System.Data.OleDb, System.Data.Odbc, System.Data.SqlServerCE and System.Data.OracleClient third-level namespaces. 

Consider the SqlDataAdapter class in the System.Data.SqlClient namespace, for example. This class, which is sometimes generically called a DataAdapter, is used to load data into a DataSet. SqlDataAdapter, like all other DataAdapters, implements the IDbDataAdapter interface. Other IDbDataAdapter implementing classes include OleDbDataAdapter, OdbcDataAdapter, and OracleDataAdapter.

It is worth noting that many database vendors have developed, or are in the process of developing, their own specific data access mechanisms. As a result, even though one of the 1.1 framework data access mechanisms will probably provide you with access to these databases, the vendor supplied data access classes are likely to be optimized for the particular server.

ADO.NET Data Access Interfaces

The following diagram depicts the data access layer in the ADO.NET framework, and its relationship to the DataSet class. As you can see here, the classes that implement the data access interfaces are designed to cooperate in accessing data from an underlying database, with the IDbDataAdapter implementing class being used to fill a DataSet instance with this acquired data.

That data access in the .NET framework is designed around interfaces is significant, in that it provides for the API of data access while leaving the specific details to the implementing classes. In the .NET framework 1.1, each of these interfaces are implemented by a class in one of the data access mechanism-specific namespaces. For example, if you are going to access data using SQL server, you can use the SqlConnection, SqlCommand, SqlDataReader, and SqlDataAdapters classes. On the other hand, if you want to access data using an OleDb provider, you can use the corresponding classes from the System.Data.OleDb namespace, which are OleDbConnection, OleDbCommand, OleDbDataReader, and OleDbDataAdapter classes, respectively.

The following sections describe the common data access interfaces in the preceding figure. For the benefit of current Borland RAD developers, the similarity between each of these interfaces and their VCL equivalent is discussed.

IDbConnection

Classes that implement the IDbConnection interface are used to establish a connection to a data source. In most cases, this will be a database associated with a particular database server. However, since both OleDB and ODBC are supported, it is possible to connect to any data source for which there are OleDb provider or ODBC (open database connectivity) drivers, such as Paradox, dBase, and MS Access.

Classes that implement the other interfaces listed in the preceding diagram all rely on the connection supplied by an IDbConnection implementing class. However, if you are using a DataSet that does not load or resolve its data to an underlying database (such as a DataSet that obtains its data from an XML file), you do not need to use a class that implements the IDbConnection interface.

Each IDbConnection implementing class has one or more constructors that are used to establish the connection to the data source. This interface also provides for control over database transactions.

The IDbConnection interface defines features that are similar to those provided by the SQLConnection component of dbExpress, ADOConnection of ADO, or the TSession component of the BDE.

IDbCommand

Classes that implement the IDbCommand interface are used to define and execute queries against the underlying database. A class that implements the IDbCommand interface must be associated with a class that implements the IDbConnection interface. 

IDbCommand queries may include parameters. When they do, you bind data to individual parameters using a class that implements the IDbDataParameter interface. All parameters associated with an IDbCommand implementing class are found in the Parameters property of the implementing class. This property is an instance of a class that implements IDataParameterCollection.

Classes that implement the IDbCommand interface are similar to the VCL's ADOCommand component.

IDataReader

Classes that implement the IDataReader class provide a fast, forward-only cursor to the result set returned by an IDbCommand instance. Classes that implement this interface are used to perform operations that do not require the caching of the data. For example, operations where you do not need to load the data into a DataTable, or operations that iterate through a result set, performing a conditional calculation on each record encountered. Classes that implement the IDataReader interface are similar to the SQLDataSet class in dbExpress. 

IDbDataAdapter

You use a class that implements the IDbDataAdapter interface to read data and subsequently load it into a DataSet. Classes that implement the IDbDataAdapter interface must point to an IDbConnection implementing class in order to access the data. In addition to loading data into a DataSet, IDbDataAdapters define the queries that are used to load data from, and save data to, an underlying database. In many ways, IDbDataAdapter implementing objects are similar to Delphi's DataSetProvider classes.

IDbDataAdapter implementing classes require at least one SQL statement, and in many cases as many as four. At a minimum, an IDbDataAdapter implementing class requires a SQL SELECT statement to identify which records to load. If the data accessed by an IDbDataAdapter implementing class can be modified, inserted, or deleted, corresponding SQL statements that specify how this data is to be updated, inserted, and deleted are also required.

An IDbDataAdapter's SQL statements are associated with the SelelectCommand, InsertCommand, UpdateCommand, and DeleteCommand properties of the IDbDataAdapter implementer, which are instances of classes that implement IDbCommand. All of the IDbDataAdapter implementing classes can take a SQL SELECT statement as a parameter of at least one of their overloaded constructors. When one of these constructors is used to create the IDbDataAdapter instance, it is not necessary to explicitly assign an IDbCommand implementing class to the SelectCommand property.

Instead of explicitly assigning SQL statements to the InsertCommand, UpdateCommand, and DeleteCommand properties, you can use an appropriate command builder class to generate the associated SQL commands. Examples of these classes include the SQLCommandBuilder and OleDbCommandBuilder classes.

In the next article in this series I will take a look at the classes that participate in the storage of 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.

Copyright ) 2003 Cary Jensen, Jensen Data Systems, Inc.
ALL RIGHTS RESERVED. NO PART OF THIS DOCUMENT CAN BE COPIED IN ANY FORM WITHOUT THE EXPRESS, WRITTEN CONSENT OF THE AUTHOR.