SqlDataReader Column Ordinals

Question

Suppose I am calling a query "SELECT name, city, country FROM People". Once I execute my SqlDataReader do columns come in the same order as in my sql query?

In other words can I rely that the following code will always work correctly:

SqlConnection connection = new SqlConnection(MyConnectionString);
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = "SELECT [name], [city], [country] WHERE [id] = @id";

try
{
    connection.Open();
    SqlDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.SingleRow);

    if (reader.Read())
    {
        // Read values.
        name = reader[0].ToString();
        city = reader[1].ToString();
        country = reader[2].ToString();
    }
}
catch (Exception)
{
    throw;
}
finally
{
    connection.Close();
}

Also how much performance do I lose if I use column names instead of ordinals (reader["name"])?

Are there any official microsoft documents describing the behavior of column ordering in SqlDataReader?

Answer 1

Yes they do but you can also use SqlDataReader.GetName(ordinal) and SqlDataReader.GetOrdinal(name).

As for performance, I think it's probably extremely insignificant compared to the overhead of say, retrieving the next row of data.

Answer 2

I totally agree with Josh - the positions of the fields are indeed such as you specify them in your SQL query text.

BUT: I would still prefer to use the column names, since it's more robust. E.g. what if you need to add a field to your SQL query?

command.CommandText = "SELECT [name], [jobtitle], [city], [country] WHERE [id] = @id";

Now suddenly you have to rewrite all your code to change the positions....

What I normally do outside the loop that enumerates through all the rows returned by the data reader is determine the positions of each field I'm interested in:

int namePosition = reader.GetOrdinal("name");
int cityPosition = reader.GetOrdinal("city");

and then I use these positions inside my loop handling the data to get quick access to the individual fields. That way you determine the positions only once, but you're using positions in your looping over the data - the best of both worlds! :-)

Marc

Answer 3

This example is the most maintainable and easiest to read:

int? quantity = reader.Get<int?>("Quantity");
Guid token = reader.Get<Guid>("Token");

It relies on the following extension method I created. It performs DB null checks, provides an informative error message when field is not found, and does not break when columns are re-aligned.

internal static T Get<T>(this SqlDataReader reader, string fieldName)
{
    int ordinal;
    try
    {
        ordinal = reader.GetOrdinal(fieldName);
    }
    catch (IndexOutOfRangeException)
    {
        throw new IndexOutOfRangeException(string.Format("Field name '{0}' not found.", fieldName));
    }

    return !reader.IsDBNull(ordinal) ? (T)reader.GetValue(ordinal) : default(T);
}

Cheers,

Harvo