vb.net 在VB.NET中将dbNull转换为字符串的简单方法

Simple way to convert dbNull to a string in VB.NET

提问by user2276280

I'm looking for a simpler way to check if a value is dbNull and to convert it to an empty string if so.

我正在寻找一种更简单的方法来检查值是否为 dbNull，如果是，则将其转换为空字符串。

An example of a situation where I need this would be:

我需要这种情况的一个例子是：

Dim dt As New DataTable
Dim conn As New OleDbConnection(someConnStr)
Dim adap As New OleDbDataAdapter(qryCSSInfo, cssConn)
adap.Fill(dt)


Dim someStr As String = "The first column of the first row returned: " & dt.rows(0).item(0)
Msgbox(someStr)

The problem is that if dt.rows(0).item(0) is null in the database it will be returned as a dbNull value, which can apparently not be appended to a string.

问题是，如果 dt.rows(0).item(0) 在数据库中为空，它将作为 dbNull 值返回，显然不能附加到字符串。

My solution to this problem has been using if statements to replace the value with blank strings:

我对这个问题的解决方案是使用 if 语句将值替换为空字符串：

Dim dt As New DataTable
Dim conn As New OleDbConnection(someConnStr)
Dim adap As New OleDbDataAdapter(qryCSSInfo, cssConn)
adap.Fill(dt)


If Not isDBNull(dt.rows(0).item(0)) then
     Dim someStr As String = "The first column of the first row returned: " & dt.rows(0).item(0)
Else
     Dim someStr As String = "The first column of the first row returned: " & ""
End If
Msgbox(someStr)

This works fine for my purposes, but it gets overwhelming if I have to make this check for every column I need to use in the table. Say I had 10 columns from the table that I wanted to display with this string. I'd have to make this check on each one to ensure they weren't null. Is there an easier or simpler way of doing so?

这对我的目的来说效果很好，但是如果我必须对表中需要使用的每一列进行检查，就会变得不知所措。假设我想用这个字符串显示表中的 10 列。我必须对每个人进行检查以确保它们不为空。有没有更简单或更简单的方法来做到这一点？

回答by haraman

For string types you can directly use it this way dt.rows(0).item(0).ToString(), without the Ifcondition

对于字符串类型，您可以直接使用这种方式dt.rows(0).item(0).ToString()，无需If条件

adap.Fill(dt)

Dim someStr As String = "The first column of the first row returned: " & dt.rows(0).item(0).ToString()

MsgBox(somestr)

i.e. you can completely omit the if statement. As per MSDNany DBNull value will be converted to EmptyString with .ToString()

即你可以完全省略 if 语句。根据MSDN，任何 DBNull 值都将转换为 EmptyString.ToString()

Also check this SO post Conversion from type 'DBNull' to type 'String'

还要检查这个 SO post Conversion from type 'DBNull' to type 'String'

However, for non-string database column types such as integers, doubles you must apply checks using IsDBNullto avoid any exceptions.

但是，对于非字符串数据库列类型，例如整数、双精度数，您必须应用检查IsDBNull以避免任何异常。

回答by N0Alias

You can leverage the If Operatorto reduce a few lines of code:

您可以利用If 运算符来减少几行代码：

Dim someStr As String = "The first column of the first row returned: " & _
                        If(dt.rows(0).item(0) Is DbNull.Value, String.Empty, dt.rows(0).item(0))

回答by rheitzman

You should be able to concatenate a null field with a string - it should convert to an empty string. That said row.IsNull(index) is a good test to use.

您应该能够将空字段与字符串连接起来 - 它应该转换为空字符串。也就是说 row.IsNull(index) 是一个很好的测试。

    SQL = "Select top 10 Region, CompanyName FROM Suppliers"
    Dim dt As DataTable = Gen.GetDataTable(SQL, scon)
    For Each row As DataRow In dt.Rows
        MsgBox(row("companyName") & " region: " & row("Region")) ' null allowed
        If row.IsNull("region") Then ' .Net test for Null
            MsgBox(row("companyName") & " region is null")
        Else
            'continue
        End If
    Next

You can also resolve this in the query - covert nulls to useful (or empty) strings. The example query is from SQL Server, I don't know if your DB supports COALESCE.

您还可以在查询中解决此问题 - 将空值转换为有用的（或空的）字符串。示例查询来自 SQL Server，我不知道您的数据库是否支持 COALESCE。

    MsgBox("COALESCE") ' SQL Server - may not be the same in ODBC databases
    SQL = "Select top 10 COALESCE(Region,'na') Region, CompanyName FROM Suppliers"
    dt = Gen.GetDataTable(SQL, scon)
    For Each row As DataRow In dt.Rows
        MsgBox(row("companyName") & " region: " & row("Region"))
    Next

---

---

Some coding notes:

一些编码说明：

    Dim dt As New DataTable
    Dim conn As New OleDbConnection(someConnStr)
    Dim adap As New OleDbDataAdapter(qryCSSInfo, cssConn)
    adap.Fill(dt)

    If Not IsDBNull(dt.Rows(0).Item(0)) Then ' in OP
        '...
    End If

    ' save some typing if you know there will be only one record
    ' will throw exception is no rows are returned, check for expected count
    Dim row As DataRow = dt.Rows(0)
    If Not IsDBNull(row(0)) Then
        '...
    End If
    ' or 
    If Not row.IsNull(0) Then
        '...
    End If

    ' note the fields can be accessed by name so you can avoid hard coding field position
    If Not row.IsNull("FieldName") Then
        '...
    End If

回答by David BS

The simplest way to do it is just add a "" after the field or string. Eg.:

最简单的方法是在字段或字符串后添加一个“”。例如。：

  dim EmptyString as string = Nullfield() & ""
  if EmptyString = ""
     ' in the sample, it should.
  end if

So, in your code you can use:

因此，在您的代码中，您可以使用：

 If dt.rows(0).item(0) & "" = "" then
      ' it should be...
 end if