DBNull check for ExecuteScalar

Question

The stored procedure for command can return null.Is it correct way to check if the returned value is null or should I also check that obj is null?

object obj = command.ExecuteScalar();
int id = -1;
if (DBNull.Value == obj)
{
   id = Convert.ToInt32(obj );
}

Answer 1

You probably want to change your if-statement to

if (obj != null && DBNull.Value != obj) { 
    ... 
}

Right now you're trying to convert if obj == DBNull.Value.

Answer 2

If there is no result your query can return null, so in the general case you should check for it. E.g.:

SELECT TOP 1 Col1 FROM TABLE WHERE ...

The above query can return:

• null if there are no rows matching the WHERE clause
• DBNull.Value if the first matching row has a NULL value in Col1
• else a non-null value

If your query is such that you can guarantee there will always be a result, you only need to check for DBNull. E.g.

SELECT MAX(Col1) FROM TABLE WHERE ...

The above query will return DBNull.Value if there are no rows matching the WHERE clause. It never returns null.

And of course there are some cases where you can guarantee a non-null result, in which case you don't need to test for null or DBNull. E.g.

SELECT COUNT(Col1) FROM TABLE WHERE ...
SELECT ISNULL(MAX(Col1),0) FROM TABLE WHERE ...

The above query will always return a non-null value. It never returns null or DBNull.Value.