avoid checking for DataRow.IsDBNull on each column?

Question

My code is 2x longer than it would be if I could automatically set IsDBNull to "" or simply roll over it without an error.

This is my code:

Dim conn As New SqlConnection
conn.ConnectionString = Module1.DBConn2
Dim sqlCommand = New SqlCommand("SELECT * FROM table", conn)
conn.Open()
Dim sqlDataset As DataSet = New DataSet()
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdapter(sqlCommand)
sqlDataAdapter.Fill(sqlDataset)
conn.Close()

For Each rs As DataRow In sqlDataset.Tables(0).Rows
    If Not IsDBNull(rs("column")) Then
        Response.Write(rs("column"))
    Else
        Response.Write("")
    End If

    Response.Write("some stuff to write")

    If Not IsDBNull(rs("column2")) Then
        Response.Write(rs("column2"))
    Else
        Response.Write("")
    End If
Next

In that case I'd just like to type Response.Write(rs("column")) instead of the If statement, and if column IsDBNull then output an empty string.

How can I do this?

Many thanks in advance!

Answer 1

You could simply use String.Join and pass row.ItemArray:

For Each row As DataRow In sqlDataset.Tables(0).Rows
    Response.Write(String.Join("", row.ItemArray))
Next

That works since DBNull.ToString returns an empty string.

If you want to address every column, you can use the strongly typed DataRowExtensions.Field method which supports nullables and return null/Nothing for string. Then you could use the null-coalescing operator (?? in C#, If in VB).

Dim rowInfo = String.Format("{0}{1}{2}",
                            If(row.Field(Of String)("Column1"), ""),
                            If(row.Field(Of String)("Column2"), ""),
                            If(row.Field(Of String)("Column3"), ""))

However, note that String.Format will convert null/Nothing to "" implicitely anyway, so the If is redundant and just fyi.

MSDN:

If the object specified by index is a null reference (Nothing in Visual Basic), then the format item is replaced by the empty string ("").