C# 4.0: Provides many new, useful and powerful features. One of those is “dynamic” type. “You can have a number of references with search keyword [Dynamic Type: C# 4.0 ].

I love to use it. I was facing a particular situation while executing sql statement (particulary returning a scalar value) and then returning the result.

I thought of using dynamic type: “A function made to execute the scalar sql statment and return the result as type dynamic.” By this i would be able to get any type of data without any explicit conversion.

So, i built one function to accept randomSql, execute it(Execute NonScalar), return the result.

public dynamic FunctionToReturnScalarValue(string aRandomSql)
{
///////lines for database connectivity and executing the
///////random sql
 
dynamic result= commandObject.ExecuteScalar();
 
return result;
 
}

Everything worked fine, even for handling null values I declared Nullable Variables (see a list of tips on Nullable Types in GPS)
But, suddenly to my surprise I found that it was still throwing error like “Cannot convert System.DBNull to Nullable ….” .

So the actual problem lies in the type, remember System.DBNull is something different from null. As i was returning the data type as dynamic, and whenever it encountered System.DBNull, it became System.DBNull.

Now i checked my dynamic result with System.DBNull, like

dynamic result = FunctionToReturnScalarValue(randomSql) ;
if(result != System.DBNull.Value)
{ requiredVariable = result;}

I checked it first for the null result, it worked fine, but when the Scalar Result was present in the DB, it again threw an exception   (something like Cannot convert Int32 to System.DBNull…)

What To DO??

The thing is, if your result is having some scalar value it’s type becomes automatically to the type of data returned (the very basic reason to use dynamic), similarly, when the data is DBNull(null from DataBase) its type becomes DBNull dynamically (not null) and hence the exception was thrown. (as i was not able to compare my valid data with System.DBNull)

How to handle this ? There are two ways :

First: instead of initializing dynamic value with the result, initialize with object and then check for System.DBNull

object result = FunctionExecutingScalar(randomSql) ;
if(result != System.DBNull.Value)
{ requiredVariable = Convert.TOType(result,RequiredType);}

But this again gives an overload for Type Conversion.

So, the second and the better alternative is to handle System.DBNull at function level from where the result is being returned (i.e. return null in case System.DBNull is encountered).

public dynamic FunctionToReturnScalarValue(string aRandomSql)
{
///////lines for database connectivity and executing the
///////Random SQL
 
dynamic tempVar = commandObject.ExecuteScalar();
 
if(tempVar !=System.DBNull.Value)
return tempVar;
else
return null
 
}