Dot NET C Sharp cannot find SQL column where values are NULL or blank

Error message and syntax references: System.IndexOutOfRangeException: Cannot find column -1. IndexOf()

Recently discovered an issue when reading data into C# DataColumnCollection of C# DataColumn’s

How to read NULL values using DataColumnCollection and DataColumn…

The most efficient way to locate data from a specific column is this …

Database database = new Database(databaseTableName, connectionString); 
DataSet dataSet = database.getDataFromDatabase();
DataRowCollection dra = dataSet.Tables[databaseTableName].Rows;
DataColumnCollection dcc = dataSet.Tables[databaseTableName].Columns;

foreach (DataRow theRow in dra)
{
    theNeedle = theRow[dcc.IndexOf("TheColumnName")];
    //Produces the following error
    //System.IndexOutOfRangeException: Cannot find column -1.
}

Unfortunately if there are NULL or blank values in TheColumnName you will get the following error. System.IndexOutOfRangeException: Cannot find column -1.

There are a couple of reasons for this.

  • Firstly if you are using a product like SSIS to import data, like spread sheets, into your database the system you are using has invariably guessed the data types incorrectly [1].
  • Secondly, by default the .NET (C#) DataColumn does not allow NULL and so in the event that there is a column with a NULL value, it will be completely ignored (hence the error which reveals that the IndexOf(“theColumnYouAreLookingFor”) is equal to -1.

The way to get around this is to use the built in DataColumnCollection iterator to loop through all DataColumn objects (whilst setting the AllowDBNull value to true for each DataColumn). The example below does the same as the code above (except it does not fail when it encounters a NULL value).

Database database = new Database(databaseTableName, connectionString); 
DataSet dataSet = database.getDataFromDatabase();
DataRowCollection dra = dataSet.Tables[databaseTableName].Rows;
DataColumnCollection dcc = dataSet.Tables[databaseTableName].Columns;

foreach (DataRow theRow in dra)
{
    foreach (DataColumn dco in dcc)
    {
        dco.AllowDBNull = true;
        if (dco.ColumnName.ToString() == "TheColumnName")
        {
            string theValue = (string)theRow[dcc.IndexOf(dco.ColumnName.ToString())];
        }
    } 
}

Although the above code allows us to get the NULL values through, there is another problem.

You don’t know if the value is NULL or not until you get it!

If you run the above code you will get the following error …

Unable to cast object of type 'System.DBNull' to type 'System.String'

So what on earth do we do in the event that the value from the database may or may not be a string or null … insert curse words here!

“The Answer” is to use generics and a static function, as demonstrated below

public static T ConvertFromDBVal(object obj)
 {
    if (obj == null || obj == DBNull.Value)
    {
        return default(T); // returns the default value for the type
    }
    else
    {
        return (T)obj; 
    }
 }

This code allows us to pass the value from the database into the ConvertFromDBVal function and either get back a default C# object with the value of null or the object in its natural C# form (in this case a string).

Here’s how this all fits together…

public static T ConvertFromDBVal(object obj)
 {
    if (obj == null || obj == DBNull.Value)
    {
        return default(T); // returns the default value for the type
    }
    else
    {
        return (T)obj; 
    }
 }

Database database = new Database(databaseTableName, connectionString); 
DataSet dataSet = database.getDataFromDatabase();
DataRowCollection dra = dataSet.Tables[databaseTableName].Rows;
DataColumnCollection dcc = dataSet.Tables[databaseTableName].Columns;

foreach (DataRow theRow in dra)
{
    foreach (DataColumn dco in dcc)
    {
        dco.AllowDBNull = true;
        if (dco.ColumnName.ToString() == "TheColumnName")
        {
            string theValue = ConvertFromDBVal(theRow[dcc2.IndexOf(dco.ColumnName.ToString())]); 
        } 
    } 
}

[1] If you need more information on how your system guesses the data types, check out this article.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s