Exception throw when trying to cast empty value

Question

I have a query where I receive information from a database in DataTable:

DataTable estimateCalculation = new DataTable();

estimateCalculation = db.ExeSQLEstimate("usp_Contracted_Calculation", param1, param2);

So now I get values as:

 var averageContractedAmount = (from DataRow dr in estimateCalculation.Rows select (decimal)dr["AverageContractedAmount"]).FirstOrDefault().ToString("C");

But in some scenarios dr can be empty so it throws an exception

System.InvalidCastException: 'The specified conversion is not valid

How can I validate if dr returns null, to not try to convert it to decimal?

You need to compare `dr["AverageContractedAmount"]`, not `estimateCalculation.Rows`, against `DBNull`. See [How to get nullable DateTime out of database](https://stackoverflow.com/questions/9503698/). — Dour High Arch, Sep 11 '18 at 23:25

Mayhem50 · Answer 1 · 2018-09-11T23:03:21.027

1

I Think you need something simple like this. If I've misunderstood please clarify. (Also Welcome)

DataTable dt = new DataTable();
if (dt.Rows.Count == 0)
    return;

It would also be worth looking at DataRow.IsNull()

if (!dataRow.IsNull());

I have not used this myself but looks like it could be ideal for what you're looking for.

edited Sep 11 '18 at 23:03

answered Sep 11 '18 at 22:56

Mayhem50

431
2
11

I think this should not gonna be work, because I receive object , I mean [0] but value is `{}` like an empty string – Pepe Sep 11 '18 at 23:03
@Pepe Your procedure returns a single row of data, is this correct? – Mayhem50 Sep 11 '18 at 23:05
I try with `if(estimateCalculation.Rows == DBNull.Value)`, but cannot use DBNull.Value in datarow Collection and yes it return only one row – Pepe Sep 11 '18 at 23:09
1

Rows is a collection so I don't think DBnull.value would work sorry. However that if statement is the same as row count. If you proc returns zero rows it's effectively null (ish) hence why row count would be ideal at that rate. – Mayhem50 Sep 11 '18 at 23:22

score 1 · Answer 2 · edited Sep 26 '18 at 18:30

You have basically 3 options to do the null-check:

Call DataRow.IsNull():

var row = (from DataRow dr in estimateCalculation.Rows select dr).FirstOrDefault();

if (row != null && !row.IsNull("AverageContractedAmount"))
{
    var averageContractedAmount = ((decimal)row["AverageContractedAmount"]).ToString("C");
}

Compare value to DbNull.Value. Note that when column in database contains null, DataRow["ColumnName"] does not return null but returns DbNull.Value instead.

var value = (from DataRow dr in estimateCalculation.Rows select dr["AverageContractedAmount"]).FirstOrDefault();

if (value != null && value != DBNull.Value)
{
    var averageContractedAmount = ((decimal)value).ToString("C");
}

Do run-time type-check of the value. This is especially nice when used with C# 7.0 pattern-matching syntax:

if ((from DataRow dr in estimateCalculation.Rows select dr["AverageContractedAmount"]).FirstOrDefault() is decimal value)
{
    var averageContractedAmount = value.ToString("C");
}

Sometimes you don't want to do if-else branching and just want to convert value to nullable type, usually using ternary conditional operator:

var averageContractedAmount = 
    (from DataRow dr in estimateCalculation.Rows
     select (dr.IsNull("Price")
                ? null
                : (decimal?)dr["AverageContractedAmount"])
    ).FirstOrDefault()
    ?.ToString("C");

Exception throw when trying to cast empty value

2 Answers2