Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > How to make sure the database connection is closed?

Reply
Thread Tools

How to make sure the database connection is closed?

 
 
Wei Lu
Guest
Posts: n/a
 
      02-15-2009
Hi all,

Our web project may hang sometime. After checking the memory dump of the
process, we found that the process is hang on the class which try to get a
connection from the database.

The code sniper is like this:

public static System.Data.DataSet getDataSet(string sSQL,string
sConnection)
{
System.Data.DataSet _ds = new System.Data.DataSet();
AseDataAdapter _da = new AseDataAdapter(sSQL.Trim(), sConnection);
try
{
_da.Fill(_ds);
return _ds;
}
catch(AseException)
{
return null;
}
finally
{
_ds.Dispose();
_da.Dispose();
}
};
So my question is, how could I may sure that my connection is close so that
other thread will not hang for asking to the get the connection?

Wei Lu

 
Reply With Quote
 
 
 
 
Steven Cheng
Guest
Posts: n/a
 
      02-15-2009
Hi Wei,

As for ADO.NET connection, we suggest that you use using block(c#) or
try..finally block to ensure the connection closing. For example:

====================
using (SqlConnection conn = new SqlConnection(""))
{
//do data access
}

or

try
{
SqlConnection conn = new SqlConnection("")
}
finally
{
conn.close();
}
==============

Also, for your code here, you're using DataAdapter to perform query, it is
noticable that DataAdapter.Dispose does not gurantee the closing of the
underlying connection. Therefore, it is better to explicitly close the
connection. for example:

====================
using (SqlConnection conn = new SqlConnection(""))
{
SqlDataAdapter = new SqlDataAdapter() {Connection = conn};

//......
}
===================

here is a thread discussing on this:

http://stackoverflow.com/questions/4...dispose-actual
ly-close-an-associated-sqlconnection

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
http://www.velocityreviews.com/forums/(E-Mail Removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subs...#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subs.../aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
>From: "Wei Lu" <(E-Mail Removed)>
>Subject: How to make sure the database connection is closed?
>Date: Sun, 15 Feb 2009 16:24:10 +0800


>
>Hi all,
>
>Our web project may hang sometime. After checking the memory dump of the
>process, we found that the process is hang on the class which try to get a
>connection from the database.
>
>The code sniper is like this:
>
> public static System.Data.DataSet getDataSet(string sSQL,string
>sConnection)
> {
> System.Data.DataSet _ds = new System.Data.DataSet();
> AseDataAdapter _da = new AseDataAdapter(sSQL.Trim(), sConnection);
> try
> {
> _da.Fill(_ds);
> return _ds;
> }
> catch(AseException)
> {
> return null;
> }
> finally
> {
> _ds.Dispose();
> _da.Dispose();
> }
> };
>So my question is, how could I may sure that my connection is close so

that
>other thread will not hang for asking to the get the connection?
>
>Wei Lu
>
>


 
Reply With Quote
 
 
 
 
Mr. Arnold
Guest
Posts: n/a
 
      02-15-2009

""Steven Cheng"" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Wei,
>
> As for ADO.NET connection, we suggest that you use using block(c#) or
> try..finally block to ensure the connection closing. For example:
>
> ====================
> using (SqlConnection conn = new SqlConnection(""))
> {
> //do data access
> }
>


What happens here if the solution blows? The *using* is not going to close
the connection if within the *using* the connection is open and some
statement causes an abort. The connection is left open. I don't care what
some book or article says about a *using* closing the connection. If it
aborts within the *using*, the connection may be left opened. I have been
there and done that.

 
Reply With Quote
 
Steven Cheng
Guest
Posts: n/a
 
      02-16-2009
Hi Arnold,

I'm not quite sure about the "solution blows" case. The "using (resource)
{...}" approach is the recommended means which guarantee resource
cleanup(as long as the IDisposable interface is correctly implemented on
that type). Also, "using(resource){...}" approach is the same as
"try{...}finally{...}" and here is a web article describe this in detail:

#Understanding the 'using' statement in C#
http://www.codeproject.com/KB/cs/tin...statement.aspx

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(E-Mail Removed).
-------------------
>From: "Mr. Arnold" <MR. (E-Mail Removed)>
>References: <(E-Mail Removed)>

<(E-Mail Removed)>
>In-Reply-To: <(E-Mail Removed)>
>Subject: Re: How to make sure the database connection is closed?
>Date: Sun, 15 Feb 2009 08:15:14 -0500


>
>""Steven Cheng"" <(E-Mail Removed)> wrote in message
>news:(E-Mail Removed)...
>> Hi Wei,
>>
>> As for ADO.NET connection, we suggest that you use using block(c#) or
>> try..finally block to ensure the connection closing. For example:
>>
>> ====================
>> using (SqlConnection conn = new SqlConnection(""))
>> {
>> //do data access
>> }
>>

>
>What happens here if the solution blows? The *using* is not going to close
>the connection if within the *using* the connection is open and some
>statement causes an abort. The connection is left open. I don't care what
>some book or article says about a *using* closing the connection. If it
>aborts within the *using*, the connection may be left opened. I have been
>there and done that.
>
>


 
Reply With Quote
 
Mr. Arnold
Guest
Posts: n/a
 
      02-16-2009

""Steven Cheng"" <(E-Mail Removed)> wrote in message
news(E-Mail Removed)...
> Hi Arnold,
>
> I'm not quite sure about the "solution blows" case. The "using (resource)
> {...}" approach is the recommended means which guarantee resource
> cleanup(as long as the IDisposable interface is correctly implemented on
> that type). Also, "using(resource){...}" approach is the same as
> "try{...}finally{...}" and here is a web article describe this in detail:
>
> #Understanding the 'using' statement in C#
> http://www.codeproject.com/KB/cs/tin...statement.aspx
>


I respectfully have to tell you that is not the case with the using
statement, as I was using a console application on a timed thread that kept
executing a using statement for an Oracle connection to the database. The
solution was blowing-up each time the thread awoke and executed the method
leaving numerous connections open until it ran out of connections to use. I
kept using the *using* statement, but on the try/catch I made sure that the
connection was closed on the catch.

Also, the using statement is not recommended for using on a WCF client
connection, as it can lead to trouble.

The preferred method is to instantiate the WCF client without a using
statement and close the client manually.



 
Reply With Quote
 
Steven Cheng
Guest
Posts: n/a
 
      02-18-2009
Thank you for your continue followup Arnold,

If that is the case, I think it does be a quite serious issue. Is it
possible to repro such scenario via some simplified code(such as a console
client that connect database and throw some error in thread)? if so, I'll
be glad to perform some research on this.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(E-Mail Removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subs...#notifications.


--------------------
>From: "Mr. Arnold" <MR. (E-Mail Removed)>
>In-Reply-To: <(E-Mail Removed)>
>Subject: Re: How to make sure the database connection is closed?
>Date: Mon, 16 Feb 2009 07:32:14 -0500

t
>
>
>""Steven Cheng"" <(E-Mail Removed)> wrote in message
>news(E-Mail Removed)...
>> Hi Arnold,
>>
>> I'm not quite sure about the "solution blows" case. The "using (resource)
>> {...}" approach is the recommended means which guarantee resource
>> cleanup(as long as the IDisposable interface is correctly implemented on
>> that type). Also, "using(resource){...}" approach is the same as
>> "try{...}finally{...}" and here is a web article describe this in detail:
>>
>> #Understanding the 'using' statement in C#
>> http://www.codeproject.com/KB/cs/tin...statement.aspx
>>

>
>I respectfully have to tell you that is not the case with the using
>statement, as I was using a console application on a timed thread that

kept
>executing a using statement for an Oracle connection to the database. The
>solution was blowing-up each time the thread awoke and executed the method
>leaving numerous connections open until it ran out of connections to use.

I
>kept using the *using* statement, but on the try/catch I made sure that

the
>connection was closed on the catch.
>
>Also, the using statement is not recommended for using on a WCF client
>connection, as it can lead to trouble.
>
>The preferred method is to instantiate the WCF client without a using
>statement and close the client manually.
>
>
>
>


 
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
Re: How include a large array? Edward A. Falk C Programming 1 04-04-2013 08:07 PM
Database Database Database Database scott93727@gmail.com Computer Information 0 09-27-2012 02:43 AM
DataBase DataBase DataBase DataBase scott93727@gmail.com Computer Information 0 09-26-2012 09:40 AM
sure shot requirement for C++ with Unix Experience !!! sure interviewto day !!! Himayat66@gmail.com ASP .Net 0 02-07-2008 12:40 AM
"Make sure disk is not full or write-protected" error???? Ric Wireless Networking 4 09-16-2005 10:42 AM



Advertisments