How do I correctly cast an item in a DataSet when it can potentially be null?

Question

I have a dataset being returned by a stored proc and one of the items in it can potentially be null. I'm trying to convert each row in the dataset to a strongly typed object but I can't seem to cast the null value properly.

I've created a mock up of my scenario as follows:

DataSet ds = new DataSet();
ds.Tables.Add(new DataTable());
ds.Tables[0].Columns.Add("Name", typeof(string));
ds.Tables[0].Columns.Add("Amount", typeof(decimal));
ds.Tables[0].Rows.Add("My Name Here", null); //create row with a null item

DataRow dataRow = ds.Tables[0].Rows[0];

Person p = new Person
{ 
    Name = (string)dataRow["Name"], 
    Amount = (decimal)dataRow["Amount"]
}

Unfortunately I'm getting the following exception: System.InvalidCastException: Specified cast is not valid.

If I try to use a nullable type (decimal?) I get this error: System.NotSupportedException: DataSet does not support System.Nullable<>.

In the debugger I've done the following tests on the value in dataRow["Amount"]:

dataRow["Amount"] is decimal (false)
dataRow["Amount"] is decimal? (false)
dataRow["Amount"] == null (false)
dataRow["Amount"] is object (true)

All I can establish is that it's some sort of object...which isn't particularly helpful.

Can any of you spot what I'm doing wrong?

Answer 1

You can also do a check for Null being returned by the database as:

if (dataRow["Amount"] is System.DBNull.Value)

That should enable you to check the value before you attempt to cast it to avoid that error message.

Answer 2

You can use dataRow.IsNull("Amount") or Convert.IsDBNull(dataRow["Amount"]) or (dataRow["Amount"] as Decimal) != null.

Answer 3

Have you tried using decimal.TryParse?

As in:

decimal result;

if (decimal.TryParse(dataRow["Amount"].ToString(), out result))
{
//do something
}