Defining a new Delphi Class with built-in Connection

Question

Using Delphi XE2, I'm wondering if it would be usable to define a base object something like below. Class is prone to be used in multiple projects.

I've chosen TSQLConnection as my starting point - since it's using the dbxpress framework and should be able to connect to a wide range of databases and other. But if a better solution exists - please feel free to comment on this.

TMyObject = class
private
  fConnection : TSQLConnection;
public  
  procedure SetupConnection(aConn : TSQLConnection); 
  property Connection : TSQLConnection read fConnection write fConnection;
  ... // a lot of other properties and methods ...
end;

procedure TMyObject.SetupConnection(aConn : TSQLConnection);
begin
  fConnection := aConn; // Should we clone here? ... or is a reuse ok?
end;

This way it would be possible to use a connection from any of my inherited classes.

Questions:

1. Is this a good design? (from a general point of view)
2. Is the use of TSQLConnection OK, if we have like 200 inherited instances?
3. Making the assignment of the Connection - is it ok to re-use from the datamodule?
4. Perhaps the SQLConnection was better located in an Interface?

Intentions

My intention is to create a "base class structure" for all my classes (in all my projects), and make it easier for co-workers to develop new applications, based on this hierarchy.

Answer 1

If I understand correctly, what you want to do, is define a base "model" like class from which you create "special" classes, correct?

IMHO it's not a bad design, but I would add an overloaded SetupConnection like:

TMyObject = class
private
  fConnection : TSQLConnection;
public  
  procedure SetupConnection(aConn : TSQLConnection); overloaded;
  procedure SetupConnection(const aUserName, aPassword, aServer: string; const aPort: Integer); overloaded;
public
  // also, have the Connect, isConnected, Disconnect + TransactionStart|Rollback|Commit
  // available within the class
public
  property Connection : TSQLConnection read fConnection write fConnection;
  ... // a lot of other properties and methods ...
end;

procedure TMyObject.SetupConnection(aConn : TSQLConnection);
begin
  fConnection := aConn;
end;

procedure TMyObject.SetupConnection(const aUserName, aPassword, aServer: string; const aPort: Integer); 
begin
  fConnection := TSQLConnection.Create( <constructor params here> );
  // based on the params
  // setup connection
end;

Say you have a data module on which you drop a Connection(TSQLConnection or any third party solution) and then your drop 20 tables and 20 queries, all of them using the same connection, is this a good design? well, the guys at Borland thought so, the same for the guys at Embarcadero now, so it seems a pretty good design, however, I would strongly recommend leaving an option to "create" a connection in the class just in case you're going to use a TMyObject descendant in a thread.

Answer 2

Is this the best design?

Best design for what? and the answers of yes, no and maybe are all correct.

What you want to research is design patterns and more specifically Patterns of Enterprise Application Architecture, Active Record, Business Objects, Repository pattern Design etc. Also lookup ORM.