ADO.NET 3.5 Cookbook, 2nd Edition

3.3. Retrieving a Result Set Stream Using a DataReader

Problem

You need to return a result set as a forward-only stream of rows.

Solution

Use the ExecuteReader() method of the Command object to create a DataReader that retrieves a result set.

The solution creates a Connection, Command, and DataReader that return a subset of rows from the Person.Contact table in AdventureWorks. Result set values from the DataReader are output to the console.

The C# code in Program.cs in the project RetrieveDataUsingDataReader is shown in Example 3-4.

Example 3-4. File: Program.cs for RetrieveDataUsingDataReader solution

using System;
using System.Data.SqlClient;

namespace RetrieveDataUsingDataReader
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            string sqlSelect =
                "SELECT ContactID, FirstName, LastName FROM Person.Contact " +
                "WHERE ContactID BETWEEN 10 AND 14";

            SqlConnection connection = new SqlConnection(sqlConnectString);

            // Create the command and open the connection
            SqlCommand command = new SqlCommand(sqlSelect, connection);
            connection.Open( );

            // Create the DataReader to retrieve data
            using (SqlDataReader dr = command.ExecuteReader( ))
            {
                while (dr.Read( ))
                {
                    // Output fields from DataReader row
                    Console.WriteLine(
                        "Cont actID = {0}\tFirstName = {1}\tLastName = {2}", dr["ContactID"], dr["LastName"], dr["FirstName"]); } } connection.Close( ); Console.WriteLine("\nPress any key to continue."); ...