Chinh Do

Put GetOrdinal Method to Good Use

6th February 2008

Put GetOrdinal Method to Good Use

How often do you see DataReader code that looks like this?

using (IDataReader dr = cmd.ExecuteNonQuery())
{
    while (dr.Read())
    {
        int orderId = dr.GetInt32(0);
        string customerId = dr.GetString(1);
        int employeeId = dr.GetInt32(2);
        DateTime orderDate = dr.GetDateTime(3);
        double freight = dr.GetDouble(4);

        // do stuff
    }

    dr.Close();
}

If that looks like your code, you are not alone :-) . Try searching codesearch.google.com for the following:

lang:c# (reader|dr)\.GetInt32\(

codesearch google

With the above code, anytime the order of columns in the SQL statement or stored procedure changes, the code is broken. And if you have lots of columns to read from, it’s a real nightmare to maintain the indexes.

The next time you write another DataReader loop, consider doing it this way instead:

using (IDataReader dr = cmd.ExecuteNonQuery())
{
    int ORDER_ID = dr.GetOrdinal("OrderID");
    int CUSTOMER_ID = dr.GetOrdinal("CustomerID");
    int EMPLOYEE_ID = dr.GetOrdinal("EmployeeID");
    int ORDER_DATE = dr.GetOrdinal("OrderDate");
    int FREIGHT = dr.GetDecimal("Freight");

    while (dr.Read())
    {
        int orderId = dr.GetInt32(ORDER_ID);
        string customerId = dr.GetString(CUSTOMER_ID);
        int employeeId = dr.GetInt32(EMPLOYEE_ID);
        DateTime orderDate = dr.GetDateTime(ORDER_DATE);
        double freight = dr.GetDouble(FREIGHT);

        // do stuff
    }

    dr.Close();
}

Using GetOrdinal makes the code much more readable and maintainable. You are calling GetOrdinal just once for each column, any performance penalty is insignificant compared to the benefits. Be careful not to put the GetOrdinal code inside the while block as that will unnecessarily slow you down (about 3% according to this article).

kick it on DotNetKicks.com

This entry was posted on Wednesday, February 6th, 2008 at 12:31 am and is filed under Dotnet/.NET - C#, Programming, Tips. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

There are currently 3 responses to “Put GetOrdinal Method to Good Use”

  1. 1 On February 7th, 2008, Mischa Kroon said:

    Looking good while performing optimally is always a good thing :)

    thx for sharing

  2. 2 On February 8th, 2008, Chinh Do said:

    Mischa: Thanks and cheers.

  3. 3 On July 17th, 2014, Las Vegas GE Appliance Repair said:

    Goood site you have got here.. It’s difficult to find excellent writing like yours nowadays.
    I seriously appreciate people like you! Takee care!!

    Review myy weblog Las Vegas GE Appliance Repair

Leave a Comment

*