Fast Data Access: The Data Reader C# Help

A data reader is the simplest and fastest way of selecting some data from a data source, but it is also the least capable, You cannot directly instantiate a data reader object – an instance is returned from the appropriate database’s command object (such as SqlCommand) after having called the ExecuteReader () method.

The following code demonstrates how to select data from the Customers table in the Northwind database. The example connects to the database, selects a number of records, loops through these selected records, and outputs them to the console.

This example uses the OLE DB provider as a brief respite from the SQL provider, In most cases, the classes have a one-to-one correspondence with their SqlClient cousins; for example, there is the OleDbConnection object, which is similar to the SqlConnection object used in the previous examples.

To execute commands against an OLE DB data source, the OleDbCommand class is used, The following code shows an example of executing a simple SQL statement and reading the records by returning an OleDbDataReader object.

Note the second using directive that makes available the OleDb classes:

using System;
using System.Data.OleDb;

Most of the data providers currently available are shipped within the same assembly, so it is only necessary i;reference the System.Data.dll assembly to import all classes used in this section. The only exceptions are the Oracle classes, which reside in System, Data. Oracle.dll.

The preceding code includes many familiar aspects of C# already covered in this chapter. To compile the example, issue the following command:

csc It:exe Idebug+ DataReaderExaMple.cs/r:Systern.Data.dll

The following code from the previous example creates a new OLE DB .NET database connection, based on the source connection string:

The third line creates a new OleDbComrnand object, based on a particular SELECTstatement, and the database connection to be used when the command is executed. When you have a valid command, you need to execute it, which returns an initialized OleDbDataReader:

OleDbDataReader aReader = cmd.ExecuteReader();

An OleDbDataReader is a forward-only “connected” cursor, In other words, you can only traverse the records returned in one direction, and the database connection used is kept open until the data reader has been closed.

An OleDbDataReader keeps the database connection open until it is explicitly closed.

The OleDbDataReader class cannot be instantiated directly – it is always returned by a call to the ExecuteReader () method of the OleDbCommand class, Once you have an open data reader, there are various ways to access the data contained within the reader.

When the OeDbDataReader object is closed (via an explicit call to Close (), or the object being garbage collected), the underlying connection may also be closed, depending on which of the ExecuteReader () methods is called, If you call ExecuteReader ( ) and pass ComrnandBehavior, CloseConnection, you can force the connection to be closed when the reader is closed.

The OleDbDataReader class has an indexer that permits access (although not type-safe access) to any field using the familiar array style syntax:

object 0 aReader[O];
or
object 0 aReader[“CategoryID”);

Assuming that the CategoryID field was the first in the SELECT statement used to populate the reader, these two lines are functionally equivalent, although the second is slower than the first; to verify this, a test application was written that performed a million iterations of accessing the same column from an open data reader, just to get some numbers that were big enough to read. You probably don’t read the same column a million times in a tight loop, but every (micro) second counts, so you should write code that is as ‘optimal as possible.

As an aside, the numeric indexer took on average 0.09 seconds for the million accesses, and the textual one 0.63 seconds, The reason for this difference is that the textual method looks up the column number internally from the schema and then accesses it using its ordinal. If you know this information beforehand you can do a better job of accessing the data.

So, should you use the numeric indexer? Maybe, but there is a better way.

In addition to the indexers just presented; OleDbDataReader has a set of type-safe methods that can be used to read columns, These are fairly self-explanatory, and all begin with Get, There are methods to read most types of data, such as Getlnt32, GetFloat, GetGuid, and so on.

The million iterations using Getlnt32 took 0.06 seconds, The overhead in the numeric indexer is incurred while getting the data type, calling the same code as Getlnt32, then boxing (and in this instance unboxing) an integer So, if you know the schema beforehand, are willing to use cryptic numbers instead of column names, and can be bothered to use a type-safe function for each and every column access, you stand to gain somewhere in the region of a tenfold speed increase over using a textual column name (when selecting those million copies of the same column).

eed1ess to say, there is a tradeoff between maintainability and speed. If you must use numeric indexers, define constants within class scope for each of the columns that you will be accessing, The preceding code can be used to select data from any OLE DB database; however, there are a number of SQL Serverspecific classes that can be used with the obvious portability tradeoff. “

The following example is the same as the previous one, except that in this instance the OLE DB provider and all references to OLE DB classes have been replaced with their SQL counterparts. The example is in the 04_DataReaderSql directory:

Notice the difference? If you’re typing this, do a global replace on OleDb with Sql, change the data source string and recompile, It’s that easy!

The same performance tests were run on the indexers for the SQL provider, and this time the numericrs were both exactly the same at 0.13 seconds for the million accesses, and indexer ran at about 0.65 seconds.