How to make sure the database connection is closed?

[Wei Lu]

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

 

[Steven Cheng]

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/469459/does-sqldataadapter-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:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#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/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------

 

[Mr. Arnold]

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.

 

[Steven Cheng]

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/tinguusingstatement.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 address removed).
-------------------

From: "Mr. Arnold" <MR. (e-mail address removed)>
References: <[email protected]>

 

[Mr. Arnold]

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/tinguusingstatement.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.

 

[Steven Cheng]

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 address removed).

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


--------------------

From: "Mr. Arnold" <MR. (e-mail address removed)>
In-Reply-To: <[email protected]>
Subject: Re: How to make sure the database connection is closed?
Date: Mon, 16 Feb 2009 07:32:14 -0500 t




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