How to use SqlDataReader Plus Source Code

SqlDataReader can be used if you want to keep an open connection to the database and use the rows as they come to you (forward only stream of data). 
When you use SqlHelper.ExecuteDataSet, what happens is it serializes the data to XML and returns it to you at the end of
the GET operation.  However, with SqlDataReader, you get the rows as they come, which can be handy if there is a lot of
rows (say, millions), or if you have some sort of parallel processing you want to do while the data is still coming.  

Here is an example of how to use SqlDataReader.  Take a look below for an example, uses both SqlCommand and then
the Microsoft Application Blocks version

Download the source code for the below example

While Microsoft provides you with the Microsoft ApplicationBlock class to make your database-driven application development easier, occasionally, you may have to use .NET’s built-in SqlReader class to connect to a database. 

Using SqlReader is quite simple–you feed it a connection string and a query, then pull fields and use readerInstance.Read() to advance row-by-row. 

First, set up your application with the appropriate settings, including the connection to the database: 

string connectionString = "...";

        string query = "..."; 

        SqlConnection conn = new SqlConnection(connectionString); 

        try

        {

            conn.Open();

        }

        catch (Exception ex)

        {

            MessageBox.Show("Couldn't open a connection to the database.  The exception is " + ex.ToString());

            conn.Close();

        }

(Fill in the appropriate query and connection string information–which can be from your configuration, decrypted from some value, etc.) 

Next, create your SqlReader instance: 

SqlDataReader reader;

        using (reader = new SqlCommand(query, conn).ExecuteReader())

        {

            if (reader.HasRows)

            {

(We use using so that the reader is automatically disposed of when we’re done with it.)  It’s important to check if you have any rows of data, so do that before you pull anything out. 

Then, advance row-by-row with reader.Read(), like so: 

while (reader.Read())

{

    string id = reader.GetString(reader.GetOrdinal("Id"));

    string firstName = reader.GetString(reader.GetOrdinal("FirstName"));

    string lastName = reader.GetString(reader.GetOrdinal("LastName"));

    // Do something interesting, albeit trivial

    MessageBox.Show(string.Format("[{0}] {1} {2}", id, firstName, lastName));

    // ... more data-processing code ...

}

SqlReader has several methods you can use to extract different kinds of fields, such as GUIDs, Integers, Strings, Dates, etc.  The methods are called (predictably) GetGuid, GetInt32, GetString, GetDate, etc.  The only thing to note is that all these methods require a column number–so in order to decouple your code from the database (i.e. make sure it doesn’t rely on the order of data), use GetOrdinal(columnName) to get the column ID. 

Finally, close your reader and connection when you’re finished, via reader.Close() and conn.Close(). 

And that’s all there is to SqlReader!

By Ashiq Alibhai, Edited by Sameer

It is interesting to note that SqlHelper.ExecuteDataSource(…) is implemented with a SqlDataReader internally, but has a lot of other bulky stuff in there.  So if efficiency is what you care about, you probably want to use a SqlDataReader, but otherwise, if you want a quick solution to get your DataSet, go for the ExecuteDataSet.

When not to use Sql Data Reader

Update - April 3, 2008

Don’t use Sql Data Reader when you don’t need to unecessarily keep a database connection open.   It’s more efficient to use a disconnected data source model, meaning that you fetch all the data that is required at the beginning, and then close the connection.  This is already implemented for you in SqlHelper.ExecuteDataSet().. If you are doing a big loop and outside the loop it has while (dr.read()) you will start to use up all your database connections pretty fast if your site gets moderately busy.  In most cases you won’t need this, so just use ExecuteDataSet instead. 

If you do need to grab the data as you are going along and use it, for example when your data source is very large, or when you want to do asynchronous work on the data, then its a good idea to use SqlDataReader so you can process the data as it comes (rather than waiting until the entire data results are available)

As well, DON’T FORGET, you need to explicitly close your Database connections, best to have a try { } catch { } and in the finally { } block you can put conn.Close(), otherwise if there is an exception you will have a connection that is going to stay open until .NET decides to dispose of it (in its own sweet time…..)

More discussion to come!

References:

1. SqlDataReader Class (System.Data.SqlClient)
   

Other Interesting Posts

• PHP VS. ASP.NET
• Sql Injections and Securing Clipshare Vulnerabilities
• Are You a Sharp Developer?? (Part 1)