CODEWITHSUNDEEP
c#sqlnullreferenceexceptionsqlclient
Unseens
Unseens

Reputation: 1

nullreferenceexception from ExecuteScalar()

I really don't know what to write here. I was just coding and this stuff happened.

connection.Open();
SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "select count(*) from Categories where Name = @Name group by CategoryID";
cmd.Parameters.AddWithValue("@Name", categoryName);
int count = (int)cmd.ExecuteScalar();

it errors at

int count = (int)cmd.ExecuteScalar();

I am expecting it to return a value of 1, since there's 1 row in the Categories table. However, it just throws an exception.

Upvotes: 0

Views: 52

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460238

If no categories match your filter, null is returned instead of 0. Then you cast null to int which causes the NRE. So you have to handle that:

object? result = cmd.ExecuteScalar();
int count =  result == null || result == DBNull.Value ? 0 : (int) result;

Maybe you could add it to an extension to avoid forgetting this:

public static class SqlCommandExtensions
{
    public static int ExecuteScalarToInt(this SqlCommand cmd)
    {
        object? result = cmd.ExecuteScalar();
        return result == null || result == DBNull.Value ? 0 : (int)result;
    }
}

Now it's easy to use:

int count = cmd.ExecuteScalarToInt();

As to the why null is returned at all if you use COUNT(*): You cannot expect any records to be returned when using a GROUP BY clause, when no records exist in your source. So with group by you are not counting the rows that exist after you filter by category-name(then you'd have to omit the GROUP BY), but you count the rows for each distinct category-name. Since your pre-filter does not return anything, you get null as result.

Upvotes: 2

Related Questions