9

Similar to this question, but the answers never really got around to what I want to know. Is there any standards around getting values from a DataReader? I.e., is this

dataReader.GetString(dataReader.GetOrdinal("ColumnName"));

considered better/worse/the same as this?

(string) dataReader["ColumnName"];
Community
  • 1
  • 1
AJ.
  • 16,368
  • 20
  • 95
  • 150

2 Answers2

11

Here is the way that I do it:

Int32 ordinal = dataReader.GetOrdinal("ColumnName");

if (!dataReader.IsDBNull(ordinal))
    yourString = dataReader.GetString(ordinal);

It is important to check for DBNull like I have shown above because if the field is null in the DataReader it will throw an exception when you try to retrieve it.

Andrew Hare
  • 344,730
  • 71
  • 640
  • 635
  • Absolutely agreed. I'm asking more about the syntactical differences of getting the actual value, using the DataReaders Item[] functionality vs. using the provided DataReader.Get methods. – AJ. Dec 06 '09 at 14:54
  • 3
    Since the `datareader["column"]` format returns an object and is not strongly-typed, I don't think you'll get an exception right then if the data is `DbNull`. You'll just get `DbNull.Value` cast to `object`. Of course, you may get an exception later when you try to use the value if you don't first do a `Convert.IsDbNull` check. – Joel Mueller Dec 06 '09 at 17:26
9

I made some extension methods to let me treat an IDataReader as an enumerable, and to deal with DbNull by returning nullable ints, etc. This lets me check for null and apply a default value with the C# ?? operator.

/// <summary>
/// Returns an IEnumerable view of the data reader.
/// WARNING: Does not support readers with multiple result sets.
/// The reader will be closed after the first result set is read.
/// </summary>
public static IEnumerable<IDataRecord> AsEnumerable(this IDataReader reader)
{
    if (reader == null)
        throw new ArgumentNullException("reader");

    using (reader)
    {
        while (reader.Read())
        {
            yield return reader;
        }
    }
}

public static int? GetNullableInt32(this IDataRecord dr, string fieldName)
{
    return GetNullableInt32(dr, dr.GetOrdinal(fieldName));
}

public static int? GetNullableInt32(this IDataRecord dr, int ordinal)
{
    return dr.IsDBNull(ordinal) ? null : (int?)dr.GetInt32(ordinal);
}

...and so on for the other GetDataType() methods on IDataReader.

Joel Mueller
  • 28,324
  • 9
  • 63
  • 88
  • 1
    Well, you could if `IDataReader` had anything at all to do with `IEnumerable`. However, this is not the case. The signature is: `public interface IDataReader : IDisposable, IDataRecord` – Joel Mueller Dec 07 '09 at 17:33
  • Nice, but you might want to close the reader after the: `while (reader.Read())` loop – Ulf Lindback Jul 01 '10 at 12:07
  • Hmm, does the reader really get closed from that Using? I thought the using would only close the reader, if you initialize the reader in it like: using (var reader = new DataReader){...} Am I wrong? – SwissCoder Nov 13 '12 at 04:32
  • 4
    @SwissCoder - The `using` statement only requires that you pass in an `IDisposable` instance. It doesn't require that you create the instance inside the parens. – Joel Mueller Nov 13 '12 at 17:04
  • thank you Joel for taking the time, learnt something today :) – SwissCoder Nov 15 '12 at 06:38
  • @JoelMueller, `DbDataReader` implements `IEnumerable`, and it will be rare someone has a concrete ADO.NET implementation of `IDataReader` without inheriting from `DbDataReader`. So basically this should work: `((DbDataReader)reader).Cast()`. Just saying.. – nawfal Nov 08 '13 at 18:08