How deal with nulls in SQLDataReader?

[Woody Splawn]

I have some code that looks like this:

Dim SSN, LName, FName, M As String
mySqlConnection = New SqlConnection(myConnectionString)
Dim sql_Command As New SqlCommand( _
"Select SSN, LName, FName, M from Students WHERE (SSN = " + " '" +
ProposedValue + "')", _
mySqlConnection)
Try
mySqlConnection.Open()
Dim myReader As SqlDataReader =
sql_Command.ExecuteReader(CommandBehavior.CloseConnection)

While myReader.Read()
SSN = myReader.GetString(0) ' The first field in the answer
set
LName = myReader.GetString(1) 'The second field in the
answer set
FName = myReader.GetString(2) 'The third field in the answer
set
'M = myReader.GetString(3) 'The fourth field in the answer
set
End While
myReader.Close()
sql_Command.Connection.Close()

The problem is that the field caled M (middle initial) is sometimes null.
When it is the code above fails. How can I write the code so that if the
middle initial field is blank or null the code won't crash?

 

[Armin Zingler]

I have some code that looks like this:

Dim SSN, LName, FName, M As String
mySqlConnection = New SqlConnection(myConnectionString)
Dim sql_Command As New SqlCommand( _
"Select SSN, LName, FName, M from Students WHERE (SSN = " + "
'" +
ProposedValue + "')", _
mySqlConnection)
Try
mySqlConnection.Open()
Dim myReader As SqlDataReader =
sql_Command.ExecuteReader(CommandBehavior.CloseConnection)

While myReader.Read()
SSN = myReader.GetString(0) ' The first field in the
answer
set
LName = myReader.GetString(1) 'The second field in
the
answer set
FName = myReader.GetString(2) 'The third field in the
answer
set
'M = myReader.GetString(3) 'The fourth field in the
answer
set
End While
myReader.Close()
sql_Command.Connection.Close()

The problem is that the field caled M (middle initial) is sometimes
null. When it is the code above fails. How can I write the code so
that if the middle initial field is blank or null the code won't
crash?

Which value do you want to have in M when the database value is Null?
Nothing? A zero-length String? A certain string like "(Null)"?

 

[William Ryan]

M = IIf(IsDBNull(dr.GetString(3)), _
String.Empty, dr(3))

 

[Adam J. Schaff]

One neat trick is to create your own DataReader class that implements
IDataReader. Yeah, it's some typing to do it once, but when you're done you
can use the resulting class in all of you database access code and never
worry about nulls again.

Public Class SafeDataReader
Implements IDataReader

Private mDataReader As IDataReader

Public Sub New(ByVal DataReader As IDataReader)
mDataReader = DataReader
End Sub

....And a whole LOT of GetXXX methods go here...

End Class

In it, you can write Get methods like this:

Public Function GetString(ByVal i As Integer) As String Implements
IDataReader.GetString
If mDataReader.IsDBNull(i) Then
Return ""
Else
Return mDataReader.GetString(i)
End If
End Function

Even if you don't want to do that, the if statement in the above function
could easily be modified for your example:
If MyReader.IsDBNull(3) Then
M = ""
Else
M = MyReader.GetString(3)
End If

However, if you do a one-time fix like this then you won't be protected when
a null crops up in other fields like FName or LName at some later date.

BTW: I don't want to steal credit for this idea; I found it in "Visual Basic
..NET Business Objects" by Rockford Lhotka.

 

[Armin Zingler]

One neat trick is to create your own DataReader class that
implements IDataReader. Yeah, it's some typing to do it once, but
when you're done you can use the resulting class in all of you
database access code and never worry about nulls again.

Only a remark:
Disallowing Null values - if possible - in a database is the better
approach. When designing the database, one should wonder whether there is a
reason to distinguish between a Null value and e.g. a zero-length string.
Usually there isn't. If Null values are converted to zero-length strings
anyway, storing Null values does not make sense.