Reading Data with the SqlDataReader - Ado.net Tutorials
Reading Data with the SqlDataReader
This lesson explains how to read data with a SqlDataReader object. Here are the objectives of this lesson:
- Learn what a SqlDataReader is used for.
- Know how to read data using a SqlDataReader.
- Understand the need to close a SqlDataReader.
Introduction
A SqlDataReader is a type that is good for reading data in the most efficient manner possible. You can *not* use it for writing data. SqlDataReaders are often described as fast-forward firehose-like streams of data.
You can read from SqlDataReader objects in a forward-only sequential manner. Once you've read some data, you must save it because you will not be able to go back and read it again.
The forward only design of the SqlDataReader is what enables it to be fast. It doesn't have overhead associated with traversing the data or writing it back to the data source. Therefore, if your only requirement for a group of data is for reading one time and you want the fastest method possible, the SqlDataReader is the best choice. Also, if the amount of data you need to read is larger than what you would prefer to hold in memory beyond a single call, then the streaming behavior of the SqlDataReader would be a good choice.
Note: Observe that I used the term "one time" in the previous paragraph when discussing the reasons why you would use a SqlDataReader. As with anything, there are exceptions. In many cases, it is more efficient to use a cached DataSet. While caching is outside the scope of this tutorial, we will discuss using DataSet objects in the next lesson.
Creating a SqlDataReader Object
Getting an instance of a SqlDataReader is a little different than the way you instantiate other ADO.NET objects. You must call ExecuteReaderon a command object, like this:
SqlDataReader rdr = cmd.ExecuteReader();
The ExecuteReader method of the SqlCommand object, cmd , returns a SqlDataReader instance. Creating a SqlDataReader with the new operator doesn't do anything for you. As you learned in previous lessons, the SqlCommand object references the connection and the SQL statement necessary for the SqlDataReader to obtain data.
Reading Data
previous lessons contained code that used a SqlDataReader, but the discussion was delayed so we could focus on the specific subject of that particular lesson. This lesson builds from what you've seen and explains how to use the SqlDataReader.
As explained earlier, the SqlDataReader returns data via a sequential stream. To read this data, you must pull data from a table row-by-row Once a row has been read, the previous row is no longer available. To read that row again, you would have to create a new instance of the SqlDataReader and read through the data stream again.
The typical method of reading from the data stream returned by the SqlDataReader is to iterate through each row with a while loop. The following code shows how to accomplish this:
while (rdr.Read()){// get the results of each columnstring contact = (string)rdr["ContactName"];string company = (string)rdr["CompanyName"];string city = (string)rdr["City"];// print out the resultsConsole.Write("{0,-25}", contact);Console.Write("{0,-20}", city);Console.WriteLine();Console.Write("{0,-25}", company);}
Notice the call to Read on the SqlDataReader, rdr, in the while loop condition in the code above. The return value of Read is type bool and returns true as long as there are more records to read. After the last record in the data stream has been read, Read returns false.
In previous lessons, we extracted the first column from the row by using the SqlDataReader indexer, i.e. rdr[0]. You can extract each column of the row with a numeric indexer like this, but it isn't very readable. The example above uses a string indexer, where the string is the column name from the SQL query (the table column name if you used an asterisk, *. String indexers are much more readable, making the code easier to maintain.
Regardless of the type of the indexer parameter, a SqlDataReader indexer will return type object. This is why the example above casts results to a string. Once the values are extracted, you can do whatever you want with them, such as printing them to output with Console type methods.
Finishing Up
Always remember to close your SqlDataReader, just like you need to close the SqlConnection. Wrap the data access code in a try block and put the close operation in the finally block, like this:
try{// data access code}finally{// 3. close the readerif (rdr != null){rdr.Close();}// close the connection too}
The code above checks the SqlDataReader to make sure it isn't null. After the code knows that a good instance of the SqlDataReader exists,it can close it. Listing 1 shows the code for the previous sections in its entirety.
Listing 1: Using the SqlDataReader
using System;using System.Data;using System.Data.SqlClient;namespace Lesson04class ReaderDemo{{static void Main(){ReaderDemo rd = new ReaderDemo();rd.SimpleRead();}public void SimpleRead(){// declare the SqlDataReader, which is used in// both the try block and the finally blockSqlDataReader rdr = null;// create a connection objectSqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");// create a command objectSqlCommand cmd = new SqlCommand("select * from Customers", conn);try{// open the connectionconn.Open();// 1. get an instance of the SqlDataReaderrdr = cmd.ExecuteReader();// print a set of column headersConsole.WriteLine("Contact Name City Company Name");Console.WriteLine("------------ ------------ ------------");// 2. print necessary columns of eachrecordwhile (rdr.Read()){// get the results of each columnstring contact = (string)rdr["ContactName"];string company = (string)rdr["CompanyName"];string city = (string)rdr["City"];// print out the resultsConsole.Write("{0,-25}", contact);Console.Write("{0,-20}", city);Console.WriteLine();Console.Write("{0,-25}", company); } }finally{// 3. close the readerif (rdr != null){rdr.Close();}// close the connectionif (conn != null){conn.Close();} } } }}
