ADO.NET 3.5 Cookbook, 2nd Edition

3.4. Accessing Data Values in a DataReader

Problem

You need to access the data values in a DataReader.

Solution

Use one of the techniques shown in this solution.

The solution creates a DataReader that returns rows from the Person.Contact table in AdventureWorks. Values from the result set are output to the console using a variety of techniques.

The C# code in Program.cs in the project RetrieveValuesDataReader is shown in Example 3-5.

Example 3-5. File: Program.cs for RetrieveValuesDataReader solution

using System;
using System.Data;
using System.Data.SqlClient;

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

            string sqlSelect = @"SELECT ContactID, NameStyle, Title,
                FirstName, MiddleName, LastName, Suffix, EmailAddress,
                EmailPromotion, Phone, PasswordHash, PasswordSalt
                FROM Person.Contact";

            SqlConnection connection = new SqlConnection(sqlConnectString);

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

            Sql DataReader dr = command.ExecuteReader( ); dr.Read( ); // Output fields from the first DataRow reader using different // techniques Console.WriteLine("ContactID = {0}", dr[0]); Console.WriteLine("Title = {0}", dr["Title"]); Console.WriteLine("FirstName = {0}", dr.IsDBNull(3) ? "NULL" : dr.GetString(3)); Console.WriteLine("MiddleName = {0}", dr.IsDBNull(4) ? "NULL" ...