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.
I have a very basic scenario: