Lesson 04: Reading Data with the SqlDataReader and the SqlDataReader Object

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 the 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 callExecuteReader on 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 column
		string contact = (string)rdr["ContactName"];
		string company = (string)rdr["CompanyName"];
		string city    = (string)rdr["City"];

		// print out the results
		Console.Write("{0,-25}", contact);
		Console.Write("{0,-20}", city);
		Console.Write("{0,-25}", company);
		Console.WriteLine();
	}

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 final block, like this:

	try
	{
		// data access code
	}
	finally
	{
		// 3. close the reader
		if (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 Lesson04
{
	class 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 block
			SqlDataReader rdr = null;

			// create a connection object
			SqlConnection conn = new SqlConnection(
"Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");

			// create a command object
			SqlCommand cmd  = new SqlCommand(
				"select * from Customers", conn);

			try
			{
				// open the connection
				conn.Open();

				// 1. get an instance of the SqlDataReader
				rdr = cmd.ExecuteReader();

				// print a set of column headers
				Console.WriteLine(
"Contact Name             City                Company Name");
				Console.WriteLine(
"------------             ------------        ------------");

				// 2. print necessary columns of each
                    record
				while (rdr.Read())
				{
					// get the results of each column
					string contact = (string)rdr["ContactName"];
					string company = (string)rdr["CompanyName"];
					string city    = (string)rdr["City"];

					// print out the results
					Console.Write("{0,-25}", contact);
					Console.Write("{0,-20}", city);
					Console.Write("{0,-25}", company);
					Console.WriteLine();
				}
			}
			finally
			{
				// 3. close the reader
				if (rdr != null)
				{
					rdr.Close();
				}

				// close the connection
				if (conn != null)
				{
					conn.Close();
				}
			}	
		}
	}
}

Summary

SqlDataReader objects allow you to read data in a fast forward-only manner. You obtain data by reading each row from the data stream. Call the Close method of the SqlDataReader to ensure there are not any resource leaks.

I hope you enjoyed this lesson. The next one in this series is Lesson 05: Working with Disconnected Data – The DataSet and SqlDataAdapter

Follow Joe Mayo on Twitter.

Feedback

Lesson 01: Introduction to ADO.NET

This lesson is an introduction to ADO.NET.  It introduces primary ADO.NET concepts and objects that you will learn about in later lessons.  Here are the objectives of this lesson:

  • Learn what ADO.NET is.
  • Understand what a data provider is.
  • Understand what a connection object is.
  • Understand what a command object is.
  • Understand what a DataReader object is.
  • Understand what a DataSet object is.
  • Understand what a DataAdapter object is.

Introduction

ADO.NET is an object-oriented set of libraries that allows you to interact with data sources. Commonly, the data source is a database, but it could also be a text file, an Excel spreadsheet, or an XML file. For the purposes of this tutorial, we will look at ADO.NET as a way to interact with a database.

As you are probably aware, there are many different types of databases available. For example, there is Microsoft SQL Server, Microsoft Access, Oracle, Borland Interbase, and IBM DB2, just to name a few. To further refine the scope of this tutorial, all of the examples will use SQL Server.

You can download the Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) here:

http://www.microsoft.com/sql/msde/downloads/download.asp

MSDE contains documentation on how to perform an installation. However, for your convenience, here are quick instructions on how to install MSDE:

http://www.asp.net/msde/default.aspx?tabindex=0&tabid=1

MSDE 2000 is a scaled down version of SQL Server. Therefore, everything you learn in this tutorial and all code will work with SQL Server. The examples will use the Northwind database. This is a tutorial is specifically for ADO.NET. MSDE is not part of ADO.NET, but it is one of the many data sources you can interact with by using ADO.NET If you need help with MSDE 2000, I refer you to the Microsoft Web site, where you can find pertinent information on licensing and technical assistance:

http://www.microsoft.com/sql/msde/

Data Providers

We know that ADO.NET allows us to interact with different types of data sources and different types of databases. However, there isn’t a single set of classes that allow you to accomplish this universally. Since different data sources expose different protocols, we need a way to communicate with the right data source using the right protocol Some older data sources use the ODBC protocol, many newer data sources use the OleDb protocol, and there are more data sources every day that allow you to communicate with them directly through .NET ADO.NET class libraries.

ADO.NET provides a relatively common way to interact with data sources but comes in different sets of libraries for each way you can talk to a data source. These libraries are called Data Providers and are usually named for the protocol or data source type they allow you to interact with. Table 1 lists some well-known data providers, the API prefix they use, and the type of data source they allow you to interact with.

Table 1. ADO.NET Data Providers are class libraries that allow a common way to interact with specific data sources or protocols. The library APIs have prefixes that indicate which provider they support.
Provider Name API prefix Data Source Description
ODBC Data Provider Odbc Data Sources with an ODBC interface. Normally older databases.
OleDb Data Provider OleDb Data Sources that expose an OleDb interface, i.e. Access or Excel.
Oracle Data Provider Oracle For Oracle Databases.
SQL Data Provider SQL For interacting with Microsoft SQL Server.
Borland Data Provider Bdp Generic access to many databases such as Interbase, SQL Server, IBM DB2, and Oracle.

An example may help you to understand the meaning of the API prefix. One of the first ADO.NET objects you’ll learn about is the connection object, which allows you to establish a connection to a data source. If we were using the OleDb Data Provider to connect to a data source that exposes an OleDb interface, we would use a connection object named OleDbConnection. Similarly, the connection object name would be prefixed with Odbc or Sql for an OdbcConnection object on an Odbc data source or a SqlConnection object on a SQL Server database, respectively. Since we are using MSDE in this tutorial (a scaled down version of SQL Server) all the API objects will have the SQL prefix. i.e. SqlConnection.

ADO.NET Objects

ADO.NET includes many objects you can use to work with data. This section introduces some of the primary objects you will use. Over the course of this tutorial, you’ll be exposed to many more ADO.NET objects from the perspective of how they are used in a particular lesson. The objects below are the ones you must know. Learning about them will give you an idea of the types of things you can do with data when using ADO.NET.

The SqlConnection Object

To interact with a database, you must have a connection to it. The connection helps identify the database server, the database name, username, password, and other parameters that are required for connecting to the database. A connection object is used by command objects so they will know which database to execute the command on.

The SqlCommand Object

The process of interacting with a database means that you must specify the actions you want to occur. This is done with a command object. You use a command object to send SQL statements to the database. A command object uses a connection object to figure out which database to communicate with. You can use a command object alone, to execute a command directly, or assign a reference to a command object to a SqlDataAdapter, which holds a set of commands that work on a group of data as described below.

The SqlDataReader Object

Many data operations require that you only get a stream of data for reading. The data reader object allows you to obtain the results of a SELECT statement from a command object. For performance reasons, the data returned from a data reader is a fast forward-only stream of data. This means that you can only pull the data from the stream in a sequential manner This is good for speed, but if you need to manipulate data, then a DataSet is a better object to work with.

The DataSet Object

DataSet objects are in-memory representations of data. They contain multiple Datatable objects, which contain columns and rows, just like normal database tables. You can even define relations between tables to create parent-child relationships. The DataSet is specifically designed to help manage data in memory and to support disconnected operations on data when such a scenario make sense. The DataSet is an object that is used by all of the Data Providers, which is why it does not have a Data Provider specific prefix.

The SqlDataAdapter Object

Sometimes the data you work with is primarily read-only and you rarely need to make changes to the underlying data source Some situations also call for caching data in memory to minimize the number of database calls for data that does not change. The data adapter makes it easy for you to accomplish these things by helping to manage data in a disconnected mode. The data adapter fills a DataSet object when reading the data and writes in a single batch when persisting changes back to the database. A data adapter contains a reference to the connection object and opens and closes the connection automatically when reading from or writing to the database. Additionally, the data adapter contains command object references for SELECT, INSERT, UPDATE, and DELETE operations on the data. You will have a data adapter defined for each table in a DataSet and it will take care of all communication with the database for you. All you need to do is tell the data adapter when to load from or write to the database.

Summary

ADO.NET is the .NET technology for interacting with data sources. You have several Data Providers, which allow communication with different data sources, depending on the protocols they use or what the database is. Regardless, of which Data Provider used, you’ll use a similar set of objects to interact with a data source. The SqlConnection object lets you manage a connection to a data source. SqlCommand objects allow you to talk to a data source and send commands to it. To have fast forward-only read access to data, use the SqlDataReader. If you want to work with disconnected data, use a DataSet and implement reading and writing to/from the data source with a SqlDataAdapter.

This is just the beginning – the first of several lessons in the ADO.NET Tutorial. The next one in this series is Lesson 02: The SqlConnection Object.

Follow Joe Mayo on Twitter.

Feedback