DataTableReader Extension Methods

Today I would like to demonstrate something you can do with the DataTableReader to make it easier and more efficient to use. The first thing I want to demonstrate is the creation of an Extension Method that allows you to emulate the SQL ISNULL function. The SQL ISNULL function tests a value for null, and if it is null, returns the second parameter passed into the function.

DECLARE @NULL VARCHAR(10) = NULL
SELECT ISNULL(@Null, ‘YES’) as IsItNull


The result of the select statement IsItNull = ‘YES’

I emulated the ISNULL SQL function in C# Using an extension method. To keep the example simple I created a separate extension for each data type. You could probably make a single extension using Generics. Here is an example extension method to get a string value from a data table column.

internal static string GetDbNullString
(
   this DataTableReader reader, 
   int ordinalPosition, 
   string valueIfNull
)
{
   var result = reader.IsDBNull(ordinalPosition) 
         ? valueIfNull 
         : reader.GetString(ordinalPosition);
   return result;
}

With this extension in your project, when you have a DataTableReader object it will now have another method appear “GetDbNullString”. You call this method just like any other method supported by the DataTableReader. Now you can find the value of column 3 as a string, and return a default value if it is null. It would look like this if you have a DataTableReader object called reader that has a current row:

string colum3Value = reader.GetDbNullString(3, “Column 3 Was Null”);

If you would like to see a working example of this solution I have an example on .Net Fiddle at https://dotnetfiddle.net/mLSnii.

Cheers,

Ben