database connection using "try finally "

Question

Can someone enlighten me on handling the database connection (and errors) using try finally ? What would be the best practice ? Seen various styles but I wonder what would be the best approach. Should opening of the tables be put in TRY block or just the main connection string ? Since I usually put my database (absolute database,access..) in my exe folder I was wondering about the best approach on this... Or first check for file like ...

if (FileExists(sDatabasePath)) then begin
    ADOConnection1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+sDatabasePath+';Persist Security Info=False';
    try
        ADOConnection1.Connected:=True;
        ADOTable1.Open;
    except
        ShowMessage ('cant access the database !');
    end;
end;

???

Answer 1

Comments:

• Never swallow exceptions, like you essentially do in your ShowMessage case. The code calling your procedure would have no way of knowing something went wrong. Only handle errors if you can fix them, or let them bubble-up the application error handler, where they'll be displayed for the user.
• Depending on how your code works, you might want to protect the connection to the database with a try-finally so you're disconnected once the job is done. I don't do that, I usually keep the connection open for the life of the application.
• Depending on what you do with the ADOTable1 you might want to make sure it gets closed once you're done using it with an other try-finally block. I usually do that because I don't use Db aware GUI controls and I'm a control-freak. I also handle the transaction manually (start transaction / commit / rollback)
• Don't ignore errors. if your database doesn't exist (ie: FileExists() returns false), code calling your procedure doesn't know a thing, nor does the user.

Here's how I'd re-write your code:

if (FileExists(sDatabasePath)) then 
  begin
      ADOConnection1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+sDatabasePath+';Persist Security Info=False';
      ADOConnection1.Connected:=True;
      try
        ADOTable1.Open;
        try
          // Do non-GUI database stuff here.
        finally ADOTabel1.Close;
        end;
      finally ADOConnection1.Connected := False;
      end;
  end
else
  raise Exception.Create('Database file not found');