SqlDataReader and SqlCommand using SUM()

Question

I have a simple query which sums a column in a table from SQL database when a condition is met.

decimal stock = 0;

SqlCommand cmd = new SqlCommand();
SqlDataReader myRdr;
cmd.Connection = connection;
cmd.CommandText = "SELECT SUM(stock) AS stock FROM table WHERE id=@id AND condition=@condition";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@id", SqlDbType.NVarChar, 15).Value = id;
cmd.Parameters.Add("@condition", SqlDbType.NVarChar, 15).Value = condition;

connection.Open();
myRdr = cmd.ExecuteReader();
if (myRdr.HasRows)
{
    myRdr.Read();
    stock += Convert.ToDecimal(myRdr1["stock"]);
}

myRdr1.Close();
cmd1.Dispose();
connection.Close();

It works ok if there are entries in the table that meet the condition. If there are no entries, it throws an exception saying I try to convert DBNull type to decimal, which is not possible. I tried it like this:

if (myRdr.HasRows)
{
    myRdr.Read();
    if (myRdr["stock"] != null)
    {
        stock += Convert.ToDecimal(myRdr1["stock"]);
    }
}

But it still throws an exception. Thank you for your help.

Answer 1

Just change your query a bit from this:

SELECT SUM(stock) AS stock
FROM table
WHERE id=@id AND condition=@condition

to this:

SELECT ISNULL(stock, 0) AS stock
FROM (
    SELECT SUM(stock) AS stock
    FROM table
    WHERE id=@id AND condition=@condition
) q