Safest way to handle null database items in VB.net

Question

For an object such as a DataGridView item or a data set item is the .tostring method safe to use against possible DBnull's?

The application is a mysqlconnector fed DB application which pretty much uses standard query's, but I do come across DBNull quite a bit. Keeping coding efficiency in mind, is the following safe?

Module DataCheck
Public Function SafeDataSTR(DBItem As Object) As String
    If Not (IsDBNull(DBItem)) Or Not (IsNothing(DBItem)) Then
        Return DBItem.ToString
    Else
        Return ""
    End If
End Function
End Module

'Elsewhere in the galaxy....'
With tempDS.Rows.Item(0)  'tempDS is a standard dataset'
Textbox1.Text = SafeDataSTR(.Item("SupplierDetails")) 'Not necessarily a text box, just an example'

The original solution was:

If Not IsDBNull(.Item("JobDescription")) Then _
     jobinfo.Job_Description = .Item("JobDescription")

Which has worked in the past but is there a better safer way to protect yourself from these?

EDIT: Sorry, would DataRow.item("Column").tostring be just as safe?

EDIT: Final Version:

 Public Function SafeData_Rowfeed(row As DataRow, ItemName As String) As String
    Try
        If Not (IsDBNull(row.Item(ItemName))) Then
            Return row.Item(ItemName).ToString
        ElseIf IsNothing(ItemName) Then
            Throw New IndexOutOfRangeException("Supplied Column " & ItemName & "Not found or is Nothing")
        Else
            Return ""
        End If
    Catch ex As System.ArgumentException
        Console.WriteLine(ex.Message)
        Return "Column Does Not Exist"
    End Try
End Function

You should use `DBItem IsNot Nothing` instead http://stackoverflow.com/questions/5791/vb-net-isnothing-versus-is-nothing — Slai, Jul 17 '16 at 12:04
If you don't need the information that the column is null, then you can use `SELECT COALESCE([nullableColumnName], ''), ...` in the query to convert such values to empty strings. — Andrew Morton, Jul 17 '16 at 13:00
`Textbox1.Text = .Item("SupplierDetails") & ""` will work with both `Nothing` and `DbNull.Value` ( `DbNull.Value.ToString` is `""` ) — Slai, Jul 18 '16 at 02:26

ChrisF · Answer 1 · 2016-07-18T08:08:17.747

0

would DataRow.item("Column").tostring

No it wouldn't as if DataRow.item("Column") is null then calling ToString will cause a null reference exception.

What you have is the safest way of doing this.

In C# 6 and VB 14 there's the ?. syntax, but that basically compiles to the same thing you have, though it will take up one less line of code:

stringValue = DataRow.item("Column")?.ToString

Though this will always set stringValue, so if you want it to retain its current value when the column is null your existing code is still the best way to do this.

edited Jul 18 '16 at 08:08

answered Jul 17 '16 at 11:52

ChrisF

134,786
31
255
325

For completeness: [Null-conditional Operators (C# and Visual Basic)](https://msdn.microsoft.com/en-us/library/dn986595.aspx). And it's also referred to as a [Null-Propagating Operator](https://msdn.microsoft.com/en-us/magazine/dn890368.aspx). (Yes, not trivial to find.) – Andrew Morton Jul 17 '16 at 12:36
Id never seen that. Thanks, when it came up i had a good search to and couldn't find anything. Thanks – Josh Jul 17 '16 at 22:46
What this actually does is `Dim value = DataRow.item("Column") : If value IsNot Nothing Then stringValue = value.ToString` so stringValue will not be changed if DataRow.item("Column") is Nothing – Slai Jul 18 '16 at 02:42
Handling the .value being nothing is the priority for me, the column names shouldn't change and if thats missing there is obviously bigger problems going on. The DBnull value is my priority. – Josh Jul 19 '16 at 22:28

score 0 · Answer 2 · answered Jul 17 '16 at 12:17

0

You could 'simplify' it a bit to

Public Function SafeDataSTR(DBItem As Object) As String
    Return If(IsDBNull(DBItem), "", If(DBItem.ToString, ""))
End Function

If(DBItem.ToString, "") checks if DBItem.ToString is Nothing.
https://msdn.microsoft.com/en-us/library/bb513985.aspx

Your use of it makes it not the safest method, because the item "SupplierDetails" might not exist.

answered Jul 17 '16 at 12:17

Slai

22,144
5
45
53

Yeah that was the 'Brief' version. The final one checks if the column exists as well and returns the error in the string (And Console.WriteLine). – Josh Jul 17 '16 at 22:47

Safest way to handle null database items in VB.net

2 Answers2