C# – Get column values by name instead of by number with SqlDataReader

When you execute a SQL query and read the results with SqlDataReader, you have two options for getting column values by name (instead of by ordinal number):

• Use the indexer and cast to the primitive type.
• Use the Get extension methods (from System.Data) such as GetString(string name).

I’ll show examples below.

Read column by name with the indexer

You can use the indexer to read columns by name (or by ordinal number). This returns an object that you have to cast. Here’s an example:

using System.Data.SqlClient;

var conString = @"Server=serverName\instanceName;Database=dbName;Integrated Security=true";

using (var con = new SqlConnection(conString))
{
    con.Open();

    using var cmd = new SqlCommand(@"SELECT Name,BirthDate,FavoriteNumber FROM People", con);
    using var reader = cmd.ExecuteReader();

    while (reader.Read())
    {
        var person = new Person()
        {
            Name = reader["Name"] as string,
            BirthDate = (DateTime)reader["BirthDate"],
            FavoriteNumber = reader["FavoriteNumber"] as int?
        };

        Console.WriteLine($"{person.Name}'s birth month is {person.BirthDate.Month}");
    }
}
Code language: C# (cs)

Note: Make sure you’re handling nulls with SqlDataReader.

This outputs the following:

Bob's birth month is 11
Alice's birth month is 1Code language: plaintext (plaintext)

Read column by name with Get extension methods

You can use methods such as GetString() to get a column value converted to its primitive type. You can read the columns by ordinal number (0-based index) or by name. SqlDataReader only implements these Get methods that read columns by ordinal number – such as GetString(int i). However, you can read columns by name by using extension methods located in the System.Data namespace. Here’s an example:

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

var conString = @"Server=serverName\instanceName;Database=dbName;Integrated Security=true";

using (var con = new SqlConnection(conString))
{
    con.Open();

    using var cmd = new SqlCommand(@"SELECT Name,BirthDate,FavoriteNumber FROM People", con);
    using SqlDataReader reader = cmd.ExecuteReader();

    while (reader.Read())
    {
        var person = new Person()
        {
            Name = reader.GetString("Name"),
            BirthDate = reader.GetDateTime("BirthDate"),
            FavoriteNumber = reader.GetFieldValue<int?>("FavoriteNumber")
        };

        //The values are the same
        Console.WriteLine($"{person.Name}'s favorite number is {person.FavoriteNumber}");
    }
}
Code language: C# (cs)

This outputs the following:

Bob's favorite number is 7
Alice's favorite number is 5Code language: plaintext (plaintext)

Tip: If you don’t want to handle mapping the results yourself, you can use Dapper to execute SQL queries and handle the mapping for you.

CS1503 error when you don’t reference System.Data

If you try to use one of the Get methods that takes a string, and you’re not referencing the System.Data namespace, you’ll get a compiler error:

Error CS1503 Argument 1: cannot convert from ‘string’ to ‘int’

This is because the compiler can’t see the extension methods. It can only see the Get methods defined in SqlDataReader that take an integer – such as GetString(int i). Hence why it thinks you’re trying to pass in a string for an integer.

You just need to reference the System.Data namespace (using System.Data;).