C# 2.0 Nullable Data Readers

• Download source - 6.75 Kb

Introduction

.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;
else
    person.FiredDate = 
      reader.GetDateTime(reader.GetOrdinal("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.

INullableReader

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.

NullableDataReader

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:

try
{
    while (dr.Read())
    {
      Person person = new Person();
      person.Age = dr.GetInt32("Age");
      person.FiredDate = 
        dr.GetNullableDateTime("FiredDate");
      personList.Add(person);
    }
}
finally
{
    dr.Dispose();
}

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.

NullableDataRowReader

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");
    personList.Add(person);
}

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 
   NullableDataReader(dataTable.CreateDataReader());

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 
       NullableDataReader(db.ExecuteReader(cmd));
person.Address = addressMapper.BuildItem(dr);

And secondly, with a NullableDataRowReader:

NullableDataRowReader dr = new 
      NullableDataRowReader(addressTable.Rows[0]);
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;
    }
    else
    {
        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;
    }
    else
    {
        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.