Solved

Disposing of sql connection obeject between functions

Posted on 2010-11-24
11
392 Views
Last Modified: 2012-05-10
Hi,
I have t functions say A() and B(). Currently in each function i create a sql db connection object in each function. Function A() calls function B(). I want to pass the db connection object from A() to B() as a paramter so i can get rid of creating the db connection object in B().
My question is this, do i still need to dipose of the passed db object in B() by using

DBConnection.Dispose()
DBConnection = Nothing

Or can i dispose of it in function A() ?
I am concerned if i close it in B() then it will no longer be valid in A(), this application is multi trheaded and i have to be ver carefull and make sure i dispose of the objects when each thread is done.
hope this makes sense

thanks
0
Comment
Question by:CraigLazar
[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
  • 3
  • 3
  • 2
  • +3
11 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 34205364
If you create it in A() and pass a reference to it to B(), then you should dispose of it in A() when B() has finished with it. Partly so that it is still valid if A() needs to use it, but also because it is good practice to dispose an object in the same method/object that created it.
0
 
LVL 4

Author Comment

by:CraigLazar
ID: 34205439
Hi, ok this is how i am declaring it in B()
B(ByVal DBConn As SqlClient.SqlConnection)
so can i still dispose of the passed object in B(), and it will not effect the connection object in A()
The reason is that i have a loopoing process where inside the loop i call B(), so instead of creating another object each time B() is called, i want to just pass it the existing open conneciton object. once the object is passed does it then created as a seperate object in memory that must be disposed of in B().

thanks
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 250 total points
ID: 34205482
You should be passing ByRef rather than ByVal, wo that you are only working with a single instance of the object. You don't need to dispose of it in B() just in A() when you are done with it.

Sequence:
Create and open Connection in A()
Pass reference to B()
B() does some operations using the connection
A() closes/disposes of the connection

Open in new window

0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34205546
You can declare the connection at class level so you dont have to pass it to B.
0
 
LVL 4

Author Comment

by:CraigLazar
ID: 34205548
hi, if i use Byval does it create a new object from the passed object in function B()? if so then i would then need to close it in B()?

thanks
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 34205602
If you have passed parameter as reference then no need to dispose it but if passed as byval then need to dispose.

In .net Byval is by default.
0
 
LVL 9

Expert Comment

by:Seven price
ID: 34205613
 If (Not conn Is Nothing) Then
            If conn.State <> ConnectionState.Closed Then conn.Close()
            conn.Dispose()
        End If
        If Not cmd Is Nothing Then cmd.Dispose()
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34205646
ByVal only works for primitive types. For object types, it passes a pointer to the object rather than the object. So the object is still being shared rather than duplicated.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 34205671
You are still only dealing with a single object. If A() creates and open the connection the only A() should be closing/disposing of it.
0
 
LVL 21

Expert Comment

by:mastoo
ID: 34206142
If you need another opinion, yes pass byval and create/dispose one time in A() outside of the loop that calls B
0
 
LVL 4

Author Closing Comment

by:CraigLazar
ID: 34281922
thanks
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
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…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

635 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