How to properly pass an element to a DataSet when it could potentially be empty?

Question

I have a dataset returned by a stored procedure, and one of the elements in it could potentially be null. I am trying to convert every row in a dataset to a strongly typed object, but I can't seem to properly assign a null value.

I created a layout for my script 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 get the following exception: System.InvalidCastException: Specified cast is not valid.

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

In the debugger, I performed the following tests for 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 install is that it is some kind of object ... which is not particularly useful.

Can any of you determine what I am doing wrong?

Answer 1

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

Answer 2

You can also check to return Null to the database as follows:

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

This should allow you to check the value before trying to use it to avoid this error message.

Answer 3

Have you tried to use decimal.TryParse?

How in:

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