• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

Disposing of sql connection obeject between functions

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
CraigLazar
Asked:
CraigLazar
  • 3
  • 3
  • 2
  • +3
1 Solution
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
CraigLazarAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
CodeCruiserCommented:
You can declare the connection at class level so you dont have to pass it to B.
0
 
CraigLazarAuthor Commented:
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
 
Alpesh PatelAssistant ConsultantCommented:
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
 
Seven priceFull StackCommented:
 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
 
CodeCruiserCommented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
mastooCommented:
If you need another opinion, yes pass byval and create/dispose one time in A() outside of the loop that calls B
0
 
CraigLazarAuthor Commented:
thanks
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now