在 vb.net 中处理 dbnull 数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/222834/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
handling dbnull data in vb.net
提问by Azim
I want to generate some formatted output of data retrieved from an MS-Access database and stored in a DataTableobject/variable, myDataTable. However, some of the fields in myDataTable cotain dbNulldata. So, the following VB.net code snippet will give errors if the value of any of the fields lastname, intials, or sIDis dbNull.
我想生成一些从 MS-Access 数据库检索并存储在DataTable对象/变量 myDataTable 中的数据的格式化输出。但是，myDataTable 中的某些字段包含dbNull数据。因此，如果任何字段lastname、intials或sID 的值为dbNull，则以下 VB.net 代码片段将给出错误。
dim myDataTable as DataTable
dim tmpStr as String
dim sID as Integer = 1
...
myDataTable = myTableAdapter.GetData() ' Reads the data from MS-Access table
...
For Each myItem As DataRow In myDataTable.Rows
tmpStr = nameItem("lastname") + " " + nameItem("initials")
If myItem("sID")=sID Then
' Do something
End If
' print tmpStr
Next
So, how do i get the above code to work when the fields may contain dbNullwithout having to check each time if the data is dbNull as in this question?
那么，当字段可能包含dbNull时，如何让上述代码正常工作，而不必每次都检查数据是否为 dbNull ，如本问题所示？
回答by Mitchel Sellers
The only way that i know of is to test for it, you can do a combined if though to make it easy.
我所知道的唯一方法是对其进行测试，您可以进行组合以使其更容易。
If NOT IsDbNull(myItem("sID")) AndAlso myItem("sID") = sId Then
'Do success
ELSE
'Failure
End If
I wrote in VB as that is what it looks like you need, even though you mixed languages.
我是用 VB 写的，因为这看起来就像你需要的那样，即使你混合了语言。
Edit
编辑
Cleaned up to use IsDbNull to make it more readable
清理以使用 IsDbNull 使其更具可读性
回答by Steve Wortham
I got tired of dealing with this problem so I wrote a NotNull() function to help me out.
我厌倦了处理这个问题，所以我写了一个 NotNull() 函数来帮助我。
Public Shared Function NotNull(Of T)(ByVal Value As T, ByVal DefaultValue As T) As T
If Value Is Nothing OrElse IsDBNull(Value) Then
Return DefaultValue
Else
Return Value
End If
End Function
Usage:
用法：
If NotNull(myItem("sID"), "") = sID Then
' Do something
End If
My NotNull() function has gone through a couple of overhauls over the years. Prior to Generics, I simply specified everything as an Object. But I much prefer the Generic version.
多年来，我的 NotNull() 函数经历了几次大修。在泛型之前，我只是将所有东西都指定为一个对象。但我更喜欢通用版本。
回答by Mitchel Sellers
You can also use the Convert.ToString() and Convert.ToInteger() methods to convert items with DB null effectivly.
您还可以使用 Convert.ToString() 和 Convert.ToInteger() 方法有效地转换带有 DB null 的项目。
回答by Greg May
A variation on Steve Wortham's code, to be used nominally with nullabletypes:
Steve Wortham 代码的变体，名义上与nullable类型一起使用：
Private Shared Function GetNullable(Of T)(dataobj As Object) As T
If Convert.IsDBNull(dataobj) Then
Return Nothing
Else
Return CType(dataobj, T)
End If
End Function
e.g.
例如
mynullable = GetNullable(Of Integer?)(myobj)
You can then query mynullable(e.g., mynullable.HasValue)
然后您可以查询mynullable（例如，mynullable.HasValue）
回答by Christian Hayter
Microsoft came up with DBNull in .NET 1.0 to represent database NULL. However, it's a pain in the behind to use because you can't create a strongly-typed variable to store a genuine value or null. Microsoft sort of solved that problem in .NET 2.0 with nullable types. However, you are still stuck with large chunks of API that use DBNull, and they can't be changed.
Microsoft 在 .NET 1.0 中提出了 DBNull 来表示数据库 NULL。但是，使用起来很麻烦，因为您无法创建强类型变量来存储真正的值或 null。Microsoft 在 .NET 2.0 中使用可空类型解决了这个问题。但是，您仍然被大量使用 DBNull 的 API 所困扰，而且它们无法更改。
Just a suggestion, but what I normally do is this:
只是一个建议，但我通常做的是这样的：
- All variables containing data read from or written to a database should be able to handle null values. For value types, this means making them Nullable(Of T). For reference types (String and Byte()), this means allowing the value to be Nothing.
- Write a set of functions to convert back and forth between "object that may contain DBNull" and "nullable .NET variable". Wrap all calls to DBNull-style APIs in these functions, then pretend that DBNull doesn't exist.
- 所有包含从数据库读取或写入数据库的数据的变量都应该能够处理空值。对于值类型，这意味着将它们设为 Nullable(Of T)。对于引用类型（String 和 Byte()），这意味着允许值为 Nothing。
- 编写一组函数来在“可能包含 DBNull 的对象”和“可为空的 .NET 变量”之间来回转换。将所有对 DBNull 样式 API 的调用包装在这些函数中，然后假装 DBNull 不存在。
回答by John B
If you are using a BLL/DAL setup try the iif when reading into the object in the DAL
如果您使用 BLL/DAL 设置，请在读入 DAL 中的对象时尝试 iif
While reader.Read()
colDropdownListNames.Add(New DDLItem( _
CType(reader("rid"), Integer), _
CType(reader("Item_Status"), String), _
CType(reader("Text_Show"), String), _
CType( IIf(IsDBNull(reader("Text_Use")), "", reader("Text_Use")) , String), _
CType(reader("Text_SystemOnly"), String), _
CType(reader("Parent_rid"), Integer)))
End While
回答by brendan
You can use the IsDbNull function:
您可以使用 IsDbNull 函数：
If IsDbNull(myItem("sID")) = False AndAlso myItem("sID")==sID Then
// Do something
End If
回答by Azim
For the rows containing strings, I can convert them to strings as in changing
对于包含字符串的行，我可以将它们转换为字符串，如更改
tmpStr = nameItem("lastname") + " " + nameItem("initials")
to
到
tmpStr = myItem("lastname").toString + " " + myItem("intials").toString
For the comparison in the ifstatement myItem("sID")=sID, it needs to be change to
if语句中的比较myItem("sID")=sID，需要改成
myItem("sID").Equals(sID)
Then the code will run without any runtime errors due to vbNulldata.
然后，由于vbNull数据，代码将运行而不会出现任何运行时错误。
回答by BINU NARAYANAN NELLIYAMPATHI
VB.Net
========
Dim da As New SqlDataAdapter
Dim dt As New DataTable
Call conecDB() 'Connection to Database
da.SelectCommand = New SqlCommand("select max(RefNo) from BaseData", connDB)
da.Fill(dt)
If dt.Rows.Count > 0 And Convert.ToString(dt.Rows(0).Item(0)) = "" Then
MsgBox("datbase is null")
ElseIf dt.Rows.Count > 0 And Convert.ToString(dt.Rows(0).Item(0)) <> "" Then
MsgBox("datbase have value")
End If
回答by user3284874
This is BY FARthe easiest way to convert DBNullto a string.
The trick is that you CANNOTuse the TRIMfunction (which was my initial problem) when referring to the fields from the database:
这是迄今为止转换DBNull为字符串的最简单方法。诀窍是在引用数据库中的字段时，您不能使用该TRIM函数（这是我最初的问题）：
BEFORE(produced error msg):
之前（产生错误消息）：
Me.txtProvNum.Text = IIf(Convert.IsDBNull(TRIM(myReader("Prov_Num"))), "", TRIM(myReader("Prov_Num")))
AFTER(no more error msg :-) ):
之后（不再有错误消息 :-) ）：
Me.txtProvNum.Text = IIf(Convert.IsDBNull(myReader("Prov_Num")), "", myReader("Prov_Num"))
