using datareader with dbnull values in .net4

Question

I heard there is a field extension method in framework 4 that permits one to receive null values from a datareader, without having to go through the process of first testing if not null then ... etc. There's information about the extension method here (MSDN), but i don't know how to use it in code (am relatively new to .net and never used extension methods before). Would appreciate if anyone can give an example.

This is what i tried to implement, but it returns an error when a dbnull is returned in either one of the columns.

Reader.Read()
Dim Val As Nullable(Of Double) = Reader.GetDecimal(0)
Dim Vol As Nullable(Of Long) = Reader.GetInt32(1)

score 6 · Answer 1 · edited Feb 27 '12 at 12:35

Those extension methods relate to DataRow - i.e. DataTable... not IDataReader (etc). You can do what you want here with a conditional, though - IIf in VB, or in C#:

double? val = reader.IsDBNull(index) ? (double?) null : reader.GetDouble(index);
long? vol = reader.IsDBNull(index) ? (long?)null : reader.GetInt64(index);

You could of course wrap those up as utility methods, perhaps as your own custom extension methods on IDataReader:

public static class DataReaderExtensions
{
    public static int? ReadNullableInt32(this IDataReader reader, int index)
    {
        return reader.IsDBNull(index) ? (int?)null : reader.GetInt32(index);
    }
    public static long? ReadNullableInt64(this IDataReader reader, int index)
    {
        return reader.IsDBNull(index) ? (long?)null : reader.GetInt64(index);
    }
    public static double? ReadNullableDouble(this IDataReader reader, int index)
    {
        return reader.IsDBNull(index) ? (double?)null : reader.GetDouble(index);
    }
    public static string ReadNullableString(this IDataReader reader, int index)
    {
        return reader.IsDBNull(index) ? null : reader.GetString(index);
    }
    // etc
}

(sorry for using c# for the examples - but you can probably read c# better than I can write accurate vb.net)

score 1 · Answer 2 · answered Feb 27 '12 at 12:03

In order to use the DataRow extension methods you need a DataRow. There is no method on a DataReader, so what you need to do is to load the reader into a DataTable (in C#):

var table = new DataTable();
table.Load(reader);

foreach(DataRow row in table.Rows)
{
    var value = row.Field<Decimal>(0);
}

It is important to realise that this is not logically equivalent to using the DataReader.Read() method as you will be loading the entire reader into memory when you load it into the DataTable. This may cause problems if your row set is large.

using datareader with dbnull values in .net4

2 Answers2

Linked