C# 没有结果返回时处理 ExecuteScalar()

Handling ExecuteScalar() when no results are returned

提问by Hemant Kothiyal

I am using the following SQL query and the ExecuteScalar()method to fetch data from an Oracle database:

我正在使用以下 SQL 查询和ExecuteScalar()从 Oracle 数据库中获取数据的方法：

sql = "select username from usermst where userid=2"
string getusername = command.ExecuteScalar();

It is showing me this error message:

它向我显示此错误消息：

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

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

This error occurs when there is no row in the database table for userid=2.
How should I handle this situation?

当数据库表中没有userid=2.
我该如何处理这种情况？

回答by Rune Grimstad

First you should ensure that your command object is not null. Then you should set the CommandText property of the command to your sql query. Finally you should store the return value in an object variable and check if it is null before using it:

首先，您应该确保您的命令对象不为空。然后您应该将命令的 CommandText 属性设置为您的 sql 查询。最后，您应该将返回值存储在一个对象变量中，并在使用之前检查它是否为空：

command = new OracleCommand(connection)
command.CommandText = sql
object userNameObj = command.ExecuteScalar()
if (userNameObj != null)
  string getUserName = userNameObj.ToString()
 ...

I'm not sure about the VB syntax but you get the idea.

我不确定 VB 语法，但你明白了。

回答by jjj

this could help .. example::

这可以帮助.. 示例::

using System;
using System.Data;
using System.Data.SqlClient;

class ExecuteScalar
{
  public static void Main()
  {
    SqlConnection mySqlConnection =new SqlConnection("server=(local)\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
    mySqlCommand.CommandText ="SELECT COUNT(*) FROM Employee";
    mySqlConnection.Open();

    int returnValue = (int) mySqlCommand.ExecuteScalar();
    Console.WriteLine("mySqlCommand.ExecuteScalar() = " + returnValue);

    mySqlConnection.Close();
  }
}

from this here

从这里开始

回答by Richard

Slight conjecture: if you check the stack for the exception, it is being thrown then the ADO.NET provider for Oracle is reading the underlying rowset to get the first value.

轻微的猜想：如果您检查堆栈中的异常，它正在被抛出，那么 Oracle 的 ADO.NET 提供程序正在读取底层行集以获取第一个值。

If there is no row, then there is no value to find.

如果没有行，则没有要查找的值。

To handle this case execute for a reader and handle Next()returning false for the case of no match.

要处理这种情况，请为读取器执行，并Next()在不匹配的情况下处理返回 false。

回答by Tommy Carlier

The following line:

以下行：

string getusername = command.ExecuteScalar();

... will try to implicitly convert the result to string, like below:

...将尝试将结果隐式转换为字符串，如下所示：

string getusername = (string)command.ExecuteScalar();

The regular casting operator will fail if the object is null. Try using the as-operator, like this:

如果对象为空，则常规转换运算符将失败。尝试使用 as-operator，如下所示：

string getusername = command.ExecuteScalar() as string;

回答by Branko Dimitrijevic

According to MSDN documentation for DbCommand.ExecuteScalar:

根据DbCommand.ExecuteScalar 的 MSDN 文档：

If the first column of the first row in the result set is not found, a null reference (Nothing in Visual Basic) is returned. If the value in the database is null, the query returns DBNull.Value.

如果未找到结果集中第一行的第一列，则返回空引用（在 Visual Basic 中为 Nothing）。如果数据库中的值为空，则查询返回 DBNull.Value。

Consider the following snippet:

考虑以下片段：

using (var conn = new OracleConnection(...)) {
    conn.Open();
    var command = conn.CreateCommand();
    command.CommandText = "select username from usermst where userid=2";
    string getusername = (string)command.ExecuteScalar();
}

At run-time (tested under ODP.NET but should be the same under any ADO.NET provider), it behaves like this:

在运行时（在 ODP.NET 下测试，但在任何 ADO.NET 提供程序下都应该相同），它的行为如下：

• If the row does not exist, the result of command.ExecuteScalar()is null, which is then casted to a null string and assigned to getusername.
• If the row exists, but has NULL in username (is this even possible in your DB?), the result of command.ExecuteScalar()is DBNull.Value, resulting in an InvalidCastException.

• 如果该行不存在，则 的结果command.ExecuteScalar()为空，然后将其强制转换为空字符串并分配给getusername。
• 如果该行存在，但用户名中为 NULL（这在您的数据库中是否可能？），则结果command.ExecuteScalar()is DBNull.Value，导致InvalidCastException.

In any case, the NullReferenceExceptionshould not be possible, so your problem probably lies elsewhere.

无论如何，这NullReferenceException不应该是可能的，所以您的问题可能出在其他地方。

回答by Fanda

I just used this:

我只是用这个：

    int? ReadTerminalID()
    {
        int? terminalID = null;

        using (FbConnection conn = connManager.CreateFbConnection())
        {
            conn.Open();
            FbCommand fbCommand = conn.CreateCommand();
            fbCommand.CommandText = "SPSYNCGETIDTERMINAL";
            fbCommand.CommandType = CommandType.StoredProcedure;

            object result = fbCommand.ExecuteScalar(); // ExecuteScalar fails on null
            if (result.GetType() != typeof(DBNull))
            {
                terminalID = (int?)result;
            }
        }

        return terminalID;
    }

回答by MAX

In your case either the record doesn't exist with the userid=2or it may contain a null value in first column, because if no value is found for the query result used in SQL command, ExecuteScalar()returns null.

在您的情况下，该记录不存在，userid=2或者它可能在第一列中包含空值，因为如果未找到 SQL 命令中使用的查询结果的值，则ExecuteScalar()返回null.

回答by zam

Try this

尝试这个

sql = "select username from usermst where userid=2"

string getusername = Convert.ToString(command.ExecuteScalar());

回答by some_yahoo

This is the easiest way to do this...

这是最简单的方法来做到这一点......

sql = "select username from usermst where userid=2"
object getusername = command.ExecuteScalar();
if (getusername!=null)
{
    //do whatever with the value here
    //use getusername.toString() to get the value from the query
}

回答by panky sharma

I Use it Like This with Microsoft Application Block DLL (Its a help library for DAL operations)

我像这样使用 Microsoft Application Block DLL（它是 DAL 操作的帮助库）

public string getCopay(string PatientID)
{
       string sqlStr = "select ISNULL(Copay,'') Copay from Test where patient_id=" + PatientID ;
        string strCopay = (string)SqlHelper.ExecuteScalar(CommonCS.ConnectionString, CommandType.Text, sqlStr);
                if (String.IsNullOrEmpty(strCopay))
                    return "";
                else
                    return strCopay ;
}