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;

???

score 2 · Accepted Answer · answered Jul 07 '11 at 11:30

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');

score 1 · Answer 2 · answered Jul 07 '11 at 11:34

1

If I cannot open the database I terminate the application - not much you can do without database access unless you specifically build an architecture that handles this.

Other than this, just let the default application error handler handle the error, since it would be pretty unexpected anyway.

answered Jul 07 '11 at 11:34

Misha

1,816
1
13
16

Maybe the user wish to access the "settings" dialog to change the database connection parameters? – Jul 07 '11 at 12:46
@ldsandon, in my experience, most applications setup the database connection once and basically never change it. I only develop corporate-type software so this might be different for other types of software. – Misha Jul 07 '11 at 14:45
1

Well, even some corporation sometimes may move a database to a different server :) – Jul 07 '11 at 14:54

database connection using "try finally "

2 Answers2