C# 使用 String result=Command.ExecuteScalar() 返回值在 result 返回 null 时发生错误

Return value using String result=Command.ExecuteScalar() error occurs when result returns null

提问by Satinder singh

I want to fetch 1st row 1st cell value from database it works well with below code . But when there is no result found it throws Exception.

我想从数据库中获取第 1 行第 1 个单元格值，它适用于以下代码。但是当没有找到结果时，它会抛出异常。

How to handle with DBNull.
Should i change my query ? which return some value if theirs no record ?

如何处理DBNull。
我应该改变我的查询吗？如果他们没有记录，它会返回一些值吗？

System.NullReferenceException: Object reference not set to an instance of an object.

System.NullReferenceException：未将对象引用设置为对象的实例。

Code:

代码：

    public string absentDayNo(DateTime sdate, DateTime edate, string idemp)
    { 
       string result="0";
       string myQuery="select COUNT(idemp_atd) absentDayNo from td_atd where ";
       myQuery +=" absentdate_atd between '"+sdate+"' and '"+edate+" ";
       myQuery +=" and idemp_atd='"+idemp+"' group by idemp_atd ";

       SqlCommand cmd = new SqlCommand(myQuery, conn);
       conn.Open();
//System.NullReferenceException occurs when their is no data/result
       string getValue = cmd.ExecuteScalar().ToString();
         if (getValue != null)
         {
            result = getValue.ToString();
         }
         conn.Close();
        return result;
    }

采纳答案by Darren

There is no need to keep calling .ToString()as getValueis already a string.

没有必要继续调用，.ToString()因为getValue它已经是一个字符串。

Aside that, this line could possibly be your problem:

除此之外，这条线可能是你的问题：

 string getValue = cmd.ExecuteScalar().ToString();  

If there are no rows .ExecuteScalarwill return nullso you need to do some checking.

如果没有行将.ExecuteScalar返回，null因此您需要进行一些检查。

For instance:

例如：

var firstColumn = cmd.ExecuteScalar();

if (firstColumn != null) {
    result = firstColumn.ToString();
}

回答by Saravanan

You can use like the following

你可以像下面这样使用

string result = null;
object value = cmd.ExecuteScalar();
 if (value != null)
 {
    result = value.ToString();
 }     
 conn.Close();
return result;

回答by Naresh Parmar

try this :

尝试这个 ：

 string getValue = Convert.ToString(cmd.ExecuteScalar());

回答by Dimitar Dimitrov

This should work:

这应该有效：

var result = cmd.ExecuteScalar();
conn.Close();

return result != null ? result.ToString() : string.Empty;

Also, I'd suggest using Parameters in your query, something like (just a suggestion):

另外，我建议在您的查询中使用参数，例如（只是一个建议）：

var cmd = new SqlCommand
{
    Connection = conn,
    CommandType = CommandType.Text,
    CommandText = "select COUNT(idemp_atd) absentDayNo from td_atd where absentdate_atd between @sdate and @edate and idemp_atd=@idemp group by idemp_atd"
};

cmd.Parameters.AddWithValue("@sdate", sdate);
cmd.Parameters.AddWithValue("@edate", edate);
// etc ...

回答by Rajeev Kumar

Try this one

试试这个

var getValue = cmd.ExecuteScalar();    
conn.Close();
return (getValue == null) ? string.Empty : getValue.ToString();

回答by Marc Gravell

If the first cell returned is a null, the result in .NET will be DBNull.Value

如果返回的第一个单元格是 a null，则 .NET 中的结果将是DBNull.Value

If no cells are returned, the result in .NET will be null; you cannot call ToString()on a null. You can of course capture what ExecuteScalarreturns and process the null/ DBNull/ other cases separately.

如果没有返回任何单元格，.NET 中的结果将为null；你不能叫ToString()上null。当然，你可以捕捉什么ExecuteScalar回报和过程null/ DBNull/其他情况下分开。

Since you are grouping etc, you presumably could potentially have more than one group. Frankly I'm not sure ExecuteScalaris your best option here...

由于您正在分组等，因此您可能拥有多个组。坦率地说，我不确定ExecuteScalar这里是你最好的选择......

---

---

Additional: the sql in the question is bad in many ways:

补充：问题中的sql在很多方面都不好：

• sql injection
• internationalization (let's hope the client and server agree on what a date looks like)
• unnecessary concatenation in separate statements

• sql注入
• 国际化（让我们希望客户端和服务器就日期的样子达成一致）
• 在单独的语句中不必要的串联

I strongly suggest you parameterize; perhaps with something like "dapper" to make it easy:

我强烈建议你参数化；也许用“dapper”之类的东西来让它变得容易：

int count = conn.Query<int>(
  @"select COUNT(idemp_atd) absentDayNo from td_atd
    where absentdate_atd between @sdate and @edate
    and idemp_atd=@idemp group by idemp_atd",
    new {sdate, edate, idemp}).FirstOrDefault();

all problems solved, including the "no rows" scenario. The dates are passed as dates (not strings); the injection hole is closed by use of a parameter. You get query-plan re-use as an added bonus, too. The group byhere is redundant, BTW - if there is only one group (via the equality condition) you might as well just select COUNT(1).

所有问题都解决了，包括“无行”场景。日期作为日期（不是字符串）传递；使用参数关闭注入孔。您还可以重用查询计划作为额外的奖励。在group by这里是多余的，顺便说一句-如果只有一组（通过平等条件下），你可能也只是选择COUNT(1)。

回答by Jānis

Value is not null, but DBNull.Value.

值不为空，而是 DBNull.Value。

object value = cmd.ExecuteScalar();
if(value == DBNull.Value)

回答by resolv

To work with NpgsqlCommand or the standard sqlCommand use:

要使用 NpgsqlCommand 或标准 sqlCommand 使用：

int result = int.Parse(cmd.ExecuteScalar().ToString());

回答by hussien

Use SQL server isnull function

使用 SQL server isnull 函数

public string absentDayNo(DateTime sdate, DateTime edate, string idemp)
{ 
    string result="0";
    string myQuery="select isnull(COUNT(idemp_atd),0) as absentDayNo from td_atd where ";
    myQuery +=" absentdate_atd between '"+sdate+"' and '"+edate+" ";
    myQuery +=" and idemp_atd='"+idemp+"' group by idemp_atd ";

    SqlCommand cmd = new SqlCommand(myQuery, conn);
    conn.Open();
    //System.NullReferenceException occurs when their is no data/result
    string getValue = cmd.ExecuteScalar().ToString();
    if (getValue != null)
    {
        result = getValue.ToString();
    }
    conn.Close();
    return result;
}

回答by Ramgy Borja

Try this one, if null set 0 or something

试试这个，如果 null set 0 或什么

return command.ExecuteScalar() == DBNull.Value ? 0 : (double)command.ExecuteScalar();