Solved

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

Posted on 2016-09-08
4
23 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 23

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 23

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Web page design problem 3 42
Removing Version of .NET in Windows 10 17 134
SSIS Column mapping 5 42
AD Computer Objects in VB .Net 2 21
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
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…

752 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