Robert Robert - 20 days ago 6
Vb.net Question

Is there a neat and clean way to handle nulls with Yields?

While CommitReader.Read()
Yield New Commit() With {
.FirstValue = CommitReader.GetInt32(CommitReader.GetOrdinal("FirstValue")),
.SecondValue = CommitReader.GetString(CommitReader.GetOrdinal("SecondValue")).Trim(),
'Lots of values
End While


I know I can do something like this; however there are 24 properties and I would like to make this part as clean as possible

While CommitReader.Read()
new Commit (){
Dim index As Integer = reader.GetOrdinal("FirstValue")
If reader.IsDBNull(index) Then
FirstValue = String.Empty
Else
FirstValue = reader(index)
End If
index = reader.GetOrdinal("SecondValue")
If reader.IsDBNull(index) Then
SecondValue = String.Empty
Else
SecondValue = reader(index)
End If
}
End While


Is there a better way to handle this type of thing? I am mainly a C# developer so if the syntax is off a little sorry, I am winging it in VB.

Answer

It's a shame that SqlDataReader doesn't have the generic Field extension method like DataRow does, but you could define your own extension method (has to be in a module in VB.NET) to help with the null checks, perhaps something like this:

<Extension>
Function GetValue(Of T)(rdr As SqlDataReader, i As Integer) As T
    If rdr.IsDBNull(i) Then
        Return Nothing
    End If
    Return DirectCast(rdr.GetValue(i), T)
End Function

And use it something like this:

While CommitReader.Read()
    Yield New Commit() With {
        .FirstValue = CommitReader.GetValue(Of Integer?)(CommitReader.GetOrdinal("FirstValue")),
        .SecondValue = CommitReader.GetValue(Of String)(CommitReader.GetOrdinal("SecondValue")),
        'Lots of values
End While

I haven't tested this fully to make sure it handles all data types appropriately (may be worth looking at DataRowExtensions.Field to see how it does it).

Note that you are using String.Empty as the "null" value for strings, while this will use Nothing/null (I also had to remove the .Trim call to avoid NREs). If you want empty string instead, you could use (adding the Trim back in):

.SecondValue = If(CommitReader.GetValue(Of String)(CommitReader.GetOrdinal("SecondValue")), String.Empty).Trim()

You may also want to move the GetOrdinal calls out of the loop to improve performance.