Is it possible to using Data Module without .DFM?

Question

I offloaded all ADO hood in a separate Data Module, so a single module can be referred by several applications. All my applications basically need only two worker methonds to access data:

AdoQuery delivers a result set in a form of TADODataSet.
AdoExecute performs simple update/delete queries without fetching any results.

Here is the class structure:

type
  TMyDataModule = class(TDataModule)
    procedure DataModuleCreate(Sender: TObject);
    procedure DataModuleDestroy(Sender: TObject);
  private
    procedure pvtAdoConnect;
    procedure pvtAdoExecute(const sql: string);
    function pvtAdoQuery(const sql: string): TADODataSet;
  public
    AdoConnection: TADOConnection;
  end;

Then I added two publicly exposed wrappers to class methods. I used that to avoid long class references in the calls:

function AdoQuery(const sql: string): TADODataSet;
procedure AdoExecute(const sql: string);

implementation

function AdoQuery(const sql: string): TADODataSet;
begin
  Result := MyDataModule.pvtAdoQuery(sql);
end;

Above are the worker function which I call from within all my forms.

AdoConnect runs only once on DataModuleCreate event. TDatModule derived from TPersistent, which allows to persist the single instance of connection throughout a runtime.

The only thing that annoys me so far - is a useless .DFM which I don't need at all.
Is there any option to get rid of it?

Answer 1

I would handle this type of thing in one of two ways, with interfaces or with inheritance. I prefer not to expose classes to the outside world in these cases. The second one could almost be called an interface without interfaces :)

Interfaces

This version returns an interface that includes the required methods. The outside world only needs to use the interface. We keep the implementation details private. Our TMyDBClass implements the interface that we have exposed to the outside world and our global function GetDBInterface returns the single instance.

interface

uses
  ADODB;

type
  IMyDBInterface = interface
  ['{2D61FC80-B89E-4265-BB3D-93356BD613FA}']
    function AdoQuery(const sql: string): TADODataSet;
    procedure AdoExecute(const sql: string);
  end;

function GetDBInterface: IMyDBInterface;

implementation

type
  TMyDBClass = class(TInterfacedObject, IMyDBInterface)
  strict private
    FConnection: TADOConnection;
  protected
    procedure AfterConstruction; override;
    procedure BeforeDestruction; override;
  public
    function AdoQuery(const sql: string): TADODataSet;
    procedure AdoExecute(const sql: string);
  end;

var
  FMyDBInterface: IMyDBInterface;

procedure TMyDBClass.AdoExecute(const sql: string);
begin
  // ...
end;

function TMyDBClass.AdoQuery(const sql: string): TADODataSet;
begin
  // ...
end;

procedure TMyDBClass.AfterConstruction;
begin
  inherited;
  FConnection := TADOConnection.Create(nil);
end;

procedure TMyDBClass.BeforeDestruction;
begin
  FConnection.Free;
  inherited;
end;

// Our global function

function GetDBInterface: IMyDBInterface;
begin
  if not Assigned(FMyDBInterface) then
    FMyDBInterface := TMyDBClass.Create;
  Result := FMyDBInterface;
end;

initialization

finalization
  FMyDBInterface := nil;
end.

Inheritance

This version uses a base class that has the required methods. This is a bit easier for people to deal with because it excludes the interface which can be complex to people starting out. Again we hide the implementation details from the user and only expose a shell of a class that includes the two methods we want people to access. The implementation of these methods is performed by a class in the implementation that inherits from the exposed class. We also have a global function that returns the instance of this class. The big advantage that the interface approach has over this approach is that the user of this object can't free the object by accident.

interface

uses
  ADODB;

type
  TMyDBClass = class(TObject)
  public
    function AdoQuery(const sql: string): TADODataSet; virtual; abstract;
    procedure AdoExecute(const sql: string); virtual; abstract;
  end;

function GetDBClass: TMyDBClass;

implementation

type
  TMyDBClassImplementation = class(TMyDBClass)
  strict private
    FConnection: TADOConnection;
  protected
    procedure AfterConstruction; override;
    procedure BeforeDestruction; override;
  public
    function AdoQuery(const sql: string): TADODataSet; override;
    procedure AdoExecute(const sql: string); override;
  end;

var
  FMyDBClass: TMyDBClassImplementation;

procedure TMyDBClassImplementation.AdoExecute(const sql: string);
begin
  inherited;
  // ...
end;

function TMyDBClassImplementation.AdoQuery(const sql: string): TADODataSet;
begin
  inherited;
  // ...
end;

procedure TMyDBClassImplementation.AfterConstruction;
begin
  inherited;
  FConnection := TADOConnection.Create(nil);
end;

procedure TMyDBClassImplementation.BeforeDestruction;
begin
  FConnection.Free;
  inherited;
end;

// Our global function

function GetDBClass: TMyDBClass;
begin
  if not Assigned(FMyDBClass) then
    FMyDBClass := TMyDBClassImplementation.Create;
  Result := FMyDBClass;
end;

initialization
  FMyDBClass := nil;
finalization
  FMyDBClass.Free;
end.

Usage

Usage of these are really easy.

implementation

uses
  MyDBAccess; // The name of the unit including the code

procedure TMyMainForm.DoSomething;
var
  myDataSet: TADODataSet;
begin
  myDataSet := GetDBInterface.AdoQuery('SELECT * FROM MyTable');
  ...
  // Or, for the class version
  myDataSet := GetDBClass.AdoQuery('SELECT * FROM MyTable');
  ...
end;

Answer 2

If you don't have any design-time non-visual components dropped onto your data module, and don't plan to ever do so, then you shouldn't need a data module at all. The whole purpose is for design-time components, and other implementations such as a Web Module or even a Windows Service Application. But not for wrapping pure code without design-time components.

Also, as mentioned in the comments, don't be confused about the meaning of TPersistent. This class is used entirely differently, and can be integrated into the IDE Object Inspector (as sub-properties within a component).

So the ideal thing for you to do is encapsulate everything in just a single class. For your purpose, a database connection...

type
  TMyData = class(TObject)
  private
    FConnection: TADOConnection;
  public
    constructor Create;
    destructor Destroy; override;
    procedure pvtAdoConnect;
    procedure pvtAdoExecute(const sql: string);
    function pvtAdoQuery(const sql: string): TADODataSet;
    ...
  end;

implementation

{ TMyData }

constructor TMyData.Create;
begin
  FConnection:= TADOConnection.Create(nil);
end;

destructor TMyData.Destroy;
begin
  FConnection.Connected:= False;
  FConnection.Free;
  inherited;
end;

As for the interpretation of being "persistent", you can create/destroy an instance of it in many ways. For example, you could use a unit's initialization and finalization sections (requiring CoInitialize) or you can have your main form initialize a global instance upon creation.

One common way of doing so is to add...

interface

function MyData: TMyData;

implementation

var
  _MyData: TMyData;

function MyData: TMyData;
begin
  if not Assigned(_MyData) then
    _MyData:= TMyData.Create;
  Result:= _MyData;
end;

initialization
  _MyData:= nil;
finalization
  _MyData.Free;
end.

The first time you call MyData from anywhere, a new global instance will be instantiated. Then, every further time it re-uses the same instance. This also solves the need of ActiveX and CoInitialize etc. because at this point, COM is expected to already be instantiated (which is required for ADO).

Usage of this unit would be extremely simple - use include it in the uses anywhere, and access its instance through the MyData function.

Notes

You should get out of the habit of global variables. This is asking for trouble down the road when trying to do later work. The example above shows how to accommodate for a global object instance. All other variables should be self-contained within that object, or in general one of scope / relevance. The whole control of your TADOConnection should be within here, including connecting/disconnecting, exception handling, assigning the connection string.

Answer 3

In case you might be interested in an alternative without DataModules alltogether, have a look at this: https://github.com/stijnsanders/xxm/blob/master/Delphi/demo2/03%20Data%20ADO/xxmData.pas

Queries are stored in a single .sql file, which is handy to edit it in specific SQL editors or workbenches. Queries are separated with a line with --"QueryName", and loaded in a query-store on start-up. Assuming you query for smaller recordsets most of the time, the best lock and open style is read-only and static, which offers the best possible performance and least load on the database server. Getting field values uses the Collect call which also offers a little performance gain.