Delphi AdoConnection Reconnect

Question

I have just decided to solve the "Connection" problem when a MSSQL Database server is restarted, and the connection is dropped eternally.

The only solution so far has been to restart the program, not always so easy on server far-far away (and the problem must first be detected).

**The code below seems to be working fine, but can a skilled ADO person look deeper into the code and see any errors/problems or improvements needed with this code? **

Type
  TComponentHelper = class helper for TComponent
     Procedure Reconnect(var AdoConn:TAdoConnection; ConnStr:String);
  end;

procedure TComponentHelper.Reconnect(var AdoConn: TAdoConnection; ConnStr: String);
begin
  if Assigned(AdoConn) then begin
    FreeAndNil(AdoConn);
    AdoConn := TAdoConnection.Create(Self);
    AdoConn.ConnectionString := ConnStr;
    AdoConn.LoginPrompt := false;
    SetConnAdoComponent(Self,AdoConn);
    AdoConn.Open;
  end;
end;

procedure SetConnAdoComponent(aSrc:TComponent; var AdoConn:TAdoConnection);
var
  Ctrl : TComponent;
  i    : Integer;
begin
  if (aSrc = Nil) then Exit;
  if (aSrc.ComponentCount <= 0) then Exit;
  for i:=0 to aSrc.ComponentCount-1 do begin
    Ctrl := aSrc.Components[i];
    if (Ctrl is TAdoQuery) then TAdoQuery(Ctrl).Connection := AdoConn;
    if (Ctrl is TAdoTable) then TAdoTable(Ctrl).Connection := AdoConn;
    if (Ctrl is TAdoDataset) then TAdoDataset(Ctrl).Connection := AdoConn;
  end;
end

I Call Reconnect() from the Exception part in a TForm or TDataModule, AdoConn is the name of the TAdoConnection component and the ConnStr is the complete connectionstring used.

Except
  On E:EOleException do begin
    ReConnect(AdoConn,ConnStr);
  end;
  On E:Exception do begin
    ReConnect(AdoConn,ConnStr);
  end;
End;

Answer 1

Instead of destroying the TADOConnection your best option is to replace the internal TADOConnection.ConnectionObject with a new one. e.g.

uses ActiveX, ComObj, ADOInt;

function CreateADOConnectionObject: _Connection;
begin
  OleCheck(CoCreateInstance(CLASS_Connection, nil, CLSCTX_INPROC_SERVER or
    CLSCTX_LOCAL_SERVER, IUnknown, Result));
end;

var
  NewConnectionObject: _Connection;
  ConnectionString: WideString;
begin
  ConnectionString := ADOConnection1.ConnectionString;
  NewConnectionObject := CreateADOConnectionObject;
  NewConnectionObject.ConnectionString := ConnectionString;
  ADOConnection1.Close;
  // set the new connection object
  ADOConnection1.ConnectionObject := NewConnectionObject;
  ADOConnection1.Open;
end;

Setting ADOConnection1.ConnectionObject := NewConnectionObject will destroy the previous internal FConnectionObject and set a new connection object to be used by the TADOConnection object.

Also you need to handle the specific EOleException.ErrorCode (probably E_FAIL) at the time of the exception so that you sure you don't handle other exceptions which has nothing to do with your issue.

I did not try this with your specific scenario (SQL restart). I leave it up to you for testing.

EDIT: Tested with SQL Server 2014 and SQLOLEDB.1. My application connected to the SQL, and after restarting the SQL, I could not reproduce the described behavior "connection is dropped eternally". a Close/Open did the job, and the client re-connected.