c# - 使用 SUM() 的 SqlDataReader 和 SqlCommand

Question

我有一个简单的查询，它在满足条件时对 SQL 数据库中的表中的列求和。

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();

如果表中有满足条件的条目，就可以正常工作。如果没有条目，它会抛出一个异常，说我尝试将 DBNull 类型转换为十进制，这是不可能的。我这样试过:

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

但是还是会抛出异常。感谢您的帮助。

Answer 1

只需稍微更改一下您的查询:

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

为此:

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