ADO.Net: Check if field name exists on IDataRecord

Question

Is there a better way for getting the field_name value from a IDataRecord only if the field_name exists in the IDataRecord, currently I'm using a try{...} catch{...} block, but this is some kind of On Error Resume next. Some alternatives?

/// <summary>
/// Returns column value from IDataRecord only if field_name exists.
/// </summary>
public static Tresult ValueIfExists<Tresult>(this IDataRecord record, string field_name)
{
    try { return record.Value<Tresult>(record.GetOrdinal(field_name)); }
    catch { return default(Tresult); }
}

/// <summary>
/// Returns column value from IDataRecord accecing by index.
/// </summary>
public static Tresult Value<Tresult>(this IDataRecord record, int field_index)
{
    return record.IsDBNull(field_index) ? default(Tresult) :
              (Tresult)Convert.ChangeType(record[field_index], typeof(Tresult));
}

I have changed my ValueIfExists function to reflect your ideas, so it looks like this:

public static Tresult ValueIfExists2<Tresult>(this IDataRecord record, string field_name)
{
    for (int index = 0; index < record.FieldCount; index++)
    {
        if (record.GetName(index).Equals(field_name, StringComparison.InvariantCulture))
        {
            return record.Value<Tresult>(record.GetOrdinal(field_name));
        }
    }
    return default(Tresult);
}

you should use `StringComparison.OrdinalIgnoreCase` as columnnames can be in any case. — Michael B, Nov 24 '10 at 15:31
possible duplicate of [Detecting if an IDataReader contains a certain field before iteration](http://stackoverflow.com/questions/52952/detecting-if-an-idatareader-contains-a-certain-field-before-iteration) — nawfal, Dec 12 '13 at 13:06

Guffa · Accepted Answer · 2010-11-08T17:59:40.817

You are right that exceptions should not be used for normal program flow.

The GetOrdinal method is intended for situations where you know what fields you get, and if a field is missing that is an error that should result in an exception.

If you don't know which fields you get in the result, you should avoid the GetOrdinal method. You can instead get all the names and their index into a dictionary that you can use as replacement for the GetOrdinal method:

public static Dictionary<string, int> GetAllNames(this IDataRecord record) {
  var result = new Dictionary<string, int>();
  for (int i = 0; i < record.FieldCount; i++) {
    result.Add(record.GetName(i), i);
  }
  return result;
}

You can use the ContainsKey method to check if the name exists in the dictionary, or the TryGetValue method to check if the name exists and get it's index it does in a single operation.

The GetOrdinal method first does a case sensetive search for the name, and if that fails it does a case insensetive search. That is not provided by the dictionary, so if you want that exact behaviour you would rather store the names in an array and write a method to loop through them when you want to find the index.

I think that [IDataRecord] interface is missing for a method for testing the elements existence. I have used something like your code for Visual Basic 6 recordsets, but I was of the idea that .Net have already something for dealing with this. — ArBR, Nov 08 '10 at 22:42
@ArceBrito: As far as I can tell there is no method for specifically checking the existance of a field in the implementation classes like `SqlDataReader` either. You just have to use `GetName` to find out which fields there are. — Guffa, Nov 08 '10 at 23:18
A more LINQy version: `Enumerable.Range(0, record.FieldCount).ToDictionary(i => record.GetName(i), i => i)` — Arithmomaniac, May 05 '15 at 16:25

score 3 · Answer 2 · answered Sep 21 '16 at 13:48

3

With one line:

bool exists = Enumerable.Range(0, dataRecord.FieldCount).Any(x => dataRecord.GetName(x) == "columnName"); // or with OrdinalIgnoreCase

answered Sep 21 '16 at 13:48

Lukasz Pyrzyk

418
4
9

score 2 · Answer 3 · edited May 23 '17 at 11:45

2

Take a look at this closely-related question for a viable approach of testing for a field's existence. Note that if you are iterating a collection of results, it is probably better to check for the column once, rather than on each iteration.

Check for column name in a SqlDataReader object

edited May 23 '17 at 11:45

Community

1
1

answered Nov 08 '10 at 17:49

kbrimington

25,142
5
62
74

It means that for now, the only manner for checking fields existence in the [IDataRecord] interface is iterating. Then the problem is to implement the best searching algorithm for IDataRecord. – ArBR Nov 08 '10 at 22:55

score 1 · Answer 4 · answered Nov 09 '10 at 13:37

1

I always use the following approach for IDataReader (since most IDataRecord you get are readers) reader.GetSchemaTable().Columns.Contains("field")

Of course if you have a genuine IDataRecord then this will fail if you try to cast it to IDataReader and it isn't one.

answered Nov 09 '10 at 13:37

Michael B

7,512
3
31
57

In my case I'm using a Mapper pattern for creating objects by passing as argument the IDataRecord so I can't use your suggested approach; in the other hand it requires to get the database table metadata which could be more expensive than iterating over the fields collection with the purpose of checking field existence. – ArBR Nov 23 '10 at 22:23
It's actually cheaper usually. The metadata was grabbed at least once in the case of the `SQLDataReader`, and it's cached afterwards, so it just keeps reading the same variable. – Michael B Nov 24 '10 at 15:28
This checks the column in the schema table, not the actual data table! Always returns false! Check this answer: http://stackoverflow.com/a/813713/1070906 – JCH2k Jun 28 '16 at 16:37

ADO.Net: Check if field name exists on IDataRecord

4 Answers4