Handling ExecuteScalar() when no results are returned

Question

I am using the following SQL query and the ExecuteScalar() method to fetch data from an Oracle database:

sql = "select username from usermst where userid=2"
string getusername = command.ExecuteScalar();

It is showing me this error message:

System.NullReferenceException: Object reference not set to an instance of an object

This error occurs when there is no row in the database table for userid=2.
How should I handle this situation?

Answer 1

According to MSDN documentation for DbCommand.ExecuteScalar:

If the first column of the first row in the result set is not found, a null reference (Nothing in Visual Basic) is returned. If the value in the database is null, the query returns DBNull.Value.

Consider the following snippet:

using (var conn = new OracleConnection(...)) {
    conn.Open();
    var command = conn.CreateCommand();
    command.CommandText = "select username from usermst where userid=2";
    string getusername = (string)command.ExecuteScalar();
}

At run-time (tested under ODP.NET but should be the same under any ADO.NET provider), it behaves like this:

• If the row does not exist, the result of command.ExecuteScalar() is null, which is then casted to a null string and assigned to getusername.
• If the row exists, but has NULL in username (is this even possible in your DB?), the result of command.ExecuteScalar() is DBNull.Value, resulting in an InvalidCastException.

In any case, the NullReferenceException should not be possible, so your problem probably lies elsewhere.