OleDBConnection problem if database sesison is killed or database is restarted

  • Thread starter Thread starter Jack Wright
  • Start date Start date
J

Jack Wright

Dear All,

We are facing problem for OleDBConnection if my connection to database
is killed or database server is restarted.
it gives error:
System.Data.OleDb.OleDbException: Session closed/terminated

Problem:

1. For executing SQL query we create new OleDBConnection, execute
that query and then instead of closing that connection,
we put that connection object in static arrayList
(freeConnectionList).

2. For next SQL query, it checks in that static arraylist for free
connection, if it gets any free connection, then uses that
connection object for executing query, else if it cannot find any
free connection in this arraylist, it creates new connection,
executes query and then add that connection in arraylist.

3. It works fine. But problem comes if my database session is killed
OR database is re-started.

4. if my database session is killed OR database is re-start, and I try
to fire query using connection object that is
there in static arraylist or using new OleDBConnection object, then
it generates error.

So if my database session is killed or database is restarted, then
1. I cannot use connection objects from static arraylist
2. and if I create new Connection object, then that new connection
object also fails to fire query.

Solution:

1. If my dadatabse session is killed or database is restarted, and I
try to fire a query, then it throws OleDBException, We will catch that
exception,
then then we will close all the connection from arraylist. For
this the code will be like this:

Pseudo Code:

public void ResetPool()
{
OleDBConnection dummyConnection = new OleDBConnection(connString);
// declare a dummy connection
dummyConnection.Open(); // open dummy connection

int i = freeConnectionList.Count-1;
while ( i >= 0 )
{
freeConnectionList.Close(); // close connection
i--;
}
dummyConnection.Close(); // close dummy connection
}


For this I have to create a dummy connection, open that connection and
then close that connection at the end when all connections from
arraylist are closed.
If I do these changes then it works fine.

Query:
Is this a correct method to handle situaton like database restart or
database session killed? OR there is any alternative way of doing the
same?

Thanx and Regards
Jack
 
Hi Jack,

You are reinventing the wheel (partialy) here.
You are basically doing pooling of pooling.
IOW you don't need to do it as OleDb provider already pools the connections
for you (when you close it isn't actually closed).
As per your problem - there is no clean way.
When you try to use broken connection you'll get an exception and connection
will be removed from pool.
So, yes, you have to try again. Maybe you might add a blank space to
connection string - to force new connection creation in this case.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Jack Wright said:
Dear All,

We are facing problem for OleDBConnection if my connection to database
is killed or database server is restarted.
it gives error:
System.Data.OleDb.OleDbException: Session closed/terminated

Problem:

1. For executing SQL query we create new OleDBConnection, execute
that query and then instead of closing that connection,
we put that connection object in static arrayList
(freeConnectionList).

2. For next SQL query, it checks in that static arraylist for free
connection, if it gets any free connection, then uses that
connection object for executing query, else if it cannot find any
free connection in this arraylist, it creates new connection,
executes query and then add that connection in arraylist.

3. It works fine. But problem comes if my database session is killed
OR database is re-started.

4. if my database session is killed OR database is re-start, and I try
to fire query using connection object that is
there in static arraylist or using new OleDBConnection object, then
it generates error.

So if my database session is killed or database is restarted, then
1. I cannot use connection objects from static arraylist
2. and if I create new Connection object, then that new connection
object also fails to fire query.

Solution:

1. If my dadatabse session is killed or database is restarted, and I
try to fire a query, then it throws OleDBException, We will catch that
exception,
then then we will close all the connection from arraylist. For
this the code will be like this:

Pseudo Code:

public void ResetPool()
{
OleDBConnection dummyConnection = new OleDBConnection(connString);
// declare a dummy connection
dummyConnection.Open(); // open dummy connection

int i = freeConnectionList.Count-1;
while ( i >= 0 )
{
freeConnectionList.Close(); // close connection
i--;
}
dummyConnection.Close(); // close dummy connection
}


For this I have to create a dummy connection, open that connection and
then close that connection at the end when all connections from
arraylist are closed.
If I do these changes then it works fine.

Query:
Is this a correct method to handle situaton like database restart or
database session killed? OR there is any alternative way of doing the
same?

Thanx and Regards
Jack
Click to expand...
 
Hi Miha,
Thanks for replying...
Agreeed that the OLEDBConnection must be closed...but if I don't store
the OLEDBConnection after closing in an freePoolArrayList and always
create a new OLEDBConnection Object a new connection is made to the
database and after some time I get the error
"maximum number of processes (400) exceeded"
Since mine is a Web application I need to store the OleDBConnection in
an arraylist and use it and do only open, execute & close and put it
back in the array...or have I understood it wrong...

Please help

Many Regards
Jack
 
Hi Jack,

Yes, you have to Close each connection ASAP.
However that doesn't mean that physical connection is closed - this part is
managed by connection pooling.
Are you telling that if you close each connection and create a new one every
time you need one (a proper way, btw) it blows after some time?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Jack Wright said:
Hi Miha,
Thanks for replying...
Agreeed that the OLEDBConnection must be closed...but if I don't store
the OLEDBConnection after closing in an freePoolArrayList and always
create a new OLEDBConnection Object a new connection is made to the
database and after some time I get the error
"maximum number of processes (400) exceeded"
Since mine is a Web application I need to store the OleDBConnection in
an arraylist and use it and do only open, execute & close and put it
back in the array...or have I understood it wrong...

Please help

Many Regards
Jack

"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
Hi Jack,

You are reinventing the wheel (partialy) here.
You are basically doing pooling of pooling.
IOW you don't need to do it as OleDb provider already pools the connections
for you (when you close it isn't actually closed).
As per your problem - there is no clean way.
When you try to use broken connection you'll get an exception and connection
will be removed from pool.
So, yes, you have to try again. Maybe you might add a blank space to
connection string - to force new connection creation in this case.
Click to expand...
Click to expand...
 
Dear Miha,
Every time I do
OleDBConnection dummyConnection = new OleDBConnection(connString);

after I close...a new connection is made to the database...hence I was
storing the OleDBConnection objects in a hashtable after calling its
close.

If my database session is killed or database is restarted, then I get
OleDBException.

We will catch that exception and then I want to reset my connection
pool(connection pool maintained by OleDB).
Is there is any method to reset connection pool?

Solutions I tried:
1. On catching 'System.Data.OleDb.OleDbException: Session
closed/terminated', I append some dummy parameters
to the existing string like this:
myConnectionString = myConnectionString + ";a=b;" //
"a=b;"
is a dummy key-val appended
Here it works fine bcoz the changes in connection string results in
new
OleDB connection pool.

2. I tried by putting extra sapaces in connection string, but it
doesnot
work. (OleDB removes all extra white spaces from the connection
string)

Query:
Is this a correct method to change connection string by using some
dummy
values(as explained in sol 1)?
OR is there any alternative way of doing the same?

Thanks & Regards
Jack

Miha Markic said:
Hi Jack,

Yes, you have to Close each connection ASAP.
However that doesn't mean that physical connection is closed - this part is
managed by connection pooling.
Are you telling that if you close each connection and create a new one every
time you need one (a proper way, btw) it blows after some time?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Jack Wright said:
Hi Miha,
Thanks for replying...
Agreeed that the OLEDBConnection must be closed...but if I don't store
the OLEDBConnection after closing in an freePoolArrayList and always
create a new OLEDBConnection Object a new connection is made to the
database and after some time I get the error
"maximum number of processes (400) exceeded"
Since mine is a Web application I need to store the OleDBConnection in
an arraylist and use it and do only open, execute & close and put it
back in the array...or have I understood it wrong...

Please help

Many Regards
Jack

"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
Hi Jack,

You are reinventing the wheel (partialy) here.
You are basically doing pooling of pooling.
IOW you don't need to do it as OleDb provider already pools the connections
for you (when you close it isn't actually closed).
As per your problem - there is no clean way.
When you try to use broken connection you'll get an exception and connection
will be removed from pool.
So, yes, you have to try again. Maybe you might add a blank space to
connection string - to force new connection creation in this case.
Click to expand...
Click to expand...
Click to expand...
 
We will catch that exception and then I want to reset my connection
pool(connection pool maintained by OleDB).
Is there is any method to reset connection pool?
Click to expand...

Unfortunatelly nope.
Solutions I tried:
1. On catching 'System.Data.OleDb.OleDbException: Session
closed/terminated', I append some dummy parameters
to the existing string like this:
myConnectionString = myConnectionString + ";a=b;" //
"a=b;"
is a dummy key-val appended
Here it works fine bcoz the changes in connection string results in
new
OleDB connection pool.

2. I tried by putting extra sapaces in connection string, but it
doesnot
work. (OleDB removes all extra white spaces from the connection
string)
Click to expand...
Query:
Is this a correct method to change connection string by using some
dummy
values(as explained in sol 1)?
OR is there any alternative way of doing the same?
Click to expand...

I guess that not recognized values would be ignored, so it makes sense.
However, there is normally only one connection in the pool (by default) if
you are not multithreading.
So, theoretically you would get only one exception (that connection is bad)
and when you retry with the same connection string it should reopen a new
one for you.
 
Back
Top