Handling null values returned via SQL ExecuteScalar() query

[Anodes]

How do I test for and handle a return of null in the following code?

try
{
OleDbConnection connection = new
OleDbConnection(connectionString);
connection.Open();
OleDbCommand command = new OleDbCommand(SQL_Query,
connection);
scalarReturned =
command.ExecuteScalar().ToString();
command.Dispose();
connection.Close();
}
catch (Exception ex)
{

}

This is part of a data-handling class I'm building, and I use this
method for pulling single values from one of my lookup tables where
the structure is fixed. It works fine; however I want to be able to
handle when the query returns null...of course trying to convert null
ToString() generates an error.

I suppose I could just trap that error, but I'd rather design it so no
error is generated.

Thanks.

 

[Marc Gravell]

IIRC - ExecuteScalar returns null if no value is returned, and
DBNull.Value if a null is returned. So test the value for these two
cases before going further. You could also try using
Convert.ToString(...) which handles null (don't know about DBNull).

Note you can test for DBNull using "foo is DBNull".
Note also that it is more reliable to use "using" than Dispose().

Marc

 

[Jeroen Mostert]

IIRC - ExecuteScalar returns null if no value is returned, and
DBNull.Value if a null is returned. So test the value for these two
cases before going further. You could also try using
Convert.ToString(...) which handles null (don't know about DBNull).

Using Convert.ToString() is rather pointless, since Convert.ToString(null)
returns null (the reason for this is the rather interesting overload
resolution that C# uses) while Convert.ToString(DBNull.Value) returns the
empty string. It's unlikely this is what you want, and even if it is,
readers of your code are probably not going to grasp immediately what's
happening. Better to test explicitly.

Note also that it is more reliable to use "using" than Dispose().

To be precise, "using" just calls Dispose(), but in a finally block.

 

[Marc Gravell]

IIRC it also handles the scenario when the disposable object is
initialized as null.

But yes - getting disposed on exception is the point I meant by "more
reliable".

Marc

 

[Anodes]

IIRC it also handles the scenario when the disposable object is
initialized asnull.

But yes - getting disposed on exception is the point I meant by "more
reliable".

Marc

Final working code:
object oScalarReturned = new Object();
oScalarReturned = command.ExecuteScalar();
if (oScalarReturned is DBNull || oScalarReturned == null)
scalarReturned = "";
else
scalarReturned = oScalarReturned.ToString();