his article benchmarks a few different ways to access data using ADO.NET. Hopefully, after seeing
the results of these tests, you will have a better idea concerning what approach to take when
accessing data with your application. The intention of this article is not to write the documentation
for ADO.NET. Microsoft has already written helpful documentation for ADO.NET (look in the PRC1 or
newer help files). We will, however, take a brief look at the overlying structure of ADO.NET.
A Brief Primer on ADO.NET
ADO.NET provides a new architecture for accessing and manipulating data. It is entirely different
than the ADO of the VB6 era. With it, we have new ways to access and manipulate data.
The Managed Provider and the DataSet provide the core functionality
1. The Managed Provider.
The Managed Provider supplies the following four classes.
The connection and command classes are similar to those found in previous versions of ADO. Objects
derived from the connection class are used to connect to data sources. Objects derived from the
command class are used to run stored procedures, establish parameter information, modify and return
The DataReader class provides read-only and forward-only access to the data source. We will benchmark
this object later. The final class of the Managed Provider component is the DataAdapter class. The
DataAdapter is the channel through which our second component, the DataSet, connects
to the Managed Provider.
2. The DataSet
The DataSet class consists of in-memory DataTables, columns, rows, constraints and relations. The
DataSet provides an interface to establish relationships between tables, retrieve and update data.
Perhaps one of the most important benefits of the DataSet is its ability to be synchronized with an
XmlDataDocument. The DataSet provides real-time hierarchical access to the XmlDataDocument object.
Managed Providers currently come in two flavors. Each is represented by its own namespace in the .NET
Framework. The first, System.Data.SQLClient provides access to SQL Server 7.0 and
higher databases. The second Managed Provider is found within the System.Data.OleDb
namespace, and is used to access any OleDb source. The SQLClient Provider takes advantage of
Microsoft SQL Server's wire format (TDS), and therefore should give better performance than accessing
data through the OleDb provider. The performance tests done later in this article will be performed
for both provider types.
If we were to create a diagram explaining the general structure of ADO.NET, it would look like this.
In the diagram above, two ways exist to retrieve data from a data source. The first is through the
DataReader class in the Managed Provider component. The second is through the DataSet, which accesses
the data source through the DataAdapter class of the Managed Provider.
The robust Dataset object gives the programmer the ability to perform functions such as establishing
relationships between tables. The DataReader provides read-only and forward-only retrieval of data.
The tests uncover what performance loss, if any, we encounter when using the dataset rather than the