Solved

ADO.Net Connection Pooling

Posted on 2004-08-25
6
613 Views
Last Modified: 2008-02-01
Hi Guys,

I am currently working on ASP.Net web app with Oracle 9i database,the problem i am facing is databsae connection's doesn't seem to be releasing to the pool
even after calling connection.Dispose ;  the connections are not returned to the pool and
appear as if they are busy doing some processing.

 I am using microsoft's ADO.Net provider for Oracle.

Connection string is as follows:

"user id=TESTUSER;data source='DBS';password=TESTPWD;Pooling=True;Connection Lifetime=50;Min Pool Size = 20;Max Pool Size=50"

I am using OracleHelper , Application Block from Microsoft for all Data Access.

The error i am getting is as follows :

"Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached."

The problem seems to dissapear when i have a very low value for Connection Lifetime
inside the connection string say 20 seconds... ,  which will defeat the purpose of connection pooling

The behavior i am expecting is the connections should return to the pool after
calling Dispose on them ..,so the next request coming in can grab a connection straight away from the pool..., Isn't it the way this should be working ...?
0
Comment
Question by:ganeshpai
  • 3
  • 3
6 Comments
 
LVL 8

Expert Comment

by:shovavnik
ID: 11896934
1. Make sure you call connection.Close()

2. You don't need to dispose your connections (but you can if you want to).  .net's garbage collector takes care of that for you.  As long as the connection is closed (see #1), the connections are released to the pool.

3. How many concurrent users are there?  Maybe you really have more than 50 concurrent database connections open?

4. You're not storing the connection objects in the session, or anything like that, are you?

0
 

Author Comment

by:ganeshpai
ID: 11897158

I am not storing the connection is session object , no way...

currently  3   concurrent users..

Currently i am suspecting the problem is with Using block  ,  does it really  work ??

0
 

Author Comment

by:ganeshpai
ID: 11897193
The following is the code in the OracleHelper Application block from Microsoft..

My  suspect is  the using statement is not working..

{ Using is suppose to call dispose according to Microsoft..}

            public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
            {
//create & open an OracleConnection, and dispose of it after we are done.

using (OracleConnection cn = new OracleConnection(connectionString))
{
      cn.Open();
 //call the overload that takes a connection in place of the connection string
return ExecuteDataset(cn, commandType, commandText, commandParameters);
}
            }
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 8

Accepted Solution

by:
shovavnik earned 400 total points
ID: 11897265
Using does call Dispose().  I'm not sure Dispose() calls Close().  I think you're supposed to do that manually.

Try this, if you can.  This is how I manage my connections:

using (OracleConnection cn = new OracleConnection(connectionString))
{
  try
  {
     cn.Open();
 //call the overload that takes a connection in place of the connection string
return ExecuteDataset(cn, commandType, commandText, commandParameters);
  }
  finally
  {
    cn.Close(); // it always needs to be closed after the operation, even if an exception occurrs.
  }
}
0
 

Author Comment

by:ganeshpai
ID: 11901758
shovavnik,

Thanks for your suggestions.

I found that the actual problem is the "usual" statement within the OracleHelper.
Looks like its not doing its job ,as it should or may be it's non-deterministic ...

0
 
LVL 8

Expert Comment

by:shovavnik
ID: 11902633
No problem.  I'm glad it worked out.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Regex validation 2 28
How to close an asp.net web form using C# ? 4 44
RLDC Reporting in Visual studio 11 28
Linq asp.net mvc 13 17
Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question