Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-09-08
4
Medium Priority
?
39 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 2000 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 2000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

704 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