C#: That Pesky DBNull Error in Datatables

Datatables and Tableadapters are one of the most beautiful things about C# and Visual Studio, until you’re processing rows in your datatable and you get something that reads like “The value for column ‘your_column’ in table ‘your_table’ is DBNull.”  If you go a little further, you may get:  “Exception Details: System.InvalidCastException: Specified cast is not valid.”  You may go to your Datatable in your Dataset and start trying to change the NullValue for the offending column or trying to change the DefaultValue in the column properties, but chances are that the data type will not let you change that NullValue.  It will insist on “(Throw Exception)”.

Help for the DBNull Problem has Arrived!

The answer is so simple (at least in VS 2008 and greater, maybe before that too).  It is rather sad how much time I’ve wasted trying to deal with this problem.  Without further ado, the answer!

You’re probably using a Datarow object to get at the column values, probably buried in a foreach statement.  Instead of messing around with the specific column like  myRow.MyColumn, the Row object has an isNull method for each column in the row.  The method name takes the column’s name and puts “is” in front, and “null” behind.  So for a column called MyColumn, the method would be:   myRow.IsMyColumnNull();

Here’s a full example of dealing with nulls in a DateTime field. The logic is the same for Int or other numerics as well.


int iReq = 6196;
certdbTableAdapters.integration_procsTableAdapter taProcs = new certdbTableAdapters.integration_procsTableAdapter();
certdb.integration_procsDataTable dtProcs = taProcs.GetData(iReq);

certdb.integration_procsRow rowProc = dtProcs.Rows[0];

if(!rowProc.IsStartTimeNull()) 
{
    sProcDate = rowProc.StartTime.ToShortDateString();
}
else
{
    sProcDate = "";
}

The Craziness I Tried Before

Before I finally found the answer to this, I was using all sorts of crazy workarounds, including using multiple queries to be sure I never got a null situation, and using the fabulous Case statement within SQL statements. Here’s a little tast of a Case statement:

SELECT DISTINCT Process.ProcessID, Process.Name, Process.Description, Process.ResultLogStatusID,
Process.StatusTimeStamp, ResultLogStatus.Name AS ProcResult, 
CASE WHEN LogProcess.EndTime IS NULL THEN '1/1/1900' ELSE LogProcess.EndTime END as rortime, 
CASE WHEN LogProcess.Status IS NULL THEN 'missing' ELSE LogProcess.Status END as rorstatus 
FROM         RequirementTestLink 
LEFT JOIN Process ON Process.ProcessID = RequirementTestLink.ProcessID 
LEFT JOIN ResultLogStatus ON Process.ResultLogStatusID = ResultLogStatus.ResultLogStatusID 
LEFT JOIN LogProcess ON Process.ProcessID = LogProcess.ProcessID AND LogProcess.ResultOfRecord = 1 WHERE     (RequirementTestLink.RequirementID IN (" + sReqs + ")) ORDER BY Process.ProcessID

I tried lots of things…here are a couple others that didn’t work on my strongly typed datatables:

myRow.MyColumn != null
or
Convert.IsDBNull(myRow.MyColumn)

Neither worked! It’s a shame the answer was much simpler!

I hope this post will help someone else out there. Hopefully I’ll remember this post the next time I’m dealing with DBNulls.
Cheers,
Byron

,

One Response to C#: That Pesky DBNull Error in Datatables

  1. Nate April 12, 2010 at 11:04 am #

    This has saved my life. I was really pulling my hair out over this.
    It is not documented anywhere on MSDN.

    Thanks a bunch!

    @cardiffblogger

Powered by WordPress. Designed by Woo Themes