This post related to the previous one, but I decided to write a separate article because it seems to be a common problem.

Sometimes when you use SqlDataReader, you would get an exception:

NullReferenceException {“Object reference not set to an instance of an object.”}
at System.Data.SqlClient.SqlDataReader.ReadColumnHeader(Int32 i)
at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.GetInt32(Int32 i)

And the maddening thing – it doesn’t happen often, just every once in a while. And it happens at different times too, sometimes reader would read 100 records, sometimes 200 etc.

One possible case – SqlDataReader is losing its connection. And one possible reason for that – connection goes out of scope.

Consider following scenario – you have a function that returns SqlDataReader:

Function GetTheReader() as SqlDataReader
   Dim oConn As New SqlConnection("Connection String") : oConn.Open()
   Dim oComm As New SqlCommand("Stored Procedure", oConn)

   Dim oReader As SqlDataReader = oComm.ExecuteReader(CommandBehavior.CloseConnection)

   Return oReader
End Function

And you use it like this:

Dim oReader as SqlDataReader = GetTheReader()
'Begin use reader - loop, read data etc.

The problem with this approach that connection used to create the reader is stored in a private variable inside of `GetTheReader` function and when the function exits – the variable goes out of scope. Eventually, sooner or later Garbage Collector will collect it and close and dispose of connection – and at this time your SqlDataReader will fail.

The solution? Either use SqlDataReader at the same scope level you created it, or, if you do need to use function – pass connection object into it as one of the parameters, so it would remain valid after function exits.