How to Read Data with the SqlDataReader ?

Answer

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 ExecuteReader 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 finally 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.