没有返回结果时处理 ExecuteScalar() [英] Handling ExecuteScalar() when no results are returned

问题描述

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

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

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

它向我显示此错误消息:

It is showing me this error message:

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

当userid=2的数据库表中没有行时会发生此错误.
这种情况应该怎么处理?

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

推荐答案

根据DbCommand.ExecuteScalar 的 MSDN 文档:

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

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.

考虑以下代码段:

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

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

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

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

• 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.

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

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

这篇关于没有返回结果时处理 ExecuteScalar()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！