Best way to connect to database for this application

Question

I have a Delphi application which hits a database (usually MySql) every 60 seconds through a TTimer. The application is more or less an unattended bulletin board. If the network drops the application needs to continue running and connect back to the database when the connection is back. Often it might be over broadband, so chances are the connection is not always the best.

I am using the TAdoConnection component. This is opened at application startup and remains open. Whenever I need to make a new query I set the Connection to the open TAdoConnection. But I am finding this is not very reliable if there is a network drop.

What is the best way to connect to the database in this instance? I have seen ways where you can build the connection string directly into the TAdoQuery. Would this be the proper way? Or is this excessively resource intensive? Sometimes I need to open 5-10 queries to get all the information.

Or how about doing this in the TTimer.OnTimer event:

Create TAdoConnection

Do All Queries

Free TAdoConnection

Thanks.

Answer 1

You should use single TAdoConnection object to avoid setting connection string to each component. Keep your connection object closed and open it when you need to access data. Something like this:

procedure OnTimer;
begin
  MyAdoConnection.Open;
  try
    // Data access code here
    ...
  finally
     MyAdoConnection.Close;
  end; 
end;

You can additionally put another try/except block around MyAdoConnection.Open to catch situation where network is not available.

About second part of your question, best would be to put all your data access components in data module that you will create when you need to run data access procedures. Then you can put all your data access code in that data module and separate it from rest of the code.

You could try to open connection in OnCreate event of datamodule, but be careful to handle possible exceptions when opening connection. Close connection in OnDestroy event. Then you can use that datamodule like this:

procedure OnTimer;
var myDataModule : TMyDataModule;
begin
  myDataModule  := TMyDataModule.Create;
  try
    // Data access code here
    myDataModule.DoSomeDatabaseWork;
  finally
     myDataModule.Free;
  end; 
end;