Using the SQL Command object, how can you check to see if the result set is empty?

Question

Using the ExecuteScalar method in the SQL Command object, how can you check to see if the result set is empty? I am using ASP.net, C#, and MS SQL 2008. Right now when I run the following code the Response.Write returns a 0 when the resultset is empty. But I would like to differentiate between 0 and empty resultsets because there are actual 0 values in my database.

Here is the current code behind:

cmd = new SqlCommand("usp_test", cn);
cmd.CommandType = CommandType.StoredProcedure;

cn.Open();
TestOuput = Convert.ToInt32(cmd.ExecuteScalar());
cn.Close();

Response.Write(TestOutput);

Thank you.

Answer 1

DbCommand.ExecuteScalar() returns the first column of the first row, or null if the result is empty. Your problem is caused by Convert.ToInt32() because it returns 0 for null.

You have to check the value returned by ExecuteScalar() for null and only call Convert.ToInt32() if it is not null.

Object result = command.ExecuteScalar();

if (result != null)
{
   Int32 value = Convert.ToInt32(result);
}
else
{
   // Handle the empty result set case
}

Answer 2

Check out the definition of ExecuteScalar. It returns an Object, which will have a null reference if the result set is empty.

The reason you are seeing zero is that Convert.ToInt32 returns a zero when given null. You need to check the return value from ExecuteScalar before you convert it to an int.

Answer 3

As you can see in here you can check if the result is null :

Return Value Type: System.Object The first column of the first row in the result set, or a null reference (Nothing in Visual Basic) if the result set is empty.

Answer 4

Execute scalar will return the first column of the first row in the result set. If there are no results it should return null.

My guess is that your stored procedure is returning a count and not the dataset, which is why you are seeing a 0.

Answer 5

ExecuteScalar returns null if the result set is empty (as @fallen888, et al. have said). The reason you are seeing zero is that Convert.ToInt32 returns a zero when given null. You need to check the return value from ExecuteScalar before you convert it to an int.

Answer 6

Why not at the SQL put ISNULL(@Return,0). Then,everything can be convert to Int32.