Handle DBNull in an object initializer

Question

In my asp.net web service, I have an object class which get data from database, but I counter the following problem when some data is null in database:

(1) If I don't handle the NULL value in database and use the code as below:

<WebMethod> _
Public Function GetCustomerDetail(ByVal sqlQuery As String) As List(Of customerInfo)
    Dim detaillist = New List(Of customerInfo)()
    Dim detail As customerInfo

    Dim da = New SqlDataAdapter(sqlQuery, conn)
    Dim dt = New DataTable()
    da.Fill(dt)

    For Each dr As DataRow In dt.Rows
        detail = New customerInfo() With { _
         .CustomerID = dr("CUSTOMER_ID"), _
         .CustomerName = dr("CUSTOMER_NAME"), _
         .RegisterDate = dr("REGISTER_DATE"), _
         .Address = dr("ADDRESS") _
        }
        detaillist.Add(detail)
    Next
    Return detaillist

End Function

Public Class customerInfo
    Public CustomerID As String = String.Empty
    Public CustomerName As String = String.Empty
    Public RegisterDate As String = Date.Now.ToString("dd/MM/yyyy")
    Public Address As String = String.Empty
End Class

I got the error:

System.InvalidCastException: Conversion from type 'DBNull' to type 'String' is not valid.

(2) if I handle the NULL in database as below:

<WebMethod> _
Public Function GetCustomerDetail(ByVal sqlQuery As String) As List(Of customerInfo)
    Dim detaillist = New List(Of customerInfo)()
    Dim detail As customerInfo

    Dim da = New SqlDataAdapter(sqlQuery, conn)
    Dim dt = New DataTable()
    da.Fill(dt)

    For Each dr As DataRow In dt.Rows
        detail = New customerInfo() With { _
         .CustomerID = dr("CUSTOMER_ID"), _
         .CustomerName = dr("CUSTOMER_NAME"), _
         .RegisterDate = dr("REGISTER_DATE"), _
         If dr("ADDRESS") = System.DBNull.Value Then
            .Address = ""
         Else
            .Address = dr("ADDRESS") _
         End if
        }
        detaillist.Add(detail)
    Next
    Return detaillist

End Function

Public Class customerInfo
    Public CustomerID As String = String.Empty
    Public CustomerName As String = String.Empty
    Public RegisterDate As String = Date.Now.ToString("dd/MM/yyyy")
    Public Address As String = String.Empty
End Class

I got the error:

Compiler Error Message: BC30985: Name of field or property being initialized in an object initializer must start with '.'.

I want to know how to handle the DBNull value for string and date in an object initializer.

Answer 1

You can use Convert.ToString

<WebMethod> _
Public Function GetCustomerDetail(ByVal sqlQuery As String) As List(Of customerInfo)
    Dim detaillist = New List(Of customerInfo)()
    Dim detail As customerInfo

    Dim da = New SqlDataAdapter(sqlQuery, conn)
    Dim dt = New DataTable()
    da.Fill(dt)

    For Each dr As DataRow In dt.Rows
    Dim registerDate As Date
    If Date.TryParse(Convert.ToString(dr("REGISTER_DATE")), registerDate ) = False Then 
    'Do what you need to do if the cell is not a valid date time value
    End If
        detail = New customerInfo() With { _
         .CustomerID = Convert.ToString(dr("CUSTOMER_ID")), _
         .CustomerName = Convert.ToString(dr("CUSTOMER_NAME")), _
         .RegisterDate = registerDate.ToString("dd/MM/yyyy"), _
         .Address = Convert.ToString(dr("ADDRESS"))
        }
        detaillist.Add(detail)
    Next
    Return detaillist

End Function

Edited based on OP's comment below.