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();
=======================================

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#)
------------------------------------------------------------------------

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]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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#)
> ------------------------------------------------------------------------

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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]" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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#)
>> ------------------------------------------------------------------------
>
>

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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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]" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> 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#)
> >> ------------------------------------------------------------------------
> >
> >
>
>
>

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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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]" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > >> 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#)
> > >> ------------------------------------------------------------------------
> > >
> > >
> >
> >
> >

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" <(E-Mail Removed)> wrote in message
news:28541F9C-0BE3-45BA-BB01-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> > news:(E-Mail Removed)...
>> > > 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]" <(E-Mail Removed)> wrote in
>> > > message
>> > > news:(E-Mail Removed)...
>> > >> 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#)
>> > >> ------------------------------------------------------------------------
>> > >
>> > >
>> >
>> >
>> >