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:

to be able to reference everything with a readable column name (rather than an ordinal), and

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:

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.

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:

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

the GetInt32() methods take a column name instead of an ordinal, and

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:

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:

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.

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:

generics,

delegate inference, and of course

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:

publicint 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:

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:

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:

the type of nullable, and

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:

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:

privatedelegate 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):

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.

License

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.

This INullableReader interface look great and I think I'm gonna use it to read data from a database into my application.
However, I also want to write nullables back to the database using a DataTable/DataSet and a DataAdapter. Should I write my own NullableWriter to accomplish this, or is there a better solution?

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):

I've been writing an ORM (Object Relational Mapper) for a school project and I was using GetOrdinal like you do it. Once you start reading 1000 rows and different collumns per row the performance drops soo hard.
So what I did was use a wrapper for the GetOrdinal method that caches the ordinals untill a new query/stored procedure was called.

It doesn't work with Nullable types which is pretty cool but here is an example:

Thanks for the suggestion. Actually, this was an enhancement that I was planning on making and I agree (I've benchmarked it) and yes, there is a significant performance increase especially when the number of rows get large. I've submitted the updated code (will probably be available in the next couple of days) for the article and it does look quite similar to your example except that I used a System.Collections.Generic.Dictionary<string, int=""> rather than a SortedList<string, int="">.

I don't agree that caching the result of GetOrdinal is a good idea in a general-purpose library like this.

The dictionary you use for caching would have to use the same IComparer as is used by the underlying IDataReader's GetOrdinal method (case sensitivity, locale-awareness, ...) to compare field names. And in the general case, you don't know this.