Click here to Skip to main content
13,006,360 members (67,591 online)
Click here to Skip to main content
Add your own
alternative version


92 bookmarked
Posted 20 Jan 2006

C# 2.0 Nullable Data Readers

, 20 Jan 2006
Rate this:
Please Sign up or sign in to vote.
An article on nullable data readers for .NET 2.0 nullable types.


.NET 2.0 introduced nullable types into the CLR which, for the first time, provided the ability for value types to be assigned a null value. However, ADO.NET 2.0 did not introduce any new features specifically for dealing with nullable types. Therefore, one of the primary goals for the classes presented in this article is to provide a simple API for working with nullable types within the persistence layer.

There are three primary goals that the data readers will address.

  • Goal #1: Provide a simple, strongly-typed API for dealing with non-nullable types for both an IDataReader and a DataRow.
  • Goal #2: Provide a simple, strongly-typed API for dealing with nullable types for both an IDataReader and a DataRow.
  • Goal #3: Provide a unified interface so that the same code can be used polymorphically to consumer either an IDataReader or a DataRow.

Goal #1

Goal #1 is to provide a simple, unified, strongly-typed API for dealing with non-nullable types for both an IDataReader and a DataRow. The two features of the API are:

  1. to be able to reference everything with a readable column name (rather than an ordinal), and
  2. have strongly-typed methods for everything to avoid casting/conversion code.

The IDataReader interface provides several convenient strongly-typed GetXXX() methods to access the data. The problem is that these methods require a cryptic ordinal rather than a readable column name. This results in data access code that looks like this:

person.Age = reader.GetInt32(reader.GetOrdinal("Age"));

It would be much more convenient to write the method like this:

person.Age = reader.GetInt32("Age");

When working with a DataRow, the value returned is System.Object which means that you have to either pay the unboxing penalty by casting like this:

person.Age = (int)row["Age"];

or you have to using the Convert class like this:

person.Age = Convert.ToInt32(row["Age"]);

It would be more convenient if you could access data from the DataRow via strongly-typed methods similar to the IDataReader without having to code the monotonous casting/conversion code.

Goal #2

Goal #2 is to provide a simple, strongly-typed API for dealing with nullable types.

The IDataReader provides no methods for dealing with nullable types. Therefore, to correctly populate nullable types, the data access code would have to be littered with procedural, error-prone code like this:

if (reader.IsDBNull(reader.GetOrdinal("FiredDate")))
    person.FiredDate = null;
    person.FiredDate = 

This is clearly not ideal – it would be best to have the same strongly-typed GetXXX() methods for nullable types.

Goal #3

Goal #3 is to provide a unified interface so that the same code can be used polymorphically to consume either an IDataReader or a DataRow.

The IDataReader and DataRow have very different APIs. In some instances, an application might require an object to be retrieved via an IDataReader for optimal performance. In other cases, the data might be retrieved as part of a DataSet (if it is part of a larger query). It would be ideal to be able to program against the same interface polymorphically in either case.


The INullableReader interface defines a contract that a class must implement in order to read both nullable and non-nullable data. This will not only provide a unified interface between an IDataReader and a DataRow but also it will allow the classes to be used polymorphically. The interface definition ensures that all of the GetXXX() methods from the IDataReader have corresponding GetXXX() methods that take a string (for the column name) instead of an ordinal. Additionally, each of these GetXXX() methods have GetNullableXXX() counterparts.

Interface definition:

public interface INullableReader
    bool GetBoolean(string name);
    Nullable<bool> GetNullableBoolean(string name);
    byte GetByte(string name);
    Nullable<byte> GetNullableByte(string name);
    char GetChar(string name);
    Nullable<char> GetNullableChar(string name);
    DateTime GetDateTime(string name);
    Nullable<DateTime> GetNullableDateTime(string name);
    decimal GetDecimal(string name);
    Nullable<Decimal> GetNullableDecimal(string name);
    double GetDouble(string name);
    Nullable<double> GetNullableDouble(string name);
    float GetFloat(string name);
    Nullable<float> GetNullableFloat(string name);
    Guid GetGuid(string name);
    Nullable<Guid> GetNullableGuid(string name);
    short GetInt16(string name);
    Nullable<short> GetNullableInt16(string name);
    int GetInt32(string name);
    Nullable<int> GetNullableInt32(string name);
    long GetInt64(string name);
    Nullable<long> GetNullableInt64(string name);
    string GetString(string name);
    string GetNullableString(string name);
    object GetValue(string name);
    bool IsDBNull(string name);

Although this interface provides GetValue() and IsDBNull() methods, these are more for completeness, and will typically not be used in code.


The NullableDataReader implements the INullableReader interface and provides a wrapper around an IDataReader object. Therefore, this works with SqlDataReader, OracleDataReader, etc. There is even a new class in ADO.NET 2.0, called DataTableReader, which can be wrapped as well.

To instantiate a NullableDataReader, simply pass the IDataReader to the constructor. Example with the Enterprise Library Data Access block:

dr = new NullableDataReader(db.ExecuteReader(cmd));

Example with raw ADO.NET:

dr = new NullableDataReader(cmd.ExecuteReader());

To read values, simply refer to the column names:

person.Age = dr.GetInt32("Age");
person.FiredDate = dr.GetNullableDateTime("FiredDate");

The NullableDataReader also implements IDataReader. Therefore, the NullableDataReader can be used like any other data reader. For example:

    while (dr.Read())
      Person person = new Person();
      person.Age = dr.GetInt32("Age");
      person.FiredDate = 

The above code looks no different than that of any other data reader except:

  1. the GetInt32() methods take a column name instead of an ordinal, and
  2. a GetNullableDateTime() method is available which is not present on a normal data reader.


The NullableDataReader also implements the INullableReader interface and provides a wrapper around a DataRow object. Because it provides all of the strongly-typed methods, the access code need not contain casts and conversions.

Instantiate a NullableDataRow, by passing a DataRow to the constructor, or by assigning the DataRow to the Row property.

If reading a single row, then passing a DataRow to the constructor is the simplest:

NullableDataRowReader dr = new NullableDataRowReader(row);
person.Age = dr.GetInt32("Age");
person.FiredDate = dr.GetNullableDateTime("FiredDate");

Notice, the access methods look identical to that of the DataReader.

If reading multiple rows (e.g., while iterating through a loop), then assigning the DataRow to the Row property is the simplest:

NullableDataRowReader dr = new NullableDataRowReader();
foreach (DataRow row in dataTable.Rows)
    dr.Row = row;
    Person person = new Person();
    person.Age = dr.GetInt32("Age");
    person.FiredDate = dr.GetNullableDateTime("FiredDate");

In the above example, we just iterated all the rows of a DataTable for the sake of a simplistic example. In fact, in that example, you could simply use a NullableDataReader in conjunction with the new DataTableReader, like this:

NullableDataReader dr = new 

However, when working with DataTables, we often want to filter and also utilize GetChildRows(), which makes the NullableDataRowReader extremely convenient.

Polymorphic NullableReader

In some cases, we may need to populate a business object with a DataReader (for optimal performance), and other times populate the same object with a DataRow (e.g., if retrieved in a multi-resultset DataSet). Rather than having to write two separate methods (one for the NullableDataReader and another for the NullableDataRowReader), one can program against the INullableReader interface polymorphically and write just a single method.

public Address BuildItem(INullableReader dr)
    Address address = new Address();

    address.ID = dr.GetInt32(Params.AddressID);
    address.StreetAddress1 = dr.GetString(Params.StreetAddress1);
    address.StreetAddress2 = dr.GetString(Params.StreetAddress2);
    address.City = dr.GetString(Params.City);
    address.State = dr.GetString(Params.State);
    address.ZipCode = dr.GetString(Params.ZipCode);

    return address;

The BuildItem() method can be called in two different ways. First, with a NullableDataReader:

NullableDataReader dr = new 
person.Address = addressMapper.BuildItem(dr);

And secondly, with a NullableDataRowReader:

NullableDataRowReader dr = new 
person.Address = addressMapper.BuildItem(dr);

Implementation Details

Internally, the NullableDataReader and the NullableDataRowReader use many of the new C# 2.0 language features to produce concise, high-performance code. Specifically, they utilize:

  1. generics,
  2. delegate inference, and of course
  3. nullable types.

Of course, to consume the data readers, the developer is not required to be aware of any of these implementation details.

To illustrate the internal implementation, we will examine the GetInt32() and GetNullableInt32() methods of the NullableDataReader class. Since the NullableDataReader wraps an IDataReader via its constructor as a private member, the GetInt32() method simply delegates this method call to the wrapped reader:

public int GetInt32(int i)
    return reader.GetInt32(i);

To provide an overloaded method that takes a column name instead of an ordinal, the standard approach is used while shielding the implementation from the consumer:

public int GetInt32(string name)
    return reader.GetInt32(reader.GetOrdinal(name));

Up to this point, we haven't done anything terribly interesting (although the new overload has provided considerable convenience). To provide two overloads to the GetNullableInt32() method, we could do this:

public Nullable<int> GetNullableInt32(string name)
    return this.GetNullableInt32(reader.GetOrdinal(name));

public Nullable<int> GetNullableInt32(int index)
    Nullable<int> nullable;
    if (reader.IsDBNull(index))
        nullable = null;
        nullable = GetInt32(index);
    return nullable;

However, the problem here is that, while not complicated, the nullable assignment with the if statement in the second overload will essentially have to be duplicated in each GetNullableXXX() method for each of the different data types – the only differences being:

  1. the type of nullable, and
  2. the method called (e.g., the GetInt32() method in the else block above).

To address this issue and produce code that is more concise and elegant, we can utilize a generic method that includes passing a delegate which we now have available as part of the C# 2.0 anonymous methods functionality. Therefore, we can simply create one method that performs the assignment:

private Nullable<T> GetNullable<T>(int ordinal, 
            Conversion<T> convert) where T : struct
    Nullable<T> nullable;
    if (reader.IsDBNull(ordinal))
        nullable = null;
        nullable = convert(ordinal);
    return nullable;

First, notice that we are using a generic type and specified the constraint that T must be a value type (i.e., struct). Secondly, notice that the second argument of the method is actually a custom private delegate that was defined for our purposes:

private delegate T Conversion<T>(int ordinal);

This makes it possible for all of the GetNullableXXX() methods to simply require a single line of code (rather than its own if statement):

public Nullable<INT> GetNullableInt32(int index)
    return GetNullable<INT>(index, GetInt32);

Notice that the second argument called is actually utilizing C# 2.0 delegate inference, and specifying that the normal GetInt32() method should be invoked in order to make the assignment in the case where the value is not DBNull.

Having the ability to utilize the same method for every GetNullableXXX() method results in code that is more concise, less error-prone, and more maintainable.


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Steve Michelotti
Web Developer
United States United States
Steve Michelotti, MCSD, MCT is Principal Developer at e.magination in Baltimore -

You may also be interested in...

Comments and Discussions

QuestionHow to handle row versions? Pin
jaycgibson3-Feb-06 14:05
memberjaycgibson3-Feb-06 14:05 
AnswerRe: How to handle row versions? Pin
jaycgibson3-Feb-06 14:14
memberjaycgibson3-Feb-06 14:14 
GeneralRe: How to handle row versions? Pin
Steve Michelotti4-Feb-06 17:22
memberSteve Michelotti4-Feb-06 17:22 
Thanks for the suggestion. I agree that this is a good enhancement and it makes the implementation more complete. Providing the overload (as you've done in your example) is the cleanest way to accomodate (and matches well with the existing DataRow API). However, you'll also have to add an overload for:

public bool? GetNullableBoolean(string name, DataRowVersion version)

and all other GetNullableXXX methods. Again, to do this, the easiest way is to use a generic method with delegate inference (where you pass the delegate to the method).

I've already re-submitted the updated code with all overloads in it (hopefully the admins of the code project will have this update up in the next couple of days). If you can't wait, then you can implement yourself by following these steps.

First, define the delegate you'll use for the new methods:
private delegate T ConversionWithVersion<T>(string name, DataRowVersion version);

Next, define the generic method that each new GetNullableXXX method will call (note this is an overload of this existing method):

private Nullable<T> GetNullable<T>(string name, DataRowVersion version, ConversionWithVersion<T> convert) where T : struct<br />
{<br />
	Nullable<T> nullable;<br />
	if (row[name, version] == DBNull.Value)<br />
	{<br />
		nullable = null;<br />
	}<br />
	else<br />
	{<br />
		nullable = convert(name, version);<br />
	}<br />
	return nullable;<br />

Last, provides GetNullableXXX(string name, DataRowVersion version) for each datatype - use this is an example:
public bool? GetNullableBoolean(string name, DataRowVersion version)<br />
{<br />
    return GetNullable<bool>(name, version, GetBoolean);<br />

P.S. The updated code that will soon be published for the article also contains a performance enhancement to the DataRowReader to cache the ordinals in a generic dictionary.
GeneralShort form Pin
ELh125-Jan-06 1:52
memberELh125-Jan-06 1:52 
General.GetOrdinal Pin
XIUnin22-Jan-06 0:03
memberXIUnin22-Jan-06 0:03 
GeneralRe: .GetOrdinal Pin
Steve Michelotti4-Feb-06 17:26
memberSteve Michelotti4-Feb-06 17:26 
GeneralRe: .GetOrdinal Pin
Vlad Bezden13-Mar-07 7:57
memberVlad Bezden13-Mar-07 7:57 
AnswerRe: .GetOrdinal Pin
JocularJoe1-Feb-11 0:35
memberJocularJoe1-Feb-11 0:35 
GeneralIs this a bug? (oops, no it's not) Pin
Marc Clifton20-Jan-06 9:38
protectorMarc Clifton20-Jan-06 9:38 
GeneralRe: Is this a bug? (oops, no it's not) Pin
What were you thinking?24-Jan-06 7:45
memberWhat were you thinking?24-Jan-06 7:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170628.1 | Last Updated 20 Jan 2006
Article Copyright 2006 by Steve Michelotti
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid