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" <amdrit@hotmail.com> wrote in message
news:ecntT%23jXGHA.1508@TK2MSFTNGP03.phx.gbl...[color=blue]
> 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" <martin.brugger@gamed.com> wrote in message
> news:443d047c$0$32387$79720d31@newsreader.inode.at ...[color=green]
>>
>> 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
>>
>>[/color]
>
>[/color]
