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"));
           }

Reading by index is faster. GetOrdinal needs to compare strings which is not so fast as acessing array elements by index. — Martin Staufcik
– Martin Staufcik, Commented Nov 20, 2019 at 14:20
I figured reading by index is faster, but I have had some devs literally insist that GetOrdinal is "better standard". My current job we are all about speed so it makes sense our lead wants us to read by index. My last job insisted using GetOrdinal was better for some reason. Good to know! — Andrew Reese
– Andrew Reese, Commented Nov 20, 2019 at 14:30

D-Shih · Accepted Answer · 2019-11-20 14:40:38Z

3

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.

edited Nov 20, 2019 at 14:40

answered Nov 20, 2019 at 14:20

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andrew Reese Over a year ago

Thanks for this detailed answer! Now it makes sense why we read by index at my current job (for speed), but used GetOrdinal at my last job (standard/didn't care about speed). It sucks when you're adding a new field in a big query and have to redo all of the indexes but it's worth it if makes your execution quicker.

D-Shih Over a year ago

@AndrewReese No problem glad to help

Bart · Accepted Answer · 2019-11-21 08:43:26Z

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.

edited Nov 21, 2019 at 8:43

answered Nov 20, 2019 at 14:25

Bart

193 bronze badges

2 Comments

Andrew Reese Over a year ago

I've never seen it used that way, but very interesting. Thanks for sharing.

Bart Over a year ago

YW. It still is slower than just calling index, but you get columns stored once what is salutary with dozens or hundreds of rows.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related