This article begins with a general overview ADO.NET, with particular attention to the capabilities defined by the .NET data provider interface. It continues with a look at how two database publishers, Microsoft and Sybase iAnywhere, have extended one of these ADO.NET classes, DbDataReader, to support the particular capabilities of their respective servers.
Overview of ADO.NET
ADO.NET refers to the interfaces and classes used for data access in the .NET framework. In its initial release, interfaces provided the application programming interface (API) for ADO.NET. Since the release of ADO.NET 2.0, abstract base classes have defined this API. Figure 1 depicts a simplified diagram of the ADO.NET architecture as it has remained since ADO.NET 2.0.
Figure 1: A simplified diagram of the core ADO.NET architecture
As depicted in Figure 1, there are two distinct categories of classes in ADO.NET. Those shown on the left side of this figure represent the data access classes. These classes, which are defined by abstract classes in the System.Data.Common namespace, represent the API for .NET data providers, classes you use to read and write data from your underlying database.
Since these classes are abstract, you cannot instantiate them to access your data. Instead, you create concrete descendants of these classes. These concrete classes, referred to as .NET data providers, come from a variety of sources, including (but certainly not exclusively) from the .NET framework class library (FCL) itself.
By comparison, the classes on the right side of Figure 1 are the data storage classes of ADO.NET, often referred to as the “DataSet classes”. These classes are concrete classes defined in the System.Data namespace that you use to read and write data, identify or define your data’s metadata, define relationships between related tables of data, and persist or restore your data from some external source, such as a file or service.
While nearly all ADO.NET technologies use the data storage classes shown on the right side of Figure 1, the same cannot be said with respect to concrete implementations of the .NET data providers. Specifically, most developers use instances of the DataTable, DataRow, DataSet, DataView, and DataColumn classes in their applications. By comparison, most developers prefer to use the .NET data provider implementation offered by the publisher of their database.
Why the two areas of ADO.NET are handled differently is both interesting and illuminating. As far as the data storage classes go, in most cases they are more than adequate for iterating through results and reading and writing data.
By comparison, the .NET data provider interfaces represent generic data access operations, such as opening connections, initiating transactions, executing queries, and retrieving result sets. By its very nature, however, an API says little about the specifics of how these operations are performed, instead providing only a consistent template for operations that need to be offered.
But there is more, and this is one of the great strengths of the ADO.NET architecture. While the .NET data provider API explicitly defines the operations concrete implementation should support, it imposes almost no limits on what a specific implementation can do. This extensibility permits database publishers to provide added value through their custom .NET data providers.
In the following section we will look at the roles of the core classes of the ADO.NET architecture. The article concludes by looking at the flexibility of this architecture by considering how two .NET data providers expose specific features of their database through the DbDataReader API.
The Core Classes Implemented by ADO.NET Data Providers
There are six primary abstract classes, as far as the .NET data provider interface is concerned. These are DbConnection, DbTransaction, DbCommand, DbParameter, DbDataReader, and DbDataAdapter. The following is a brief description of each of these classes.The DbConnection class defines the basic interface for connecting to a data source. In most cases this will be a database associated with a particular database server.
Methods associated with DbConnection include Open, CreateCommand, and Close. In addition, DbConnection includes the BeginTransaction method, which returns a DbTransaction instances. You use the returned DbTransaction to provide all-or-none updates to your underlying data, committing or rolling back any changes made to your database through the DbConnection since the call to create the DbTransaction.
You use the DbCommand class to execute queries against your database through a DbConnection. DbCommands must be associated with a DbConnection before you can execute a query. In fact, you can either create a DbCommand instance and then associated it with a DbConnection, or you can call the DbConnection CreateCommand method, which creates a DbCommand that is already associated with the corresponding DbConnection.
All queries, regardless of how they are executed using ADO.NET, are performed using DbCommands (even the DbDataAdapter class, which appears to be able to execute queries on its own, employs DbCommand objects for this purpose).
The DbCommand interface provides methods for executing three distinct types of queries. These include those that do not return a result set (ExecuteNonQuery), those that return a single value (ExecuteScalar), and those that return zero or more records of a result set. This final type of query, that which returns a result set, requires the use of one of two other .NET data provider classes: DbDataReader or DbDataAdapter.
Regardless of the type of query being executed by a DbCommand, those queries can include one or more parameters. Those parameters are represented by concrete implementations of the DbParameter class. Depending on the particular .NET data provider you are using, parameters can either be named parameters, positional parameters, or both.
Returning to the classes that you use to work with the result set returned by a DbCommand, the DbDataReader class provides a cursor to a result set. In the .NET data provider API, this data reader is a readonly, forward navigating the cursor to the records of the result set. These types of classes tend to be both fast and ideal for either simply reading data or for populating visual controls, such as listboxes and gridviews, with values from a query result.
Unlike the DbDataReader, a DbDataAdapter is not used to read data directly. Instead, this class serves as an intermediate class, providing a means for both loading data from a result set into a DataTable, which is an in-memory dataset, as well as resolving a DataTable’s changes back to the underlying database server.
The second feature, being able to resolve a DataTable’s changes, employs the DataTable’s change log, a record of the inserts, updates, and deletions that have been applied to this in-memory data. Persisting these changes through a DbDataAdapter employs the services of parameterised queries, which are represented by none other than DbCommand instances used for this purpose by the DbDataAdapter class.
Extending the .NET Data Provider Interface
As mentioned earlier, classes of a particular .NET data provider are free to extend the interfaces defined in the abstract base classes. In this section we take a look at two concrete implementations of the DbDataReader abstract base classes.The first example of a DbDataReader implementation is part of the .NET FCL. This is the SqlDataReader class, part of the MS SqlServer .NET data provider, and found in the System.Data.SqlClient namespace.
The SqlDataReader class can be populated asynchronously. This feature is implemented through the BeginExecuteReader method of the SqlCommand class. Most overloads of this method take a callback function, which is executed once the query that populates the SqlDataReader has completed its execution.
The point being made here is that the DbCommand class does not have a BeginExecuteReader method. This method is introduced by the SqlCommand class, thereby extending the DbCommand interface.
This feature is demonstrated through the following three C# code segments, which were inspired in part from an example found in the Microsoft .NET Framework 2.0 SDK. The first segment represents the basic partial class declaration of a Windows Forms application. In this segment you will find two delegate declarations, two variables, the form’s constructor, and two methods, ShowMessage and LoadList. ShowMessage is used to display messages during the execution of this code, and LoadList is used to populate a listbox control from the callback function (shown later in this article). The two delegates are used to invoke ShowMessage and LoadList from the callback function, which is necessary since that code is not guaranteed to execute in the form’s thread:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Advantage.Data.Provider;
public partial class Form1 : Form
{
private delegate void
LoadListDelegate(
SqlDataReader sqlDataReader,
SqlConnection sqlConnection);
private delegate void
ShowMessageDelegate(String text);
private SqlConnection sqlConnection;
private bool working = false;
public Form1()
{
InitializeComponent();
}
private void ShowMessage(String text)
{
this.SqlDataReaderFeedbackLabel.
Text = text;
}
private void LoadList(SqlDataReader
sqlDataReader, SqlConnection
sqlConnection)
{
try
{
while (sqlDataReader.Read())
{
this.listBox1.Items.Add(
sqlDataReader.GetString(0));
}
}
catch (Exception ex)
{
// Because you are guaranteed this
// procedure is running from within the
// form's thread, it can directly
// interact with members of the form.
ShowMessage(ex.Message);
}
finally
{
// Do not forget to close the connection
if (sqlDataReader != null)
{
sqlDataReader.Close();
}
if (sqlConnection != null)
{
sqlConnection.Close();
}
}
}
//Additional methods here
}
The next code segment demonstrates the execution of the asynchronous query.
After ensuring that the query isn’t currently executing, a connection is made to SqlServer, which is used to create the SqlCommand with which BeginExecuteReader is invoked.
In this example, this method is passed two parameters, the callback function and a reference to the SqlCommand used to execute the query. This SqlCommand is used to close the SqlConnection when the code is through with the SqlDataReader. The query, in this example, is a Sql script, which begins with the WAITFOR DELAY command. This is included in the query to emulate a query that takes at least 3 seconds to execute, in order to emphasise the asynchronous nature of the query execution:
private void LoadListButton_Click(
object sender, EventArgs e)
{
if (working)
{
ShowMessage("Already executing.
Try again later");
}
else
{
try
{
if (this.listBox1.Items.Count > 0)
{
this.listBox1.Items.Clear();
};
ShowMessage("Connecting to
database");
sqlConnection =
new SqlConnection();
// Connection string with
// Asynchronous Processing=true
sqlConnection.ConnectionString =
"Data Source=.;Initial
Catalog=Northwind;" +
"Integrated Security=True;
Asynchronous Processing=true";
sqlConnection.Open();
SqlCommand sqlCommand =
sqlConnection.CreateCommand();
ShowMessage("Prepaing query...");
sqlCommand.CommandText =
"WAITFOR DELAY '0:0:3';" +
"SELECT CompanyName FROM
Customers";
working = true;
// Pass the SqlCommand as the
// second parameter so that it
// is available from the callback
// handler
sqlCommand.BeginExecuteReader(new
AsyncCallback(CallbackHandler),
sqlCommand);
ShowMessage("Executing query...");
}
catch (Exception ex)
{
ShowMessage("Error: " +
ex.Message);
}
}
}
The final code segment contains the callback function.
Here the SqlCommand is retrieved from the IAsyncResult.AsyncState, and the SqlDataReader is retrieved through the SqlCommand.EndExecuteReader method. The delegates are then used to execute the LoadList and ShowMessage methods.
As seen in the preceding code segment, the LoadList method takes a SqlConnection parameter, which LoadList uses to close the connection once the listbox has been populated:
private void CallbackHandler(
IAsyncResult theResult)
{
try
{
SqlCommand sqlCommand =
(SqlCommand)theResult.AsyncState;
SqlDataReader sqlDataReader =
sqlCommand.EndExecuteReader(
theResult);
// This code is executing from a thread,
// and therefore cannot access the UI.
// Use delegates to load the data and
// display the main thread.
this.Invoke(new LoadListDelegate(
this.LoadList), new Object[]
{sqlDataReader,
sqlCommand.Connection});
this.Invoke(new
ShowMessageDelegate(ShowMessage), "Query complete");
}
catch (Exception ex)
{
// Cannot access the UI for the reason given above.
// Use a delegate.
this.Invoke(new ShowMessageDelegate(ShowMessage),
"Error: " + ex.Message);
}
finally
{
working = false;
}
}
The next example of how a particular .NET data provider extends ADO.NET comes from the Advantage .NET Data Provider, which is published by Sybase iAnywhere for the Advantage Database Server. The Advantage Database Server is unique in a number of ways, one of them being that the underlying architecture of Advantage is based on indexed sequential access method (ISAM) technology.
While ISAM databases are often associated with file server based databases, such as dBase and Clipper, the Advantage Database Server is a high performance, remote, transaction supporting, SQL-enabled database that is popular with vertical market developers due to its near zero maintenance requirements.
One of the intriguing benefits of the ISAM architecture as it is implemented by Advantage, one that traditional SQL servers do not share, it its support for index-optimised, server-side cursors. The Advantage .NET Data Provider leverages this capability through the AdsExtendedReader class, which is a DbDataReader descendant.
While the DbDataReader interface supports a readonly, forward navigating cursor, the AdsExtendedReader implements a read/write, bi-directional, index-optimised, server-side cursor to a query result set. Naturally, a read/write cursor is only possible if the query selects records from a single table, and does not include expressions or aggregate operations.
Some of the advanced capabilities of the AdsExtendedReader class are demonstrated in the following code segment, which is a second event handler in the previously shown form’s partial class. This code begins by connecting to a data table that ships with the Advantage Data Architect help files. (The Advantage Data Architect is a free, graphic interface for configuring Advantage databases.)
This AdsConnection is used to create an AdsCommand, which in turn is used to return an AdsExtendedReader. The AdsExtendedReader is then used to perform a number of operations, each of which is documented and confirmed by comments written to a second listbox on the Windows Form.
private void ExecuteExtendedReader_Click(
object sender, EventArgs e)
{
AdsConnection adsConnection =
new AdsConnection();
adsConnection.ConnectionString =
"Data Source=C:\\Program Files\\" +
"Advantage 9.10\\Help\\ADS_DATA;
ServerType = REMOTE | LOCAL";
adsConnection.Open();
try
{
AdsCommand adsCommand =
adsConnection.CreateCommand();
adsCommand.CommandText = "SELECT * FROM CUSTOMER";
AdsExtendedReader adsExtendedReader =
adsCommand.ExecuteExtendedReader();
if (adsExtendedReader.Read())
{
try
{
listBox2.Items.Add(
"Write two records based on
natural order");
listBox2.Items.Add(
adsExtendedReader.GetString(
1));
adsExtendedReader.Read();
listBox2.Items.Add(
adsExtendedReader.GetString(
1));
listBox2.Items.Add(
"Navigate back one record");
adsExtendedReader.
ReadPrevious();
listBox2.Items.Add(
adsExtendedReader.GetString(
1));
listBox2.Items.Add(
"Select the BYCOMPANY index");
adsExtendedReader.ActiveIndex =
"BYCOMPANY";
listBox2.Items.Add(
"Move to the first record of
this index");
adsExtendedReader.GotoTop();
listBox2.Items.Add(
adsExtendedReader.GetString(
1));
listBox2.Items.Add(
"Use an indexed Seek to find
Unisco");
if (adsExtendedReader.Seek(
new Object[] {"Unisco"},
AdsExtendedReader.SeekType.
HardSeek))
{
listBox2.Items.Add(
"Record found");
listBox2.Items.Add(
adsExtendedReader.
GetString(1));
}
listBox2.Items.Add(
"Insert a new record");
adsExtendedReader.
AppendRecord();
adsExtendedReader.SetInt32(
0, 2222);
adsExtendedReader.SetString(
1, "New Sample Company");
adsExtendedReader.WriteRecord();
listBox2.Items.Add(
"Record inserted");
listBox2.Items.Add("Switch to
the PRIMARY index");
adsExtendedReader.ActiveIndex =
"PRIMARY";
adsExtendedReader.GotoTop();
listBox2.Items.Add(
"Move to first record of
primary index");
listBox2.Items.Add(
adsExtendedReader.GetString(
1));
listBox2.Items.Add(
"Seek newly added record");
if (adsExtendedReader.Seek(
new Object[] {2222},
AdsExtendedReader.SeekType.
HardSeek))
{
listBox2.Items.Add(
"Record found");
listBox2.Items.Add(
adsExtendedReader.
GetString(1));
listBox2.Items.Add(
"Deleting added record");
adsExtendedReader.
DeleteRecord();
listBox2.Items.Add(
"Seek deleted record");
if (! adsExtendedReader.Seek(
new Object[] {2222},
AdsExtendedReader.SeekType.
HardSeek))
{
listBox2.Items.Add(
"Deleted record not
found");
}
}
}
finally
{
listBox2.Items.Add(
"All done. Closing the
reader");
adsExtendedReader.Close();
}
}
}
finally
{
adsConnection.Close();
}
}
Figure 2 shows the running application after both of the event handlers have been executed.
Figure 2. The output of the two DbDataReader implementations in a Windows Form
The listbox on the left contains the results of the query that was executed asynchronously, which the listbox on the right contains the output generated as the AdsExtendedReader performed its operations.
Summary
While the .NET provider interface defined by the abstract classes in the System.Data.Common namespace provides a basic template for data access in ADO.NET, individual implementations are free to exploit the strengths of the databases to which they connect.This was demonstrated in this article through the asynchronous capabilities of the MS Sql Server implementations, as well as the index-friendly AdsExtendedReader class from Sybase iAnywhere’s Advantage .NET Data Provider.
Cary Jensen is President of Jensen Data Systems, Inc., a software training and consulting company. Cary has a Ph.D. in Human Factors Psychology from Rice University, specialising in human-computer interaction. He is a co-author of 20 books and a speaker and trainer.
Comments