Solved

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

Posted on 2016-09-08
4
17 Views
Last Modified: 2016-10-28
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
0
Comment
Question by:AlexPonnath
  • 2
4 Comments
 
LVL 22

Assisted Solution

by:Snarf0001
Snarf0001 earned 500 total points
ID: 41790292
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
 

Author Comment

by:AlexPonnath
ID: 41790323
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
 
LVL 22

Accepted Solution

by:
Snarf0001 earned 500 total points
ID: 41790645
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question