Handling DBNull data

Question

I am assigning one datarow object the values of another datarow object. For example:

newAddress.AddrId     = frenchAddress.AddrId
newAddress.AddrLn1    = frenchAddress.AddrLn1
newAddress.AddrLn2    = frenchAddress.AddrLn2
newAddress.AddrLn3    = frenchAddress.AddrLn3
newAddress.AddrCityNm = frenchAddress.AddrCityNm
newAddress.CntryId    = frenchAddress.CntryId
newAddress.ProvId     = frenchAddress.ProvId

Some of the values in frenchAddress can be NULL so I end up getting an exception because I am assigning newAddress the value of DBNull. What is the best and cleanest way of handling DBNull? Is there anyway to just insert NULL where there is a DBNull?

Using VB.Net.

Answer 1

Since that is a typed DataRow(so an auto-generated type with named properties) there are already methods for this purpose:

If frenchAddress.IsAddrCityNmNull Then
    newAddress.SetAddrCityNmNull()
Else
    newAddress.AddrCityNm = frenchAddress.AddrCityNm
End If 

If it was not a typed but a standard DataRow you could use the SetField method that supports nullable types and also should handle this case:

newAddress.SetField("ColumnName", frenchAddress.Field(Of String)("ColumnName"))

Answer 2

You can use the IsDbNull function with the If statement for example:

newAddress.AddrLn1 = If (IsDbNull(frenchAddress.AddrLn1), String.Empty, frenchAddress.AddrLine1)

Answer 3

This ended up being the solution I used, a little bit of a mixture between the two answer above. It seems to work ok:

newAddress.AddrLn1 = If(frenchAddress.IsAddrLn1Null = True, Nothing, frenchAddress.AddrLn1)