Closing SQL Connections

[bannaman]

Please can someone help.

I have a routine as follows

Public Function dbConnection() As SqlConnection

Dim dbConn As New SqlConnection
Dim errorH As New errorHandle

Try
Try
dbConn.ConnectionString =
ConfigurationManager.ConnectionStrings(globalapp.currentSite).ConnectionString.ToString
Catch ex As Exception
errorH.move(ex)
End Try
If dbConn.State = ConnectionState.Closed Then
dbConn.Open()
End If
Catch ex As SqlException
errorH.move(ex)
End Try

Return dbConn


End Function

This creates my sqlconnection. I call this from other functions in my
class in this format

Try
dim sqlconn as sqlconnection = dbconnection()

Catch ex as exception

Finally
If db.State = ConnectionState.Open Then
db.Close()
dbConnection.Dispose()
End If
End try

I recently only added the dbconnection.dispose because i read that i
should be doing that not sure if i should. But i'm getting the usual
error message of the maximum timeout has been reached or the maximum
pool size has been reached. I don't know what to do. Can anyone advise.

 

[Marina Levit [MVP]]

I don't know what you are closing, but it is not the connection your declare
in your Try block.

You need to declare the variable outside the Try block, but get the
connection in it. Something like:

Dim sqlconn as SqlConnection
Try
sqlconn = dbconnection()
Catch

Finally
If Not IsNothing(sqlconn) Then
sqlconn.Close()
End If
End Try

 

[Bruce Barker]

if you call close you do not need to call dispose.

-- bruce (sqlwork.com)

 

[bannaman]

Sorry the dim sqlconn as sqlconnection = dbconnection() was a typo it
should have been dim db as sqlconnection = dbconnection().

As i am creating a connection in another function and passing it back
to my routine does it still close the conneciton that was opened when
calling dbconnection. do i not need to do dbconnection.close or will
db.close be enough?

 

[Kevin Spencer]

As Bruce said, close it. Don't dispose it.

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

Show me your certification without works,
and I'll show my certification
*by* my works.

 

[tdavisjr]

First, I would dim your sqlconn outside the Try block like a poster
suggested.

Second, when you say db = dbconnection() you are not actually creating
a new connection object you are just getting a reference to the
conneciton object that was created inside dbconnection(). So, when you
say, db.close() you are actually closing the original connection that
you opened and there is no need to do dbconneciton.close().

Lastly, step through your code in debug mode to see if the connection
is closing.

 

[Juan T. Llibre]

re:

As Bruce said, close it. Don't dispose it.

Interesting point for discussion, Kevin.

There's a question as to the *unmanaged* resources which need freeing,
over and above the need for freeing the *managed* .net resources.

See Rocky Lhotka's article :

http://www.lhotka.net/WeBlog/DisposeYourCommandObjects.aspx

"However, it also turns out that some Command objects really do have non-managed
resources that need to be disposed. Some don't. How do you know which do and
which don't? You need to ask the dev that wrote the code.

It turns out that SqlCommand has no un-managed resources, which is why most of us
have gotten away with this so far. However, OleDbCommand and OdbcCommand
do have un-managed resources and must be disposed to be safe."


Comments ?

btw, I just finished a fix for a client's application, developed by someone else,
and for which I was brought in as trouble-shooter, which was having resource problems.

It had 117 undisposed of OleDbCommand objects.

I disposed all of them...and the problem vanished.





Juan T. Llibre, asp.net MVP
aspnetfaq.com : http://www.aspnetfaq.com/
asp.net faq : http://asp.net.do/faq/
foros de asp.net, en español : http://asp.net.do/foros/
===================================

 

[Kevin Spencer]

I'll take your word for it, Juan. I haven't done any OleDb stuff in a couple
of years. I would not be surprised if you were correct!

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

Show me your certification without works,
and I'll show my certification
*by* my works.

 

[Bruce Barker]

oledbcommand doesn't have a close so you must call its dispose to release
unmanged resources.

with SqlConnection, it has a Close method, which you can call instead of
dispose (all dispose does is call Close), which can make the code more
readable. also you can call open again, while it not recommened to reinit
disposed objects.

if you are using c# (or vb.net 2.0), you probably shoudl switch to using
(which will call dispose even on an error)


using (SqlConnection conn = new SqlConnection())
{
// my sql code here
}


-- bruce (sqlwork.com)

 

[Juan T. Llibre]

heh, heh...

I wouldn't be surprised if Rocky was correct!

I practically worship the ground he walks on.

;-)




Juan T. Llibre, asp.net MVP
aspnetfaq.com : http://www.aspnetfaq.com/
asp.net faq : http://asp.net.do/faq/
foros de asp.net, en español : http://asp.net.do/foros/
===================================

 

[Juan T. Llibre]

re:

all dispose does is call Close

Yup...so calling either will work for SqlConnection objects.




Juan T. Llibre, asp.net MVP
aspnetfaq.com : http://www.aspnetfaq.com/
asp.net faq : http://asp.net.do/faq/
foros de asp.net, en español : http://asp.net.do/foros/
===================================