sql server - Keeping TAdoConnection alive -

in 1 of question here: how can detect tadoconnection lost communication server?

it suggested create timer , poll sql server.

from answer:

ttimer ok. query should performed in thread, corresponding connection used. not must although, different issue.

should use same/main connection (in dm of main thread) or should create new connection in timer event "ping" sql server?

is polling sql server keeping connection alive in first place?

using timer poll connection bad idea. doing unneccessary polls consumes resources , bad concept of application design.

the best way restore broken connection start using again in try block , re-establish within except..end block. take @ piece of code:

// re-establishes connection every time gets broken procedure respawnconnection; begin   fconnection := tadoconnection.create(nil);   fadodataset := tadodataset.create(nil);   fconnection   begin     connectionstring := adoconnectionstring; // cs here     loginprompt := false;     connected := true;   end;   fadodataset.connection := fconnection; end;  // exposed method ado queries function query(const sql: string): tadodataset; begin   try     result := fadodataset;     fadodataset.commandtext := sql; // if fadodataset not assigned,                                     // line throws excepion...     fadodataset.open;   except     respawnconnection; // ...which handled here...     result := query(sql); // ...and repeats query   end; end; 

don't forget free both objects in formclose event (unless components on form).

in case of heavy , time-consuming queries, may worth offload whole ado stuff separate thread. running such query in separate thread more efficient in terms of user experience. once const sql: string passed thread, thread magic, while user keeps interacting main form.

if you're implement separate thread concept, don't forget call coinitialize , couninizialize because ado based on com (as jerry mentined) , uses appartment-threaded objects.