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

Ceres's answer is probably the best given that it avoids any sort of null testing, but it's worth noting that the 'IIF' function would also work pretty well her. It's still going to do the test for null but it's much more compact than how Joe was originally doing it. Something like this should do the trick:

For Each rs As DataRow In sqlDataset.Tables(0).Rows

    Response.Write( IIF( IsDBNull(rs("column")), "", rs("column") ) )

Next

What's neat with this is you can substitute the "" for whatever you want to output if the value is in fact null ( a nice little added bonus. )

Here's some info on the 'IIF' function for those who don't know what it is:

http://msdn.microsoft.com/en-ca/library/27ydhh0d(v=vs.71).aspx

Answer 2

Dataset Extensions give you a clean way of doing and it's also strongly typed. The type must match the column type in the database though. If the database column can be null, then use a nullable type like below. The null values become Nothing for the returned nullable type.

For Each rs As DataRow In sqlDataset.Tables(0).Rows

    'If string, you can use this. Null becomes nothing for the string.
    Response.Write(rs.field(of String)("column"))

    'if it's another type
    Response.Write(rs.field(of Integer?)("column"))


Next

Answer 3

Here's a one-liner:

Response.Write(rs.IsNull("column") ? "" : rs("column"));

or make it an extension method:

public string GetValueOrBlankString(this DataRow rs, string column)
{
    return rs.IsNull(column) ? "" : rs(column).ToString();
}

then call it as:

Response.Write(rs.GetValueOrBlankString("column"));

Answer 4

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 ("").