Thursday, October 5, 2017

Difference Between DBNull.Value and Null Reference

Let's say you are looking up some numerical value in a table, based on some id, like a PRODUCT ID, for example, the PRICE. 

If the table allows nulls for the PRICE (and in the real world, sometimes this happens, out of your control), then it is possible that there is a PRODUCT ID in the table that has a NULL price.  If you do a query in SQL Server Mgt Studio, you'll actually see it say in the cell, NULL.

On the other hand, let's say that the PRODUCT ID is erroneous, that is, it does not exist in the table.  So a query for the price would return no rows.

If you are using ExecuteScalar(), then you have two possible types of null situations to consider.

In the former situation, ExecuteScalar() would return an object which has the value of DBNull.Value.
In the latter, it would return null.

object returnValue = cmd.ExecuteScalar();
if(returnValue == DBNull.Value){
//table allows nulls for this field

if(returnValue == null) {
// no records were returned

if(returnValue != null && returnValue != DBNull.Value){
     double price = (double)returnValue;

No comments: