database connection loss

microsoft.public.dotnet.framework

12 Apr 2006 2:46 PM

Martin B

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