I have a Delphi windows service which uses ADOConnection to connect to SQL Server 2005 database.
Some times the database need to be restarted, in this case we have to restart our service.
Now we need to avoid restarting the service , instead handle this through code.
The service code should reestablish connection with the database itself.
But we do not want to use timer or thread. Whenever a user senda a request from client side
then the service should check connection with the database and then reestablish it if not connected.
I tried this by doing
Fired an arbitary query to database, if it fails then handle the exception by doing below:
What this is doing is not serving the request that the client sent, but does it fine from
the next time but only if a select is fired. Here I do not need to restart the service.
Also if there is an update or insert fired from the client side, this does not work. Please let me know how can I restablish connection with database through service code and serve all kind of request insert update and delete.
Also there is a problem by this solution. A new user is not able to connect to the service
through the client.