Sql查询数据读取器即使返回True';s在C#中为False
本文关键字:False 中为 True 读取 数据 查询 返回 Sql | 更新日期: 2023-09-27 17:59:07
您好!
为什么我的查询即使是假的也会返回true,我花了好几个小时。
这是我的密码。
public SqlDataReader Check(BEL bel) {
SqlCommand cmd = new SqlCommand();
cmd.Connection = dbcon.getcon();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT SUM(Total) as OverallTotal FROM table WHERE Id=@Id AND DateFrom=@From AND DateTo=@To AND Status='Without'";
cmd.Parameters.AddWithValue("@Id",bel.CLEmpID);
cmd.Parameters.AddWithValue("@From", bel.CLPayrollFrom);
cmd.Parameters.AddWithValue("@To", bel.CLPayrollTo);
SqlDataReader dr = cmd.ExecuteReader();
return dr;
}
假设参数化值为:
@Id = 0001
@From = 1/28/2016
@To = 1/29/2016
这是我调用数据读取器的方法
SqlDataReader drCheck;
drCheck = bal.Check(bel);
if (drCheck.HasRows == true)
{
drCheck.Read();
// I'm inside the computation of OverallTotal
}else{
drCheck.Close();
// I'm out
}
drCheck.Close();
问题是,例如,当我的"To"的值为2016年1月30日时,它假设为false,这超出了true条件,但事实并非如此。
请帮忙。提前感谢
在您的查询中,您有聚合函数。无论您来自&为了不返回任何行,因为您有聚合函数,所以您总是得到一个值至少为零的行。
在您不需要阅读器的情况下,您可以使用ExecuteScalar:
public decimal? Check(BEL bel)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = dbcon.getcon();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT SUM(Total) as OverallTotal FROM table WHERE Id=@Id " +
"AND DateFrom=@From AND DateTo=@To AND Status='Without'";
cmd.Parameters.AddWithValue("@Id", bel.CLEmpID);
cmd.Parameters.AddWithValue("@From", bel.CLPayrollFrom);
cmd.Parameters.AddWithValue("@To", bel.CLPayrollTo);
object obj = cmd.ExecuteScalar();
decimal? value = null;
if (obj != DBNull.Value)
value = Convert.ToDecimal(obj);
return value;
}
decimal? total = bal.Check(bel);
if (total.HasValue)
{
// do something with the total.Value
}
else
{
}
看看的简单示例
SELECT SUM(total1) as tt1, SUM(total2) AS tt2, COUNT(*) AS [Nbr of rows]
FROM (
SELECT 10 AS total1, CAST(NULL AS INT) total2 --
) t
--WHERE 1=2
通常,当SUMmed列可以为null时,只有COUNT(*)会告诉差异是选择了任何行还是没有选择行。
即使没有匹配的记录,您的查询也总是返回一个值,因为您正在对它们求和,所以在没有记录的情况下,您会得到NULL
,而在所有匹配的记录的值都是NULL
的情况下。如果有记录,并且总和结果为0(包括将被计为0的NULL
值),则会得到0。
您可以使用以下方法,使用不同的查询来返回这两个信息:
public bool GetOverAll(int id, DateTime payrollFrom, DateTime payrollTo, out double? overall)
{
overall = null;
string sql = @"
;WITH Data AS(
SELECT t.*
FROM table
WHERE Id = @Id
AND DateFrom = @From
AND DateTo = @To
AND Status = 'Without'
)
SELECT HasRows = CAST(CASE WHEN EXIST( SELECT 1 FROM CTE )
THEN 1 ELSE 0 END AS bit),
OverallTotal = SUM(Total)
FROM CTE";
using (var con = new SqlConnection("connectionstring"))
using (var cmd = new SqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("@Id", id);
cmd.Parameters.AddWithValue("@From", payrollFrom);
cmd.Parameters.AddWithValue("@To", payrollTo);
con.Open();
using (var rd = cmd.ExecuteReader())
{
if (rd.Read())
{
bool hasRows = rd.GetBoolean(0);
if (!rd.IsDBNull(1))
overall = rd.GetDouble(1);
return hasRows;
}
else
{
throw new Exception("This should never be the case!");
}
}
}
}
你现在知道是否有匹配的记录:
double? overall;
bool hasRows = GetOverAll(id, payrollFrom, payrollTo, out overall);
if(hasRows && overall.HasValue)
{
// matching records and the sum of these values was not NULL (possible if nullable column and all values were NULL)
double total = overall.Value;
}