C# - SQLDataReader by Index vs. SQLDataReader.GetOrdinal(ColumnName)

Question

Is one considered better standard? Is one quicker than the other? Or, is just mainly preference? GetOrdinal is nice because you can call the column name out itself and not have to worry about counting the index of the fields in SQL, but I would like to know if there are benefits using one over the other.

Reading by Index:

            while (reader.Read())
            { 
                Column1 = reader.GetValue(0).ToString().Trim();
                Column2 = reader.GetValue(1).ToString().Trim();
            }

Reader.GetOrdinal:

           while (reader.Read())
           {
               data.Column1 = reader.GetValue(reader.GetOrdinal("COLUMN1")).ToString();
               data.Column2 = reader.GetValue(reader.GetOrdinal("COLUMN2")).ToString();
               data.Column3 = reader.GetDateTime(reader.GetOrdinal("COLUMN3"));
           }

Answer 1

reader.GetOrdinal(string) will get the column ordinal, given the name of the column

We can see GetOrdinal sourcecode from SqlDataReader it will return a index from _fieldNameLookup.GetOrdinal (_fieldNameLookup field is a FieldNameLookup class)

_fieldNames is a hashtable stores the index, match via case-sensitive

override public int GetOrdinal(string name) {
    SqlStatistics statistics = null;
    try {
        statistics = SqlStatistics.StartTimer(Statistics);
        if (null == _fieldNameLookup) {
            CheckMetaDataIsReady();
            _fieldNameLookup = new FieldNameLookup(this, _defaultLCID);
        }
        return _fieldNameLookup.GetOrdinal(name); // MDAC 71470
    }
    finally {
        SqlStatistics.StopTimer(statistics);
    }
}

we can see the source code GetOrdinal method from FieldNameLookup class.

public int GetOrdinal(string fieldName) { // V1.2.3300
    if (null == fieldName) {
        throw ADP.ArgumentNull("fieldName");
    }
    int index = IndexOf(fieldName);
    if (-1 == index) {
        throw ADP.IndexOutOfRange(fieldName);
    }
    return index;
}

public int IndexOf(string fieldName) { // V1.2.3300
    if (null == _fieldNameLookup) {
        GenerateLookup();
    }
    int index;
    object value = _fieldNameLookup[fieldName];
    if (null != value) {
        // via case sensitive search, first match with lowest ordinal matches
        index = (int) value;
    }
    else {
        // via case insensitive search, first match with lowest ordinal matches
        index = LinearIndexOf(fieldName, CompareOptions.IgnoreCase);
        if (-1 == index) {
            // do the slow search now (kana, width insensitive comparison)
            index = LinearIndexOf(fieldName, ADP.compareOptions);
        }
    }
    return index;
}

Is one quicker than the other?

If you already know columns exist index number reader.GetValue(0) will faster then reader.GetValue(reader.GetOrdinal("COLUMN1")) becuase it didn't cause resource to get the colunm index from reader.GetOrdinal method.

Is one considered better standard?

There isn't comparison standard because of reader.GetValue(0) and reader.GetValue(reader.GetOrdinal("COLUMN1")) are doing the same thing, as before answer.

reader.GetValue(reader.GetOrdinal("COLUMN1")) be better reading then reader.GetValue(0), because columns name will be better to know instead index.

Answer 2

I always use function that returns dictionary with columns names as key and index as value, like that one:

public IDictionary<string, int> GetColumnNames(ref SqlDataReader reader) {
    IDictionary<string, int> dict = new Dictionary<string, int>();
    if (reader == null)
        return dict;
    int columns = reader.FieldCount;

    for (int i = 0; i < columns; i++) {
        dict[reader.GetName(i)] = i;
    }

    return dict;
}

then you can just create new object an call any time:

var cols = GetColumnNames(ref r);
while (r.Read())
    var value = r.GetInt32(cols["SOME_COLUMN"]);

I don't really know if it's quicker, but works for me. Also, works nice with defined constant column names.