Quick Data Access: The Data Reader

 
Chapter 9 - Data Access with .NET
bySimon Robinsonet al.
Wrox Press 2002
  

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 a SqlCommand or OleDbCommand object having called the ExecuteReader() method from a SqlCommand object, a SqlDataReader object is returned, and from the OleDbCommand object, a OleDbDataReader object is returned.

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, so for instance 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.

The code for this example can be found in the Chapter 09\03_DataReader directory.

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

   using System;     using System.Data.OleDb;   

All the data providers currently available are shipped within the same DLL, so it is only necessary to reference the System.Data.dll assembly to import all classes used in this section:

   public class DataReaderExample     {     public static void Main(string[] args)     {     string source = "Provider=SQLOLEDB;" +     "server=(local)\NetSDK;" +     "uid=QSUser;pwd=QSPassword;" +     "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 {1}" ,     aReader.GetString(0) , aReader.GetString(1));     aReader.Close();     conn.Close();     }     }   

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

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

The following code from the example above 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 then 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 directly instantiated 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];     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 I wrote a simple test application 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. I know 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.

Just for interest, 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 presented above, the 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.

My 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 code above 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 above, except in this instance I have replaced the OLE DB provider and all references to OLE DB classes with their 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)\NetSDK;" +     "uid=QSUser;pwd=QSPassword;" +     "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(0) ,                             aReader.GetString(1));       aReader.Close();       conn.Close();       return 0;    } } 

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

I ran the same performance tests 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 I tested this section under the release version of .NET it was. I'm reasonably sure that this is an anomaly due to the simplistic test approach I am using (selecting the same value 1,000,000 times), and would expect a real-world test to show better performance from the managed SQL provider.

If you are interested in running the code on your own computer to see what performance is like, see the 05_IndexerTestingOleDb and 06_IndexerTestingSql examples included in the code download.

  


Professional C#. 2nd Edition
Performance Consulting: A Practical Guide for HR and Learning Professionals
ISBN: 1576754359
EAN: 2147483647
Year: 2002
Pages: 244

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net