IDataReader and the CommandBehavior Enumeration

by William Ryan

If you've done much heavy lifting (or any lifting at all) in ADO.NET, you've no doubt used a DataReader.  If you haven't, it's probably time that you should.  Why?  It's very easy to use, has a tiny footprint and in most cases, the Fastest way to retrieve data.

There are some nuances of the DataReader though that you should know about.  Although all of the provider specific DataReaders (SqlDataReader, OleDbDataReader, OracleDataReader, OdbcDataReader) inherit from IDataReader, you'll want to use a provider specific one for maximum performance.  But this is common sense and hardly a telling revelation.  

The first thing you need to realize is that DataReaders operate in a Connected Fashion.  You may have heard that ADO.NET is  a  completely disconnected model, but that's not true.  There are both Connected and Disconnected objects.  Whereas many objects like the DataSet and the DataTable, don't need a connection or a database to function and are made to work in a Connect and Kill mode, you can't do anything with a DataReader without a database connection.  As such, the DataAdapter does all the real work for you in regard to opening and closing your connections.  With a DataReader, you need to do it manually.  When I was first learning ADO.NET, I forgot to open my connections (and worse, close them) more times than I care to admit.  Another nuance is that only one DataReader can be associated with one connection at a time.

Now, as a strategy you want to close your connections as soon as you can, but there are instances where you may want to leave the connection open to span a few consecutive queries because opening and closing the connection a few hundred times when you have back to back queries will cause performance degradation.  So, two things can be 'open' or 'closed', the DataReader and the Connection.  By Closing a connection, you'll close the reader because as I said, a DataReader is useless without a connection.  However, it's good practice to explicitly close the reader (so other people can understand what's going one) and in instances like the one mentioned above, you need to leave the connection open for a few simultaneous operations, so you can't rely on closing the connection to close your reader.  

Well, System.Data provides a CommandBehavior enumeration which allows you to do a few cool things.  The enum conists of the following members:  CloseConnection (32), Default (0), KeyInfo (4), SchemaOnly (2), SequentialAccess (16), SingleResult (1) and SingleRow (8).

The two I'd like to discuss are CloseConnection and SchemaOnly.

Take the examples below:

VB.NET

Dim rdr As SqlDataReader Dim cmd As New SqlCommand("SELECT Facility FROM Tbl_Facilities", cn)    Try        If cn.State <> ConnectionState.Open Then cn.Open()    Catch ex As SqlException        Debug.Assert(False, ex.ToString)    End Try    Try        rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)        While rdr.Read            Console.WriteLine(rdr.GetString(0))        End While        rdr.Close()        Console.WriteLine(cn.State.ToString) 'Closed    Catch ex As Exception        Debug.Assert(False, ex.ToString) End Try

C#

SqlDataReader reader; SqlCommand cmd = new SqlCommand("SELECT Facility FROM Tbl_Facilities", cn); try {    if(cn.State != ConnectionState.Open){cn.Open();} } catch(SqlException ex) {     Debug.Assert(false, ex.ToString); } try {     reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); } catch(System.Exception ex) {     Debug.Assert(false, ex.ToString()); } while(reader.Read()) {        Console.WriteLine(reader.GetString(0)); } reader.Close; Console.WriteLine(cn.State);//Closed }

What CommandBehavior.CloseConnection does is close the underlying connection whenever the reader is closed.  So, if we didn't specify a CommandBehavior, we'd use the Default and the ConnectionState would be open when we tested it.  This may seem like a inconsequential feature suited to lazy people, but it's easy to forget to close those connections, and thereby waste resources that may be quite scarce.

The next feature I'd like to mention is SchemaOnly.  I've written another article that discusses strategies for retrieving Schema information, but this is an additional method that you may find interesting.  I've only included three of the possible values you can discover, but I encourage you to play with it some and find out about the others...


VB.NET

Dim rdr As SqlDataReader Dim cmd As New SqlCommand("SELECT * FROM Tbl_Facilities", cn)    Try        If cn.State <> ConnectionState.Open Then cn.Open()    Catch ex As SqlException        Debug.Assert(False, ex.ToString)    End Try    Try        rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly)        Dim dt As DataTable = rdr.GetSchemaTable        Debug.WriteLine("Column Name: " & dt.Rows(0).Item(0).ToString)        Debug.WriteLine("Column Type: " & dt.Rows(0).Item(1).ToString)        Debug.WriteLine("Column Size: " & dt.Rows(0).Item(2).ToString)        rdr.Close()        cn.Close()         Console.WriteLine(cn.State.ToString) 'Closed    Catch ex As Exception        Debug.Assert(False, ex.ToString) End Try

C#

SqlDataReader reader; SqlCommand cmd = new SqlCommand("SELECT Facility FROM Tbl_Facilities", cn); try {    if(cn.State != ConnectionState.Open){cn.Open();} } catch(SqlException ex) {     Debug.Assert(false, ex.ToString()); } try {     reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly); } catch(System.Exception ex) {     Debug.Assert(false, ex.ToString()); } DataTable dt = reader.GetSchemaTable(); Debug.WriteLine("Column Name: " + dt.Rows[0][0].ToString()); Debug.WriteLine("Column Type: " + dt.Rows[0][1].ToString()); Debug.WriteLine("Column Size: " + dt.Rows[0][2].ToString()); reader.Close(); cn.Close(); Console.WriteLine(cn.State);//Closed

The Output of the following code is provided below: Column Name: Facility Column Type: 0 'Where 0 is an integer corresponding to NVarchar Column Size: 50