debug suggestion for connection pool problem with Oracle using daa

Hi

We have a asp.net web application with oracle 9.1.
Data access layer we use is DAAB 2.0.
We use connection pool and set max pool size = 150.
But we always get the following error almost every month ( we have to
restart web server to make it work):
Timeout expired, The timeout period elapsed prior to obtaining a connection
from the pool. This may have occured becasue all pooled connections where in
use and max pool size was reached.

As many perople said, after I went though whole implementation, I did not
find any connections we did not close. Anyone have good advice for how to
debug such issue? or recommend some tool?
I have seen lots of similar questions and topics, and found many answers are
extremely confused.
Can anyone make clear for my some questions?
1. We use USING to dispose connection and OracleDataReader in most cases, but
also there are some exceptions, we use close() to close connection in Catch
block of try-catch. here is example:
==============================================
OracleConnection cn = new OracleConnection(connectionString);
cn.Open();

try
{
return ExecuteReader(cn, null, commandType, commandText, commandParameters,
OracleConnectionOwnership.Internal);
}
catch
{
cn.Close();
throw;
}
==========================================

Is anything wrong for these pieces of code?

2. In many cases, we opened connection in one function, and call another to
process the real operation of DB., example:
===================================================
using (OracleConnection cn = new OracleConnection(connectionString))
{
cn.Open();
return ExecuteNonQuery(cn, commandType, commandText, commandParameters);
}
===================================================
if ExecuteNonQuery() got exception, the USING still can dispose connection?

3. ===================================================
using(OracleDataReader r =
OracleHelper.ExecuteReader(ConnectionManager.GetConnStr(), CommandType.Text,
sql.SQLString, pa))
{
ArrayList al = QueryResultsParser.Parse(r, request);
r.Close(); //Force the OracleDataReader to be closed
return al;

}
====================================================
(we have add
For these pieces of code, we use datareader to get data, I wonder:
1. is it necessary we explicitely close the datareader?
2. if answer yes for 1., and if OracleHelper.ExecuteReader() falied, does it
mean
we cannot close connection?


4. for some objects, do we need to displose them as soon as it is not used?
example:
=================================================
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connection, (OracleTransaction)null, commandType,
commandText, commandParameters);

//create the DataAdapter & DataSet
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();

//fill the DataSet using default values for DataTable names, etc.
da.Fill(ds);

cmd.Dispose();
da.Dispose();
=======================================

Frans
Sun Jun 26 05:36:00 CDT 2005

donotfill wrote:
> We have a asp.net web application with oracle 9.1.
> Data access layer we use is DAAB 2.0.
> We use connection pool and set max pool size = 150.

It's important to know which Oracle provider you're using. As you
don't mention any, I pressume you're using Microsoft's client.

There were some bugs in this client, and most of them (if not all) are
fixed with .NET 1.1 SP1. Be sure to install that service pack of .NET
on your webservers.

> But we always get the following error almost every month ( we have to
> restart web server to make it work):
> Timeout expired, The timeout period elapsed prior to obtaining a
> connection from the pool. This may have occured becasue all pooled
> connections where in use and max pool size was reached.
>
> As many perople said, after I went though whole implementation, I did
> not find any connections we did not close. Anyone have good advice
> for how to debug such issue? or recommend some tool?
> I have seen lots of similar questions and topics, and found many
> answers are extremely confused.
> Can anyone make clear for my some questions?
> 1. We use USING to dispose connection and OracleDataReader in most
> cases, but also there are some exceptions, we use close() to close
> connection in Catch block of try-catch. here is example:
> ==============================================
> OracleConnection cn = new OracleConnection(connectionString);
> cn.Open();
>
> try
> {
> return ExecuteReader(cn, null, commandType, commandText,
> commandParameters, OracleConnectionOwnership.Internal);
> }
> catch
> {
> cn.Close();
> throw;
> }
> ==========================================
>
> Is anything wrong for these pieces of code?

No, seems ok, well 'ok' as in: it should work. Though I wouldn't pass
datareaders around if I were you. Because: in the snippet above, you
open a connection and return a reader. That means that when the reader
is returned, the connection is still open. What happens in the caller
of this routine which uses the reader, in the case of an exception? You
then also close the connection?

> 2. In many cases, we opened connection in one function, and call
> another to process the real operation of DB., example:
> ===================================================
> using (OracleConnection cn = new OracleConnection(connectionString))
> {
> cn.Open();
> return ExecuteNonQuery(cn, commandType, commandText,
> commandParameters); }
> ===================================================
> if ExecuteNonQuery() got exception, the USING still can dispose
> connection?

Yes. In KB 830173, a bug is described in the Oracle provider from MS
which shows it doesn't do that always, though this bug was fixed in
.NET 1.1 SP1.

> 3. ===================================================
> using(OracleDataReader r =
> OracleHelper.ExecuteReader(ConnectionManager.GetConnStr(),
> CommandType.Text, sql.SQLString, pa))
> {
> ArrayList al = QueryResultsParser.Parse(r, request);
> r.Close(); //Force the OracleDataReader to be closed
> return al;
>
> }
> ====================================================
> (we have add
> For these pieces of code, we use datareader to get data, I wonder:
> 1. is it necessary we explicitely close the datareader?

It's common practise, to close what you open :)

> 2. if answer yes for 1., and if OracleHelper.ExecuteReader() falied,
> does it mean
> we cannot close connection?

A connection is the platform over which commands are executed. If a
command fails, the connection is still there. So that should be
closable without the outcome of the command.

> 4. for some objects, do we need to displose them as soon as it is not
> used? example:
> =================================================
> OracleCommand cmd = new OracleCommand();
> PrepareCommand(cmd, connection, (OracleTransaction)null, commandType,
> commandText, commandParameters);
> //create the DataAdapter & DataSet
> OracleDataAdapter da = new OracleDataAdapter(cmd);
> DataSet ds = new DataSet();
>
> //fill the DataSet using default values for DataTable names, etc.
> da.Fill(ds);
>
> cmd.Dispose();
> da.Dispose();
> =======================================

If the connection was open, it stays open after this snippet. Be aware
of that.

Frans

--
------------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

Srinivas
Mon Jun 27 18:33:38 CDT 2005

Hello,
I too have the same problem in Sql Server for my application. we increased
the connection time out parameter for connection string from 3 to 30 seconds
and we never got that problem... try it...

srinivas

"Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xs4all.nl> wrote in message
news:xn0e3ygrb3spud000@news.microsoft.com...
> donotfill wrote:
> > We have a asp.net web application with oracle 9.1.
> > Data access layer we use is DAAB 2.0.
> > We use connection pool and set max pool size = 150.
>
> It's important to know which Oracle provider you're using. As you
> don't mention any, I pressume you're using Microsoft's client.
>
> There were some bugs in this client, and most of them (if not all) are
> fixed with .NET 1.1 SP1. Be sure to install that service pack of .NET
> on your webservers.
>
> > But we always get the following error almost every month ( we have to
> > restart web server to make it work):
> > Timeout expired, The timeout period elapsed prior to obtaining a
> > connection from the pool. This may have occured becasue all pooled
> > connections where in use and max pool size was reached.
> >
> > As many perople said, after I went though whole implementation, I did
> > not find any connections we did not close. Anyone have good advice
> > for how to debug such issue? or recommend some tool?
> > I have seen lots of similar questions and topics, and found many
> > answers are extremely confused.
> > Can anyone make clear for my some questions?
> > 1. We use USING to dispose connection and OracleDataReader in most
> > cases, but also there are some exceptions, we use close() to close
> > connection in Catch block of try-catch. here is example:
> > ==============================================
> > OracleConnection cn = new OracleConnection(connectionString);
> > cn.Open();
> >
> > try
> > {
> > return ExecuteReader(cn, null, commandType, commandText,
> > commandParameters, OracleConnectionOwnership.Internal);
> > }
> > catch
> > {
> > cn.Close();
> > throw;
> > }
> > ==========================================
> >
> > Is anything wrong for these pieces of code?
>
> No, seems ok, well 'ok' as in: it should work. Though I wouldn't pass
> datareaders around if I were you. Because: in the snippet above, you
> open a connection and return a reader. That means that when the reader
> is returned, the connection is still open. What happens in the caller
> of this routine which uses the reader, in the case of an exception? You
> then also close the connection?
>
> > 2. In many cases, we opened connection in one function, and call
> > another to process the real operation of DB., example:
> > ===================================================
> > using (OracleConnection cn = new OracleConnection(connectionString))
> > {
> > cn.Open();
> > return ExecuteNonQuery(cn, commandType, commandText,
> > commandParameters); }
> > ===================================================
> > if ExecuteNonQuery() got exception, the USING still can dispose
> > connection?
>
> Yes. In KB 830173, a bug is described in the Oracle provider from MS
> which shows it doesn't do that always, though this bug was fixed in
> .NET 1.1 SP1.
>
> > 3. ===================================================
> > using(OracleDataReader r =
> > OracleHelper.ExecuteReader(ConnectionManager.GetConnStr(),
> > CommandType.Text, sql.SQLString, pa))
> > {
> > ArrayList al = QueryResultsParser.Parse(r, request);
> > r.Close(); //Force the OracleDataReader to be closed
> > return al;
> >
> > }
> > ====================================================
> > (we have add
> > For these pieces of code, we use datareader to get data, I wonder:
> > 1. is it necessary we explicitely close the datareader?
>
> It's common practise, to close what you open :)
>
> > 2. if answer yes for 1., and if OracleHelper.ExecuteReader() falied,
> > does it mean
> > we cannot close connection?
>
> A connection is the platform over which commands are executed. If a
> command fails, the connection is still there. So that should be
> closable without the outcome of the command.
>
> > 4. for some objects, do we need to displose them as soon as it is not
> > used? example:
> > =================================================
> > OracleCommand cmd = new OracleCommand();
> > PrepareCommand(cmd, connection, (OracleTransaction)null, commandType,
> > commandText, commandParameters);
> > //create the DataAdapter & DataSet
> > OracleDataAdapter da = new OracleDataAdapter(cmd);
> > DataSet ds = new DataSet();
> >
> > //fill the DataSet using default values for DataTable names, etc.
> > da.Fill(ds);
> >
> > cmd.Dispose();
> > da.Dispose();
> > =======================================
>
> If the connection was open, it stays open after this snippet. Be aware
> of that.
>
> Frans
>
> --
> ------------------------------------------------------------------------
> Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
> My .NET blog: http://weblogs.asp.net/fbouma
> Microsoft MVP (C#)
> ------------------------------------------------------------------------

William
Mon Jun 27 19:20:17 CDT 2005

Yes, that might work if the operations you're executing are consuming more
CPU time than is available to share among the users. I expect that your
system (if this works) is on the edge of its performance envelope.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"Srinivas Kollipara" <skollipara@stratasolutions.com> wrote in message
news:uK61gC3eFHA.1504@TK2MSFTNGP15.phx.gbl...
> Hello,
> I too have the same problem in Sql Server for my application. we increased
> the connection time out parameter for connection string from 3 to 30
> seconds
> and we never got that problem... try it...
>
> srinivas
>
> "Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xs4all.nl> wrote in message
> news:xn0e3ygrb3spud000@news.microsoft.com...
>> donotfill wrote:
>> > We have a asp.net web application with oracle 9.1.
>> > Data access layer we use is DAAB 2.0.
>> > We use connection pool and set max pool size = 150.
>>
>> It's important to know which Oracle provider you're using. As you
>> don't mention any, I pressume you're using Microsoft's client.
>>
>> There were some bugs in this client, and most of them (if not all) are
>> fixed with .NET 1.1 SP1. Be sure to install that service pack of .NET
>> on your webservers.
>>
>> > But we always get the following error almost every month ( we have to
>> > restart web server to make it work):
>> > Timeout expired, The timeout period elapsed prior to obtaining a
>> > connection from the pool. This may have occured becasue all pooled
>> > connections where in use and max pool size was reached.
>> >
>> > As many perople said, after I went though whole implementation, I did
>> > not find any connections we did not close. Anyone have good advice
>> > for how to debug such issue? or recommend some tool?
>> > I have seen lots of similar questions and topics, and found many
>> > answers are extremely confused.
>> > Can anyone make clear for my some questions?
>> > 1. We use USING to dispose connection and OracleDataReader in most
>> > cases, but also there are some exceptions, we use close() to close
>> > connection in Catch block of try-catch. here is example:
>> > ==============================================
>> > OracleConnection cn = new OracleConnection(connectionString);
>> > cn.Open();
>> >
>> > try
>> > {
>> > return ExecuteReader(cn, null, commandType, commandText,
>> > commandParameters, OracleConnectionOwnership.Internal);
>> > }
>> > catch
>> > {
>> > cn.Close();
>> > throw;
>> > }
>> > ==========================================
>> >
>> > Is anything wrong for these pieces of code?
>>
>> No, seems ok, well 'ok' as in: it should work. Though I wouldn't pass
>> datareaders around if I were you. Because: in the snippet above, you
>> open a connection and return a reader. That means that when the reader
>> is returned, the connection is still open. What happens in the caller
>> of this routine which uses the reader, in the case of an exception? You
>> then also close the connection?
>>
>> > 2. In many cases, we opened connection in one function, and call
>> > another to process the real operation of DB., example:
>> > ===================================================
>> > using (OracleConnection cn = new OracleConnection(connectionString))
>> > {
>> > cn.Open();
>> > return ExecuteNonQuery(cn, commandType, commandText,
>> > commandParameters); }
>> > ===================================================
>> > if ExecuteNonQuery() got exception, the USING still can dispose
>> > connection?
>>
>> Yes. In KB 830173, a bug is described in the Oracle provider from MS
>> which shows it doesn't do that always, though this bug was fixed in
>> .NET 1.1 SP1.
>>
>> > 3. ===================================================
>> > using(OracleDataReader r =
>> > OracleHelper.ExecuteReader(ConnectionManager.GetConnStr(),
>> > CommandType.Text, sql.SQLString, pa))
>> > {
>> > ArrayList al = QueryResultsParser.Parse(r, request);
>> > r.Close(); //Force the OracleDataReader to be closed
>> > return al;
>> >
>> > }
>> > ====================================================
>> > (we have add
>> > For these pieces of code, we use datareader to get data, I wonder:
>> > 1. is it necessary we explicitely close the datareader?
>>
>> It's common practise, to close what you open :)
>>
>> > 2. if answer yes for 1., and if OracleHelper.ExecuteReader() falied,
>> > does it mean
>> > we cannot close connection?
>>
>> A connection is the platform over which commands are executed. If a
>> command fails, the connection is still there. So that should be
>> closable without the outcome of the command.
>>
>> > 4. for some objects, do we need to displose them as soon as it is not
>> > used? example:
>> > =================================================
>> > OracleCommand cmd = new OracleCommand();
>> > PrepareCommand(cmd, connection, (OracleTransaction)null, commandType,
>> > commandText, commandParameters);
>> > //create the DataAdapter & DataSet
>> > OracleDataAdapter da = new OracleDataAdapter(cmd);
>> > DataSet ds = new DataSet();
>> >
>> > //fill the DataSet using default values for DataTable names, etc.
>> > da.Fill(ds);
>> >
>> > cmd.Dispose();
>> > da.Dispose();
>> > =======================================
>>
>> If the connection was open, it stays open after this snippet. Be aware
>> of that.
>>
>> Frans
>>
>> --
>> ------------------------------------------------------------------------
>> Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
>> My .NET blog: http://weblogs.asp.net/fbouma
>> Microsoft MVP (C#)
>> ------------------------------------------------------------------------
>
>

donotfill
Mon Jun 27 21:55:04 CDT 2005

Hi,

How to setup timeout in connection string?
I did not see the parameter in OracleConnection.ConnectionString Property.

I really need expert's help, but I donot want to get confused answer.


regards,

Eric



"William (Bill) Vaughn" wrote:

> Yes, that might work if the operations you're executing are consuming more
> CPU time than is available to share among the users. I expect that your
> system (if this works) is on the edge of its performance envelope.
>
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
>
> "Srinivas Kollipara" <skollipara@stratasolutions.com> wrote in message
> news:uK61gC3eFHA.1504@TK2MSFTNGP15.phx.gbl...
> > Hello,
> > I too have the same problem in Sql Server for my application. we increased
> > the connection time out parameter for connection string from 3 to 30
> > seconds
> > and we never got that problem... try it...
> >
> > srinivas
> >
> > "Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xs4all.nl> wrote in message
> > news:xn0e3ygrb3spud000@news.microsoft.com...
> >> donotfill wrote:
> >> > We have a asp.net web application with oracle 9.1.
> >> > Data access layer we use is DAAB 2.0.
> >> > We use connection pool and set max pool size = 150.
> >>
> >> It's important to know which Oracle provider you're using. As you
> >> don't mention any, I pressume you're using Microsoft's client.
> >>
> >> There were some bugs in this client, and most of them (if not all) are
> >> fixed with .NET 1.1 SP1. Be sure to install that service pack of .NET
> >> on your webservers.
> >>
> >> > But we always get the following error almost every month ( we have to
> >> > restart web server to make it work):
> >> > Timeout expired, The timeout period elapsed prior to obtaining a
> >> > connection from the pool. This may have occured becasue all pooled
> >> > connections where in use and max pool size was reached.
> >> >
> >> > As many perople said, after I went though whole implementation, I did
> >> > not find any connections we did not close. Anyone have good advice
> >> > for how to debug such issue? or recommend some tool?
> >> > I have seen lots of similar questions and topics, and found many
> >> > answers are extremely confused.
> >> > Can anyone make clear for my some questions?
> >> > 1. We use USING to dispose connection and OracleDataReader in most
> >> > cases, but also there are some exceptions, we use close() to close
> >> > connection in Catch block of try-catch. here is example:
> >> > ==============================================
> >> > OracleConnection cn = new OracleConnection(connectionString);
> >> > cn.Open();
> >> >
> >> > try
> >> > {
> >> > return ExecuteReader(cn, null, commandType, commandText,
> >> > commandParameters, OracleConnectionOwnership.Internal);
> >> > }
> >> > catch
> >> > {
> >> > cn.Close();
> >> > throw;
> >> > }
> >> > ==========================================
> >> >
> >> > Is anything wrong for these pieces of code?
> >>
> >> No, seems ok, well 'ok' as in: it should work. Though I wouldn't pass
> >> datareaders around if I were you. Because: in the snippet above, you
> >> open a connection and return a reader. That means that when the reader
> >> is returned, the connection is still open. What happens in the caller
> >> of this routine which uses the reader, in the case of an exception? You
> >> then also close the connection?
> >>
> >> > 2. In many cases, we opened connection in one function, and call
> >> > another to process the real operation of DB., example:
> >> > ===================================================
> >> > using (OracleConnection cn = new OracleConnection(connectionString))
> >> > {
> >> > cn.Open();
> >> > return ExecuteNonQuery(cn, commandType, commandText,
> >> > commandParameters); }
> >> > ===================================================
> >> > if ExecuteNonQuery() got exception, the USING still can dispose
> >> > connection?
> >>
> >> Yes. In KB 830173, a bug is described in the Oracle provider from MS
> >> which shows it doesn't do that always, though this bug was fixed in
> >> .NET 1.1 SP1.
> >>
> >> > 3. ===================================================
> >> > using(OracleDataReader r =
> >> > OracleHelper.ExecuteReader(ConnectionManager.GetConnStr(),
> >> > CommandType.Text, sql.SQLString, pa))
> >> > {
> >> > ArrayList al = QueryResultsParser.Parse(r, request);
> >> > r.Close(); //Force the OracleDataReader to be closed
> >> > return al;
> >> >
> >> > }
> >> > ====================================================
> >> > (we have add
> >> > For these pieces of code, we use datareader to get data, I wonder:
> >> > 1. is it necessary we explicitely close the datareader?
> >>
> >> It's common practise, to close what you open :)
> >>
> >> > 2. if answer yes for 1., and if OracleHelper.ExecuteReader() falied,
> >> > does it mean
> >> > we cannot close connection?
> >>
> >> A connection is the platform over which commands are executed. If a
> >> command fails, the connection is still there. So that should be
> >> closable without the outcome of the command.
> >>
> >> > 4. for some objects, do we need to displose them as soon as it is not
> >> > used? example:
> >> > =================================================
> >> > OracleCommand cmd = new OracleCommand();
> >> > PrepareCommand(cmd, connection, (OracleTransaction)null, commandType,
> >> > commandText, commandParameters);
> >> > //create the DataAdapter & DataSet
> >> > OracleDataAdapter da = new OracleDataAdapter(cmd);
> >> > DataSet ds = new DataSet();
> >> >
> >> > //fill the DataSet using default values for DataTable names, etc.
> >> > da.Fill(ds);
> >> >
> >> > cmd.Dispose();
> >> > da.Dispose();
> >> > =======================================
> >>
> >> If the connection was open, it stays open after this snippet. Be aware
> >> of that.
> >>
> >> Frans
> >>
> >> --
> >> ------------------------------------------------------------------------
> >> Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
> >> My .NET blog: http://weblogs.asp.net/fbouma
> >> Microsoft MVP (C#)
> >> ------------------------------------------------------------------------
> >
> >
>
>
>

donotfill
Mon Jun 27 22:05:03 CDT 2005

Hi,

if you gus think Connection Lifetime ( dfault=0) as connection timeout,
then A value of zero (0) causes pooled connections to have the maximum
connection timeout.

So I donot know how to set timeout in connection string?

regards,

Eric


"donotfill" wrote:

> Hi,
>
> How to setup timeout in connection string?
> I did not see the parameter in OracleConnection.ConnectionString Property.
>
> I really need expert's help, but I donot want to get confused answer.
>
>
> regards,
>
> Eric
>
>
>
> "William (Bill) Vaughn" wrote:
>
> > Yes, that might work if the operations you're executing are consuming more
> > CPU time than is available to share among the users. I expect that your
> > system (if this works) is on the edge of its performance envelope.
> >
> > --
> > ____________________________________
> > William (Bill) Vaughn
> > Author, Mentor, Consultant
> > Microsoft MVP
> > www.betav.com/blog/billva
> > www.betav.com
> > Please reply only to the newsgroup so that others can benefit.
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> > __________________________________
> >
> > "Srinivas Kollipara" <skollipara@stratasolutions.com> wrote in message
> > news:uK61gC3eFHA.1504@TK2MSFTNGP15.phx.gbl...
> > > Hello,
> > > I too have the same problem in Sql Server for my application. we increased
> > > the connection time out parameter for connection string from 3 to 30
> > > seconds
> > > and we never got that problem... try it...
> > >
> > > srinivas
> > >
> > > "Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xs4all.nl> wrote in message
> > > news:xn0e3ygrb3spud000@news.microsoft.com...
> > >> donotfill wrote:
> > >> > We have a asp.net web application with oracle 9.1.
> > >> > Data access layer we use is DAAB 2.0.
> > >> > We use connection pool and set max pool size = 150.
> > >>
> > >> It's important to know which Oracle provider you're using. As you
> > >> don't mention any, I pressume you're using Microsoft's client.
> > >>
> > >> There were some bugs in this client, and most of them (if not all) are
> > >> fixed with .NET 1.1 SP1. Be sure to install that service pack of .NET
> > >> on your webservers.
> > >>
> > >> > But we always get the following error almost every month ( we have to
> > >> > restart web server to make it work):
> > >> > Timeout expired, The timeout period elapsed prior to obtaining a
> > >> > connection from the pool. This may have occured becasue all pooled
> > >> > connections where in use and max pool size was reached.
> > >> >
> > >> > As many perople said, after I went though whole implementation, I did
> > >> > not find any connections we did not close. Anyone have good advice
> > >> > for how to debug such issue? or recommend some tool?
> > >> > I have seen lots of similar questions and topics, and found many
> > >> > answers are extremely confused.
> > >> > Can anyone make clear for my some questions?
> > >> > 1. We use USING to dispose connection and OracleDataReader in most
> > >> > cases, but also there are some exceptions, we use close() to close
> > >> > connection in Catch block of try-catch. here is example:
> > >> > ==============================================
> > >> > OracleConnection cn = new OracleConnection(connectionString);
> > >> > cn.Open();
> > >> >
> > >> > try
> > >> > {
> > >> > return ExecuteReader(cn, null, commandType, commandText,
> > >> > commandParameters, OracleConnectionOwnership.Internal);
> > >> > }
> > >> > catch
> > >> > {
> > >> > cn.Close();
> > >> > throw;
> > >> > }
> > >> > ==========================================
> > >> >
> > >> > Is anything wrong for these pieces of code?
> > >>
> > >> No, seems ok, well 'ok' as in: it should work. Though I wouldn't pass
> > >> datareaders around if I were you. Because: in the snippet above, you
> > >> open a connection and return a reader. That means that when the reader
> > >> is returned, the connection is still open. What happens in the caller
> > >> of this routine which uses the reader, in the case of an exception? You
> > >> then also close the connection?
> > >>
> > >> > 2. In many cases, we opened connection in one function, and call
> > >> > another to process the real operation of DB., example:
> > >> > ===================================================
> > >> > using (OracleConnection cn = new OracleConnection(connectionString))
> > >> > {
> > >> > cn.Open();
> > >> > return ExecuteNonQuery(cn, commandType, commandText,
> > >> > commandParameters); }
> > >> > ===================================================
> > >> > if ExecuteNonQuery() got exception, the USING still can dispose
> > >> > connection?
> > >>
> > >> Yes. In KB 830173, a bug is described in the Oracle provider from MS
> > >> which shows it doesn't do that always, though this bug was fixed in
> > >> .NET 1.1 SP1.
> > >>
> > >> > 3. ===================================================
> > >> > using(OracleDataReader r =
> > >> > OracleHelper.ExecuteReader(ConnectionManager.GetConnStr(),
> > >> > CommandType.Text, sql.SQLString, pa))
> > >> > {
> > >> > ArrayList al = QueryResultsParser.Parse(r, request);
> > >> > r.Close(); //Force the OracleDataReader to be closed
> > >> > return al;
> > >> >
> > >> > }
> > >> > ====================================================
> > >> > (we have add
> > >> > For these pieces of code, we use datareader to get data, I wonder:
> > >> > 1. is it necessary we explicitely close the datareader?
> > >>
> > >> It's common practise, to close what you open :)
> > >>
> > >> > 2. if answer yes for 1., and if OracleHelper.ExecuteReader() falied,
> > >> > does it mean
> > >> > we cannot close connection?
> > >>
> > >> A connection is the platform over which commands are executed. If a
> > >> command fails, the connection is still there. So that should be
> > >> closable without the outcome of the command.
> > >>
> > >> > 4. for some objects, do we need to displose them as soon as it is not
> > >> > used? example:
> > >> > =================================================
> > >> > OracleCommand cmd = new OracleCommand();
> > >> > PrepareCommand(cmd, connection, (OracleTransaction)null, commandType,
> > >> > commandText, commandParameters);
> > >> > //create the DataAdapter & DataSet
> > >> > OracleDataAdapter da = new OracleDataAdapter(cmd);
> > >> > DataSet ds = new DataSet();
> > >> >
> > >> > //fill the DataSet using default values for DataTable names, etc.
> > >> > da.Fill(ds);
> > >> >
> > >> > cmd.Dispose();
> > >> > da.Dispose();
> > >> > =======================================
> > >>
> > >> If the connection was open, it stays open after this snippet. Be aware
> > >> of that.
> > >>
> > >> Frans
> > >>
> > >> --
> > >> ------------------------------------------------------------------------
> > >> Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
> > >> My .NET blog: http://weblogs.asp.net/fbouma
> > >> Microsoft MVP (C#)
> > >> ------------------------------------------------------------------------
> > >
> > >
> >
> >
> >

William
Tue Jun 28 11:40:07 CDT 2005

This is a formula for disaster. If the pool overfills, you'll never find
out--just lock up.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"donotfill" <donotfill@discussions.microsoft.com> wrote in message
news:28541F9C-0BE3-45BA-BB01-F5B234C5B910@microsoft.com...
> Hi,
>
> if you gus think Connection Lifetime ( dfault=0) as connection timeout,
> then A value of zero (0) causes pooled connections to have the maximum
> connection timeout.
>
> So I donot know how to set timeout in connection string?
>
> regards,
>
> Eric
>
>
> "donotfill" wrote:
>
>> Hi,
>>
>> How to setup timeout in connection string?
>> I did not see the parameter in OracleConnection.ConnectionString
>> Property.
>>
>> I really need expert's help, but I donot want to get confused answer.
>>
>>
>> regards,
>>
>> Eric
>>
>>
>>
>> "William (Bill) Vaughn" wrote:
>>
>> > Yes, that might work if the operations you're executing are consuming
>> > more
>> > CPU time than is available to share among the users. I expect that your
>> > system (if this works) is on the edge of its performance envelope.
>> >
>> > --
>> > ____________________________________
>> > William (Bill) Vaughn
>> > Author, Mentor, Consultant
>> > Microsoft MVP
>> > www.betav.com/blog/billva
>> > www.betav.com
>> > Please reply only to the newsgroup so that others can benefit.
>> > This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> > __________________________________
>> >
>> > "Srinivas Kollipara" <skollipara@stratasolutions.com> wrote in message
>> > news:uK61gC3eFHA.1504@TK2MSFTNGP15.phx.gbl...
>> > > Hello,
>> > > I too have the same problem in Sql Server for my application. we
>> > > increased
>> > > the connection time out parameter for connection string from 3 to 30
>> > > seconds
>> > > and we never got that problem... try it...
>> > >
>> > > srinivas
>> > >
>> > > "Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xs4all.nl> wrote in
>> > > message
>> > > news:xn0e3ygrb3spud000@news.microsoft.com...
>> > >> donotfill wrote:
>> > >> > We have a asp.net web application with oracle 9.1.
>> > >> > Data access layer we use is DAAB 2.0.
>> > >> > We use connection pool and set max pool size = 150.
>> > >>
>> > >> It's important to know which Oracle provider you're using. As you
>> > >> don't mention any, I pressume you're using Microsoft's client.
>> > >>
>> > >> There were some bugs in this client, and most of them (if not all)
>> > >> are
>> > >> fixed with .NET 1.1 SP1. Be sure to install that service pack of
>> > >> .NET
>> > >> on your webservers.
>> > >>
>> > >> > But we always get the following error almost every month ( we have
>> > >> > to
>> > >> > restart web server to make it work):
>> > >> > Timeout expired, The timeout period elapsed prior to obtaining a
>> > >> > connection from the pool. This may have occured becasue all pooled
>> > >> > connections where in use and max pool size was reached.
>> > >> >
>> > >> > As many perople said, after I went though whole implementation, I
>> > >> > did
>> > >> > not find any connections we did not close. Anyone have good advice
>> > >> > for how to debug such issue? or recommend some tool?
>> > >> > I have seen lots of similar questions and topics, and found many
>> > >> > answers are extremely confused.
>> > >> > Can anyone make clear for my some questions?
>> > >> > 1. We use USING to dispose connection and OracleDataReader in
>> > >> > most
>> > >> > cases, but also there are some exceptions, we use close() to close
>> > >> > connection in Catch block of try-catch. here is example:
>> > >> > ==============================================
>> > >> > OracleConnection cn = new OracleConnection(connectionString);
>> > >> > cn.Open();
>> > >> >
>> > >> > try
>> > >> > {
>> > >> > return ExecuteReader(cn, null, commandType, commandText,
>> > >> > commandParameters, OracleConnectionOwnership.Internal);
>> > >> > }
>> > >> > catch
>> > >> > {
>> > >> > cn.Close();
>> > >> > throw;
>> > >> > }
>> > >> > ==========================================
>> > >> >
>> > >> > Is anything wrong for these pieces of code?
>> > >>
>> > >> No, seems ok, well 'ok' as in: it should work. Though I wouldn't
>> > >> pass
>> > >> datareaders around if I were you. Because: in the snippet above, you
>> > >> open a connection and return a reader. That means that when the
>> > >> reader
>> > >> is returned, the connection is still open. What happens in the
>> > >> caller
>> > >> of this routine which uses the reader, in the case of an exception?
>> > >> You
>> > >> then also close the connection?
>> > >>
>> > >> > 2. In many cases, we opened connection in one function, and call
>> > >> > another to process the real operation of DB., example:
>> > >> > ===================================================
>> > >> > using (OracleConnection cn = new
>> > >> > OracleConnection(connectionString))
>> > >> > {
>> > >> > cn.Open();
>> > >> > return ExecuteNonQuery(cn, commandType, commandText,
>> > >> > commandParameters); }
>> > >> > ===================================================
>> > >> > if ExecuteNonQuery() got exception, the USING still can dispose
>> > >> > connection?
>> > >>
>> > >> Yes. In KB 830173, a bug is described in the Oracle provider from MS
>> > >> which shows it doesn't do that always, though this bug was fixed in
>> > >> .NET 1.1 SP1.
>> > >>
>> > >> > 3. ===================================================
>> > >> > using(OracleDataReader r =
>> > >> > OracleHelper.ExecuteReader(ConnectionManager.GetConnStr(),
>> > >> > CommandType.Text, sql.SQLString, pa))
>> > >> > {
>> > >> > ArrayList al = QueryResultsParser.Parse(r, request);
>> > >> > r.Close(); //Force the OracleDataReader to be closed
>> > >> > return al;
>> > >> >
>> > >> > }
>> > >> > ====================================================
>> > >> > (we have add
>> > >> > For these pieces of code, we use datareader to get data, I wonder:
>> > >> > 1. is it necessary we explicitely close the datareader?
>> > >>
>> > >> It's common practise, to close what you open :)
>> > >>
>> > >> > 2. if answer yes for 1., and if OracleHelper.ExecuteReader()
>> > >> > falied,
>> > >> > does it mean
>> > >> > we cannot close connection?
>> > >>
>> > >> A connection is the platform over which commands are executed. If a
>> > >> command fails, the connection is still there. So that should be
>> > >> closable without the outcome of the command.
>> > >>
>> > >> > 4. for some objects, do we need to displose them as soon as it is
>> > >> > not
>> > >> > used? example:
>> > >> > =================================================
>> > >> > OracleCommand cmd = new OracleCommand();
>> > >> > PrepareCommand(cmd, connection, (OracleTransaction)null,
>> > >> > commandType,
>> > >> > commandText, commandParameters);
>> > >> > //create the DataAdapter & DataSet
>> > >> > OracleDataAdapter da = new OracleDataAdapter(cmd);
>> > >> > DataSet ds = new DataSet();
>> > >> >
>> > >> > //fill the DataSet using default values for DataTable names, etc.
>> > >> > da.Fill(ds);
>> > >> >
>> > >> > cmd.Dispose();
>> > >> > da.Dispose();
>> > >> > =======================================
>> > >>
>> > >> If the connection was open, it stays open after this snippet. Be
>> > >> aware
>> > >> of that.
>> > >>
>> > >> Frans
>> > >>
>> > >> --
>> > >> ------------------------------------------------------------------------
>> > >> Get LLBLGen Pro, productive O/R mapping for .NET:
>> > >> http://www.llblgen.com
>> > >> My .NET blog: http://weblogs.asp.net/fbouma
>> > >> Microsoft MVP (C#)
>> > >> ------------------------------------------------------------------------
>> > >
>> > >
>> >
>> >
>> >