Defining a new Delphi Class with built-in Connection

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.

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.

• I totally get your concern about the creation of a connection when used in a thread. That would expose a TSQLConnection directly in my datamodule. So good advice to have an option "inside" that lets it create its own. But that also has some problems. Creating many threads - is creating many connections. And that is not always a good thing - I've experienced. – Bimmer_R Feb 22 '12 at 15:20
• yes sir, but I find that most of the "issues" come from the design, the trouble is that maybe, just maybe, you will create a Connection only when necessary. However, you can also have something like SetConnection(TNotifyEvent) and OnSetConnection(private event) if fconnection was created(remember that using a private flag that you reset accordingly) you free it and set the reference to the newly assigned connection... there are more than 100 ways to skin a cat, that's for sure ((: – user10745 Feb 22 '12 at 15:28
• I've now adapted my source to look somewhat like your suggestion. And so far I'm pleased with the results :-) So this one is going to have to be the answer .... Thanks again. – Bimmer_R Feb 29 '12 at 7:40
• @Bimmer_R I've used a similar design for over 1.5 years for web, it proven very good, however I've used "self-contained" connections, I'm glad it worked, cheers! – user10745 Feb 29 '12 at 8:52

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.

• Yes - Best ... is a strong word here. Probably should have used "Is this a good design?". Nevertheless - thanks for directing my attention to subjects at hand. I'll make an edit on the question to clarify my intentions better. :-) – Bimmer_R Feb 22 '12 at 11:52