c# - 在 ExecuteScalar : Object reference not set etc 之后

Question

我应该添加什么代码来接受来自 WHERE 语句的 null。

{
    int numApprovals = 0;
    string sql = "SELECT COUNT(Type) AS OpenforApproval " +
       "FROM dbo.LeaveRequest " +
       "WHERE Type IN (2, 3, 4, 5, 6, 8, 13, 14, 16, 22) " +
       "GROUP BY MgtApproval " +
       "HAVING MgtApproval IS NULL";
       //"SELECT COUNT(EffectiveDate) AS OpenforApproval FROM LeaveRequest GROUP BY TimeStampApproval HAVING (TimeStampApproval IS NULL)";

    using (cn = new SqlConnection(ConnectionString()))
    {
        cn.Open();
        using (cmd = new SqlCommand(sql, cn))
        {
            cmd.CommandType = CommandType.Text;
            numApprovals = (int)cmd.ExecuteScalar();
        }
    }

    return numApprovals;
}

Answer 1

只是:

WHERE Type IN (2, 3, 4, 5, 6, 8, 13, 14, 16, 22) OR Type IS NULL

但我完全不相信这是您真正想要的，或者是问题的原因。

如果您在 C# 代码中遇到异常，它不会来自 where 子句。

顺便说一下，我担心您的连接似乎在重复使用现有变量。馊主意。它几乎肯定是一个局部变量。您还可以通过从中间返回来简化代码:

string sql = ...;

using (var cn = new SqlConnection(ConnectionString()))
{
    cn.Open();
    using (cmd = new SqlCommand(sql, cn))
    {
        cmd.CommandType = CommandType.Text;
        return (int) cmd.ExecuteScalar();
    }
}    

如果问题如 Jamie 所说，ExecuteScalar 返回 null，最简单的解决方法是将其转换为可为 null 的 int 并使用 null 合并运算符:

return (int?) cmd.ExecuteScalar() ?? 0;