Keeping TAdoConnection alive

Question

In one of the question here: How can I detect that a TadoConnection lost the communication with the server?

It was suggested to create a Timer and poll the SQL server.

From the answer:

TTimer is OK. The query should be performed in a thread, where the corresponding connection is used. Not a must although, but it is a different issue.

Should I use the same/main connection (in the DM of the main thread) or should I create a new connection in the timer event to "ping" the SQL server?

Is polling the SQL server will help keeping the connection alive in the first place?

There is no problem to use the same(main) connection in main thread . "Is polling the SQL server will help keeping the connection alive in the first place?" No. — Val Marinov, Jul 23 '15 at 13:00
Bear in mind ADO uses COM, which is thread-specific. Meaning an ADO connection cannot be shared in different threads. — Jerry Dodge, Jul 23 '15 at 13:49
@JerryDodge, Thanks. I am fully aware of that. So what do you suggest then? — zig, Jul 28 '15 at 13:15

Interface Unknown · Answer 1 · 2015-07-29T22:40:52.650

Using a timer to poll connection is a bad idea. Doing unneccessary polls consumes resources and is generally bad concept of your application design.

The best way to restore a broken connection is to start using is again in try block and then re-establish it within except..end block. Take a look at this piece of code:

// Re-establishes connection every time it gets broken
procedure RespawnConnection;
begin
  FConnection := TADOConnection.Create(nil);
  FADODataSet := TADODataSet.Create(nil);
  with FConnection do
  begin
    ConnectionString := AdoConnectionString; // your CS here
    LoginPrompt := False;
    Connected := True;
  end;
  FADODataSet.Connection := FConnection;
end;

// An exposed method to do ADO queries
function Query(const sql: string): TADODataSet;
begin
  try
    Result := FADODataSet;
    FADODataSet.CommandText := sql; // if FADODataSet is not assigned,
                                    // this line throws an excepion...
    FADODataSet.Open;
  except
    RespawnConnection; // ...which is properly handled here...
    Result := Query(sql); // ...and then repeats the query
  end;
end;

Don't forget to free both objects in FormClose event (unless they are components on your form).

In case of heavy and time-consuming queries, it may be worth to offload the whole ADO stuff to a separate thread. Running such query in a separate thread would be more efficient in terms of user experience. Once const sql: string is passed to a thread, the thread will do the magic, while user keeps interacting with the main form.

If you're up to implement a separate thread concept, don't forget to call CoInitialize and CoUninizialize because ADO is based on COM (as Jerry mentined) and uses appartment-threaded objects.

Keeping TAdoConnection alive

1 Answers1