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