Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Closing SQL Connections

Reply
Thread Tools

Closing SQL Connections

 
 
bannaman
Guest
Posts: n/a
 
      03-21-2006
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.c urrentSite).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.

 
Reply With Quote
 
 
 
 
Marina Levit [MVP]
Guest
Posts: n/a
 
      03-21-2006
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
"bannaman" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) oups.com...
> 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.c urrentSite).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.
>



 
Reply With Quote
 
 
 
 
Bruce Barker
Guest
Posts: n/a
 
      03-21-2006
if you call close you do not need to call dispose.

-- bruce (sqlwork.com)


"bannaman" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) oups.com...
> 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.c urrentSite).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.
>



 
Reply With Quote
 
bannaman
Guest
Posts: n/a
 
      03-21-2006
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?

 
Reply With Quote
 
Kevin Spencer
Guest
Posts: n/a
 
      03-21-2006
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.

"bannaman" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) ups.com...
> 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?
>



 
Reply With Quote
 
tdavisjr
Guest
Posts: n/a
 
      03-21-2006
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.

 
Reply With Quote
 
Juan T. Llibre
Guest
Posts: n/a
 
      03-21-2006
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/Dispose...ndObjects.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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
> "bannaman" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed) ups.com...
>> 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?
>>

>
>



 
Reply With Quote
 
Kevin Spencer
Guest
Posts: n/a
 
      03-21-2006
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.

"Juan T. Llibre" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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/Dispose...ndObjects.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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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.
>>
>> "bannaman" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed) ups.com...
>>> 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?
>>>

>>
>>

>
>



 
Reply With Quote
 
Bruce Barker
Guest
Posts: n/a
 
      03-21-2006
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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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/Dispose...ndObjects.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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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.
>>
>> "bannaman" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed) ups.com...
>>> 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?
>>>

>>
>>

>
>



 
Reply With Quote
 
Juan T. Llibre
Guest
Posts: n/a
 
      03-21-2006
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/
===================================
"Kevin Spencer" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
> "Juan T. Llibre" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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/Dispose...ndObjects.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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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.
>>>
>>> "bannaman" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed) ups.com...
>>>> 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?
>>>>
>>>
>>>

>>
>>

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Closing popup window when closing parent window? =?Utf-8?B?Vk1J?= ASP .Net 3 02-15-2007 08:29 AM
Closing the doors 15 minutes before closing. doofus Computer Support 12 06-11-2005 08:20 AM
Re: Opening / Closing SQL Server connections Michael D. Long ASP .Net 6 09-23-2004 11:46 PM
Re: Opening / Closing SQL Server connections Michael D. Long ASP .Net 0 09-22-2004 12:41 AM
Re: Opening / Closing SQL Server connections Patrice ASP .Net 3 09-21-2004 11:58 PM



Advertisments