VB.net How to pass Data Reader between class and Form

So here is my situation, i am trying to stick more and more reusable code into seperate class. In this instance i am calling a function which expects a dataReader to process the data. The actual call is in my sqlHelper class.

SqlHelper Code

    Public Shared Function getNenaRecords(ByVal myStatus As String) As SqlDataReader
        Dim myCon As SqlConnection = SqlConnectString()
        myCon.Open()

        Dim Myreader As SqlDataReader

        Dim cmd As SqlCommand = New SqlCommand("sp_E911Records_byStatus_SEL", myCon)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@status", myStatus)   'Status

        Myreader = cmd.ExecuteReader()

 
        myCon.Close()
        myCon.Dispose()

        Return Myreader


    End Function

Open in new window


And i call the function from my form via

 Dim myReader2 As SqlDataReader = getNenaRecords("v")

Open in new window


When i trace my code i see that myreader has Data but myReader2 is an empty reader after the code has been processed
so not sure what i am missing here
AlexPonnathAsked:
Who is Participating?
 
Snarf0001Connect With a Mentor Commented:
Eventually it will close when GC kicks in, but DB connections are one thing you definitely don't want to depend on that for.

.ExecuteReader also has an override for "CommandBehavior", if you pass in .CloseConnection, you can dispose of the datareader in your calling class, and that will terminate the connection.

Quick and "pretty safe" way to handle the issue without doing a larger code change.
0
 
Snarf0001Connect With a Mentor Commented:
A datareader is a live connection to the database, which streams the records as they come out.
When you close the underlying connection, the reader is also closed.

I would suggest either:
1) Constructing class objects and passing back with a  yield statement for the underlying records
2) Using a dataset or datatable instead of the datareader.
0
 
AlexPonnathAuthor Commented:
I figured that out, if i do not close my connection the data can be passed along. Since it is in a separate class, how does vb handle this connection ? Will it automatically close it after the code has been executed or will it hang around ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.