Fast Data Access: The Data Reader

A data reader is the simplest and fastest way of selecting some data from a data source, but 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 utilizes 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 below 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 to reference the System.Data.dll assembly to import all classes used in this section. The only exception is the Oracle classes, which reside in System.Data.Oracle.dll.

 public class DataReaderExample { public static void Main(string[] args) { string source = "Provider=SQLOLEDB;" + "server=(local);" + "integrated security=SSPI;" + "database=northwind"; string select = "SELECT ContactName,CompanyName FROM Customers"; OleDbConnection conn = new OleDbConnection(source); conn.Open(); OleDbCommand cmd = new OleDbCommand(select , conn); OleDbDataReader aReader = cmd.ExecuteReader(); while(aReader.Read()) Console.WriteLine("'{0}' from aReader.GetString(0) , aReader.GetString(1)); aReader.Close(); conn.Close(); } } 

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

 csc /t:exe /debug+ DataReaderExample.cs /r:System.Data.dll 

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

 OleDbConnection conn = new OleDbConnection(source);  conn.Open(); OleDbCommand cmd = new OleDbCommand(select, conn); 

The third line creates a new OleDbCommand object, based on a particular SELECT statement, 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 through the records returned in one direction, and the database connection used is kept open until the data reader has been closed.

Important

An OleDbDataReader keeps the database connection open until 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 OleDbDataReader 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 CommandBehavior.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 o = aReader[0]; or object o = 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, and you might as well write code that is as close to 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 GetInt32, GetFloat, GetGuid, and so on.

The million iterations using GetInt32 took 0.06 seconds. The overhead in the numeric indexer is incurred while getting the data type, calling the same code as GetInt32, 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 you 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 ten-fold speed increase over using a textual column name (when selecting those million copies of the same column).

Needless 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 Server–specific classes that can be used with the obvious portability tradeoff.

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

using System; using System.Data.SqlClient; public class DataReaderSql {    public static int Main(string[] args)    { string source = "server=(local);" + "integrated security=SSPI;" + "database=northwind";       string select = "SELECT ContactName,CompanyName FROM Customers"; SqlConnection conn = new SqlConnection(source);       conn.Open(); SqlCommand cmd = new SqlCommand(select , conn); SqlDataReader aReader = cmd.ExecuteReader();       while(aReader.Read())          Console.WriteLine("'{0}' from {1}"                             aReader.GetString(1));       aReader.Close();       conn.Close();       return 0;    } }

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 numeric indexers were both exactly the same at 0.13 seconds for the million accesses, and the string-based indexer ran at about 0.65 seconds. You would expect the native SQL Server provider to be faster than going through OleDb, which up until this was tested under the release version of .NET it was. This might be an anomaly due to the simplistic test approach used in the example (selecting the same value 1,000,000 times); a real-world test should show better performance from the managed SQL provider.