how to handled null exception in Db.SingleDecimal?

Question

c = new TableCell();
decimal pembayaran = Db.SingleDecimal("Select (valuta) from ArInvoice where customerID='01');
c.Text = Cf.Num(pembayaran);
c.Attributes["style"] = "text-align: right;";
tr.Cells.Add(c);

it works when the query have a value, but it contains this error when the result is null "Object cannot be cast from DBNull to other types"

How I solve this problem?

Answer 1

The problem is with the following method call:

Db.SingleDecimal("Select (valuta) from ArInvoice where customerID='01');

Not sure what it does exactly since the method definition is not provided in the question, but I am guessing it tries to convert a DBNull value to another type. You need to show the exact definition of that method for better help, but you'd generally handle the DBNull with something like this:

var myDecimal = dr.IsDBNull(0) ? 0M : dr.GetDecimal(0); // get a decimal from the first column

Answer 2

I am not directly familiar with any tool with a SingleDecimal method, so I'll answer in the general sense:

When querying data from ADO.NET (directly or indirectly), it is pretty likely that (possibly both) you will get some kind of "reader" API (most likely IDataReader), or that you will get raw object values.

In the first case, you should have access to an IsDBNull method (per column); so call that method, and do whatever you want to do with nulls.

In the second case, you should check the value for DBNull, i.e. if(val is DBNull).

In both cases, what the code does next is up to the library. In both cases, you might need to think about two null-esque scenarios here: no rows, and a row with a single value.

Frankly, in both cases it would be easier to use a library/tool that has existing support for this. For example, with dapper, this would just be:

string customerId = "01";
decimal? pembayaran = Db.Query<decimal?>(
    "Select valuta from ArInvoice where customerID=@customerId",
    new { customerId }).Single();

which gives you:

• correct parameterisation
• null handling
• the same API for querying all data (Query<T>)
• the ability to use any LINQ you like for ordinality (Single(), First(), ToList(), etc)

If you wanted to use a default value (rather than an empty decimal? when the cell's value was null, you could just use:

decimal pembayaran = Db.Query<decimal?>(
    "Select valuta from ArInvoice where customerID=@customerId",
    new { customerId }).Single() ?? 0M;

here note the ?? 0M is just a null-coalescing operation to get a zero decimal in place of null.

Answer 3

i found the answer from my own question, hehe. I use select isnull((valuta),0) in sql, so it will replace with 0 when the result is DbNull.

decimal pembayaran = Db.SingleDecimal("Select isnull((valuta),0) from ArInvoice where customerID='01');

But if you want to take some action when the result is DbNull (not only set the default value) maybe u can try the Eren Ersönmez's or Marc Gravell's answer. Happy Coding :D