469,346 Members | 6,539 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,346 developers. It's quick & easy.

database connection loss


Hallo!

I'm working with C# .NET 2.0, implementing Client/Server Applications which
are connecting via Network to SQL-Server or Oracle Databases.
To stay independent from the underlaying Database I use
System.Data.Common.DBConnection and .DBCommand.

How can I keep aware from connection losses (network not availeable,
db-server not available...)?
Are there any strategies to detect this broken connections, and how can I
implement reconnecting to the database?

The only solution I figured out, is creating a thread which frequntly
performs a SELECT 1 (MSSQL) or SELECT 1 FROM DUAL (Oracle) within a
try-catch block. When an exception occurs I have to send my other
worker-threads to sleep, till I'm able to reopen the database connection and
my Select-Statement works again. Then I have to trigger the other threads to
reopen their connections and continue working (every thread gots his own
DBConnection).

For me this seems a little bit to complicated and insecure.
It also could happen that one of the worker threads gots a timeout due
locking or delayed server responstime, and will loose his Connection in fact
of this exception. Such, or similar problems I can not get handled with my
solution.

Are there any suggestions, patterns or automathisms with ADO 2.0?

with friendly regards

Martin
Apr 12 '06 #1
3 1868
There are a couple of things you can do outside of ADO to test that the
serveris available and listening on the desired port.

Netstat will tell you if the server is:

1. Available and online
2. Reporting that it is listening on the desired DB TCP/IP ports (i.e.,
SQL server is by default port 1433)

Telnet will tell you:

1. The server is available and online
2. Connection to the desired port is possible

Niether of these solutions will tell you that the server is bottlenecked,
experiencing long queues, in single use mode, or in a unstable state.

In your situation, I would plan worse case and code in that manner.

Make use of transactions, and minimize connectivitiy when possible.
Devise failover solutions that allow for resuming once a failed thread
restarts.
Log exceptions for later review
Request SLA from Network Support along with best times for processing
Prepare data prior to committing it
Open Connecion
store prepared data locally with a processed flag
create a transaction
commit data
complete transaction
Close connection
mark prepared data as processed
remove local cache

:Failover

Open locally cached prepared data
Open connection
create transaction
commit data
complete transaction
Close Connection
mark prepared data as processed
remove local cache
"Martin B" <ma************@gamed.com> wrote in message
news:44***********************@newsreader.inode.at ...

Hallo!

I'm working with C# .NET 2.0, implementing Client/Server Applications
which are connecting via Network to SQL-Server or Oracle Databases.
To stay independent from the underlaying Database I use
System.Data.Common.DBConnection and .DBCommand.

How can I keep aware from connection losses (network not availeable,
db-server not available...)?
Are there any strategies to detect this broken connections, and how can I
implement reconnecting to the database?

The only solution I figured out, is creating a thread which frequntly
performs a SELECT 1 (MSSQL) or SELECT 1 FROM DUAL (Oracle) within a
try-catch block. When an exception occurs I have to send my other
worker-threads to sleep, till I'm able to reopen the database connection
and my Select-Statement works again. Then I have to trigger the other
threads to reopen their connections and continue working (every thread
gots his own DBConnection).

For me this seems a little bit to complicated and insecure.
It also could happen that one of the worker threads gots a timeout due
locking or delayed server responstime, and will loose his Connection in
fact of this exception. Such, or similar problems I can not get handled
with my solution.

Are there any suggestions, patterns or automathisms with ADO 2.0?

with friendly regards

Martin

Apr 12 '06 #2
Thanx for your immediate Reply!

For me it's rather impossible to Close and Open the Connection at any
transaction. I have to process 5 transactions per second so it seems to
decrease my performance despite connection pooling.

What I wanted to know is, how to implement a simple and secure reconnect
policy in my program code.
It doesn't matter for me that there will be a data-loss on connection abort,
important is that after reestablishing and fixing the network or database
link physically (it's not my job) my program recognizes as soon as possible
the availability and restarts with his work.
That means removing the broken connection object (Close, Dispose I don't
know exactly) and recreating a new Connection for further transactions.
During the Connection isn't available for me my program should stand in
waiting position, ignoring all user events and testing the connection
periodically.

So the topics of relevance:

1.) detection of connection loss in my code (avoiding thread or program shut
down)
2.) cycling test of db availability
3.) reopen connections on availability

This points should work for several threads which are owning an connection
object for each of them.

with friendly regards

Martin

"AMDRIT" <am****@hotmail.com> wrote in message
news:ec****************@TK2MSFTNGP03.phx.gbl...
There are a couple of things you can do outside of ADO to test that the
serveris available and listening on the desired port.

Netstat will tell you if the server is:

1. Available and online
2. Reporting that it is listening on the desired DB TCP/IP ports
(i.e., SQL server is by default port 1433)

Telnet will tell you:

1. The server is available and online
2. Connection to the desired port is possible

Niether of these solutions will tell you that the server is bottlenecked,
experiencing long queues, in single use mode, or in a unstable state.

In your situation, I would plan worse case and code in that manner.

Make use of transactions, and minimize connectivitiy when possible.
Devise failover solutions that allow for resuming once a failed thread
restarts.
Log exceptions for later review
Request SLA from Network Support along with best times for processing
Prepare data prior to committing it
Open Connecion
store prepared data locally with a processed flag
create a transaction
commit data
complete transaction
Close connection
mark prepared data as processed
remove local cache

:Failover

Open locally cached prepared data
Open connection
create transaction
commit data
complete transaction
Close Connection
mark prepared data as processed
remove local cache
"Martin B" <ma************@gamed.com> wrote in message
news:44***********************@newsreader.inode.at ...

Hallo!

I'm working with C# .NET 2.0, implementing Client/Server Applications
which are connecting via Network to SQL-Server or Oracle Databases.
To stay independent from the underlaying Database I use
System.Data.Common.DBConnection and .DBCommand.

How can I keep aware from connection losses (network not availeable,
db-server not available...)?
Are there any strategies to detect this broken connections, and how can I
implement reconnecting to the database?

The only solution I figured out, is creating a thread which frequntly
performs a SELECT 1 (MSSQL) or SELECT 1 FROM DUAL (Oracle) within a
try-catch block. When an exception occurs I have to send my other
worker-threads to sleep, till I'm able to reopen the database connection
and my Select-Statement works again. Then I have to trigger the other
threads to reopen their connections and continue working (every thread
gots his own DBConnection).

For me this seems a little bit to complicated and insecure.
It also could happen that one of the worker threads gots a timeout due
locking or delayed server responstime, and will loose his Connection in
fact of this exception. Such, or similar problems I can not get handled
with my solution.

Are there any suggestions, patterns or automathisms with ADO 2.0?

with friendly regards

Martin


Apr 13 '06 #3
MS bugs: nowhere you are told how to work with connections. I guess the best is to
1. open connection
2. transaction
3. close connection

over and over again.

Regards Marcus

PS I have the same problem, open connections will be autoclosed and cannot be reopened (again MS bug).
---
Posted via www.DotNetSlackers.com
Apr 19 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Peter Larsson | last post: by
3 posts views Thread by markaelkins | last post: by
1 post views Thread by Sonya | last post: by
3 posts views Thread by Martin B | last post: by
5 posts views Thread by Dennis | last post: by
7 posts views Thread by wally | last post: by
8 posts views Thread by Rahul | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.