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

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.