如何检查 DataReader 值是否为空?

2021-12-06 00:00:00 vb.net oracle

我正在编写通过 SQL 查询读取 Oracle 表的 VB.Net 代码.

I'm writing a VB.Net code that reads an Oracle Table through an SQL query.

SQL 查询可能会返回一些空列.我正在尝试检查这些列是否为空，但我收到错误 Oracle.DataAccess.dll 中发生类型为System.InvalidCastException"的异常，但未在用户代码中处理.该列包含一些空数据

The SQL query may return some null columns. I'm trying to check if these columns are null or not but I'm receiving the error An exception of type 'System.InvalidCastException' occurred in Oracle.DataAccess.dll but was not handled in user code. The column contains some Null Data

这是我的代码:

Dim Reader as OracleDataReader 
'Execute the query here...

Reader.Read()
If IsNothing(Reader.GetDateTime(0)) Then  'Error here !!
    'Do some staff 
end if

有人知道如何检查列是否为空吗?

Does anyone have an idea on how to check if a column is null please ?

谢谢

推荐答案

Nothing 表示对象尚未初始化，DBNull 表示数据未定义/缺失.有几种方法可以检查:

Nothing means an object has not been initialized, DBNull means the data is not defined/missing. There are several ways to check:

' The VB Function
If IsDBNull(Reader.Item(0)) Then...

GetDateTime 方法有问题，因为您要求它将非值转换为 DateTime.Item() 返回可以在转换之前轻松测试的对象.

The GetDateTime method is problematic because you are asking it to convert a non value to DateTime. Item() returns Object which can be tested easily before converting.

 ' System Type
 If System.DBNull.Value.Equals(...)

您也可以使用 DbReader.这仅适用于序数索引，不适用于列名:

You can also the DbReader. This only works with the ordinal index, not a column name:

If myReader.IsDbNull(index) Then

基于此，您可以将函数放在一起作为共享类成员，也可以重新编写为扩展以测试 DBNull 并返回默认值:

Based on that, you can put together functions either as Shared class members or reworked into Extensions to test for DBNull and return a default value:

Public Class SafeConvert
    Public Shared Function ToInt32(Value As Object) As Integer
        If DBNull.Value.Equals(Value) Then
            Return 0
        Else
            Return Convert.ToInt32(Value)
        End If
    End Function

    Public Shared Function ToInt64(Value As Object) As Int64
        If DBNull.Value.Equals(Value) Then
            Return 0
        Else
            Return Convert.ToInt64(Value)
        End If
    End Function

    ' etc
End Class

用法:

myDate = SafeConvert.ToDateTime(Reader.Item(0))

对于 DateTime 转换器，您必须决定返回什么.我更喜欢单独做这些.

For a DateTime converter, you'd have to decide what to return. I prefer to do those individually.

相关文章