Storing your Objects in a Database – Getting Started

If you’ve read the Introduction, you know why we’re here. If you haven’t, it might be worth reading that first because I want to get straight to business in this article.

I’m going to assume you have TMS Aurelius installed. If not, you can grab the trial version here. I’m also assuming you have a database of some sort. I’m going to use Interbase and FireDAC, but Aurelius can work with just about everything so use whatever you like.

Note, if you prefer to watch rather than read, I’ve done a live coding video of what follows that you can watch along with below:

Start up Delphi or C++Builder (I’m using 10.1 Berlin, but this will all work in earlier versions) and create a new VCL application. You can of course do this with FireMonkey on any of the mobile or desktop platforms, but I’m trying to keep the number of new things to a minimum so lets go with something that most people will know. Save the app and call it something like CRM. Next, add a DataModule to the project and save it.

Setting up the Database

Now we need a database. Fire up IBConsole, connect to your Interbase instance and create a new, empty database.

Next, go back into Delphi and in the Data Explorer, right-click on the FireDAC | Interbase node and select Add New Connection. Give it a name (I chose CRM) and then put in the details of the empty database you just created in IBConsole. Click Test Connection just to be sure you’ve got it all correct. Even though the database is empty, you should be able to expand the newly created CRM node in the Data Explorer, which means you’ve connected. You don’t strictly need to add the node to the Data Explorer, but I want to be able to regularly check what has been created for me in the database, and this is a nice, easy way.

Now, select the CRM node you just created in Data Explorer and drag it out onto your datamodule. This will create a TFDConnection component.

While there, lets also drop down a TFDMoniRemoteClientLink component. This is entirely optional, but will let us configure FireDAC to log it’s actions and allow us to see the actual SQL Aurelius is executing for us, so very useful while learning.  Expand its EventKinds property and uncheck them all except ekCmdExecute. Set the Tracing property to True, and back on the TFDConnection component, set the Params | MonitorBy property to mbRemote and set Params| MonitorByInitial to True. Lastly, go into your Delphi or C++Builder bin directory, and run FDMonitor.exe. This is the app that will receive the trace messages from FireDAC.

OK, now that’s done we can get to using Aurelius. The first thing we have to do is give Aurelius some details about our database. In the sourcecode for your datamodule, add the following units to the uses clause:

• Aurelius.Drivers.Interfaces – this unit contains all the interface definitions Aurelius uses for it’s data access
• Aurelius.Drivers.FireDAC – this one provides FireDAC specific implementations of those interfaces
• Aurelius.SQL.Interbase – this one tells Aurelius to generate Interbase-specific SQL

Now we’ve done that, we need to create an instance of a TDatabaseManager. We’re going to need to access this at various points in our app, whenever we want to interact with the database, so I like to set it up as a property of my datamodule. Specifically, I add to my datamodule a private field and a read-only property, both of type TDatabaseManager, like so:

TdmMain = class(TDataModule)
   CrmConnection: TFDConnection;
   FDMoniRemoteClientLink1: TFDMoniRemoteClientLink;
   procedure DataModuleDestroy(Sender: TObject);
 private
   FDBMgr : TDatabaseManager;
   function GetDBMgr: TDatabaseManager;
 public
   property DBMgr: TDatabaseManager read GetDBMgr;
 end;

TDatabaseManager is defined in a unit called Aurelius.Engine.DatabaseManager, so add that to your uses clause as well. Next, implement the GetDBMgr function like this:

function TdmMain.GetDBMgr: TDatabaseManager;
begin
  if not Assigned(FDBMgr) then
    FDBMgr := TDatabaseManager.Create(TFireDACConnectionAdapter.Create(CrmConnection, False));
  Result := FDBMgr;
end;

What’s happening here is that the first time the property is access, we’ll create the TDatabaseManager instance. The constructor of TDatabaseManager expects a parameter of type IDBConnection. This is the interface Aurelius uses to abstract away the different database connection components it can use. We’re using FireDAC, so we create an instance of  TFireDACConnectionAdapter, which implements IDBConnection. We pass the TFDConnection component on our datamodule (in my case, CRMConnection) in as the parameter to the constructor, as well as False, indicating that TFireDACConnectionAdapter should not destroy the FDConnection when it is destroyed.

Like I said, you can create this TDBManager in other ways, but follow along for now until you know what you’re doing.

We’ve create it, so we need to free it. Add an event handler to the DataModule’s OnDestroy event, and call FDBMgr.Free inside it.

The Business Objects

Now, we’re ready to start loading and saving our Business Objects to the database. Well, we would be if we had any, so let’s make some.

Add a new unit to the project, call it whatever you like (mine is called uObjects) and inside it define a TCustomer class that looks like this:

 TCustomer = class
 private
   FName: string;
   FACN: string;
   FID: Integer;
 public
   property ID: Integer read FID write FID;
   property Name: string read FName write FName;
   property ACN: string read FACN write FACN;
 end;

This is going to be the first of our business objects. It’s simple, yes, but we’ll build on it (and add more) as we go. A few things to note about this class:

• it doesn’t have to descend from any particular class. It can of course, but Aurelius doesn’t require it.
• likewise, Aurelius doesn’t require you to implement any particular interface.
• I have an integer property called ID, which is there for Aurelius’s benefit, but this is just what it looks for by default. We’ll look in a later post about how to tell Aurelius to use a different property, a different type (eg. a string or GUID, etc)  and even multiple properties in order to uniquely identify our instance.

This is one of the things I really like about Aurelius. It doesn’t inflict a bunch of requirements on me. I essentially create plain Delphi classes and it works with them.

Now we have our business object, we’d better tell Aurelius we want it to be responsible for loading and saving it. We do this by tagging the class with an Entity attribute, like so:

 [Entity]
 TCustomer = class
 private
   FName: string;
 ...

We also need to tell Aurelius how much say we want in how this class is mapped to the database. Initially we’re going to tell it we don’t want any say, by tagging the class with the AutoMapping attribute. However, in future posts we’ll get more opinionated and start specifying more about how it should be saved and loaded. The nice thing about Aurelius is that this can be a gradual process. Leave it to do its thing and then only stick your nose in when you have specific requirements, rather than some frameworks which force you to specify all the details.

Your class should look like this now:

 [Entity]
 [AutoMapping]
 TCustomer = class
 private
   FName: string;
   FACN: string;
   FID: Integer;
 public
   property ID: Integer read FID write FID;
   property Name: string read FName write FName;
   property ACN: string read FACN write FACN;
 end;

Like I said, we’ll make this richer later on, but this will do for a start.

The User Interface

Now, let’s get our app up and running.

On the form, drop down a button and set its Caption property to Create Database. In its OnClick event, we’re going to tell Aurelius to create the tables, indexes and other database objects necessary to save and load our business objects.

procedure TForm1.Button1Click(Sender: TObject);
begin
  dmMain.DBMgr.BuildDatabase;
end;

dmMain is my dataModule, DBMgr is the TDatabaseManager property we created earlier and we’re calling BuildDatabase. Normally we probably would not have this under a button in our main form. We might instead have it called by a utility in our installation script, but in this case I want to be able to explicitly call it so we can see what it does for us.

Next we need to create some instances of our business objects. Before we do that though, there is an Aurelius object we’re going to want to use repeatedly when interacting with our objects called TObjectManager. So we’re going to create a property for it on our form, like we did with TDatabaseManager on our datamodule. Again, you can do this in different ways, but for now just follow along with me.

TObjectManager is declared in a unit called Aurelius.Engine.ObjectManager, so add that to your Implementation uses clause. Next, define a private field and a read only property on your form, both of type TObjectManager. It should look like this:

 TForm1 = class(TForm)
   Button1: TButton;
   procedure Button1Click(Sender: TObject);
 private
   FObjMgr : TObjectManager;
   function GetObjMgr: TObjectManager;
 public
   property ObjMgr: TObjectManager read GetObjMgr;
 end;

Implement the GetObjMgr in a similar way to the GetDBMgr function earlier:

function TForm1.GetObjMgr: TObjectManager;
begin
  if not Assigned(FObjMgr) then
    FObjMgr := TObjectManager.Create(dmMain.DBMgr.Connection);
  Result := FObjMgr;
end;

Note the constructor of TObjectManager wants an IDBConnection, just like the constructor of TDatabaseManager, so I’m grabbing the same instance I used before by referencing the Connection property of my DBMgr property. As before, we’ll also need to free the ObjectManager, so in the OnDestroy event of your Form, put the following two lines of code:

procedure TForm1.FormDestroy(Sender: TObject);
begin
  ObjMgr.Flush;
  FObjMgr.Free;
end;

Flush tells the ObjectManager to save any pending object changes, and, well, you should know what Free does.

Now that we have an ObjectManager, we can create some business objects. Drop another button on the form and set its Caption to Create Objects. Implement its OnClick event like this:

procedure TForm1.Button2Click(Sender: TObject);
var
  LCustomer : TCustomer;
begin
  LCustomer := TCustomer.Create;
  LCustomer.Name := 'Acme Inc';
  LCustomer.ACN := 'abc123';
  ObjMgr.Save(LCustomer);

  LCustomer := TCustomer.Create;
  LCustomer.Name := 'Code Partners';
  LCustomer.ACN := 'ABC124';
  ObjMgr.Save(LCustomer);
end;

Most of this should not be too surprising. We’re creating an instance of our TCustomer object and putting some values in its properties (ACN stands for Australian Company Number, by the way). The only new part here is that after we’ve created a TCustomer, we pass it to the Save method of our ObjectManager. This will cause Aurelius to generate the necessary SQL to save it into the database. Just for good measure we create a second instance and save it as well.

That’s it. Nice, clean, Delphi object code. No SQL, no queries, just plain old Delphi objects and then a call to Save. Yay for not writing plumbing code!

There are a couple of things worth calling out about this code:

• Note I didn’t set the ID property. That’s Aurelius’s job, but in a later post we’ll look at how you can get involved if you need to.
• Also note I didn’t free the instances I created. Part of having Aurelius manage our business objects for us is that it is responsible for the object lifecycle. When we query for objects it will create them before giving them to us and when save it will delete them when necessary.

We’ll come back later and do more, but for now lets run our app and see what happens.

Assuming you have no compilation or linker errors, your app should start up ok. At this stage we have our database, but it is empty. Click the Create Database button. The cursor should briefly change to a SQL Hourglass. If we have a look in the FDMonitor app, we should be able to see the DDL commands Aurelius generated in order to create the structure it needs to save our TCustomer. If we browse into the CRM node in the Data Explorer, we should also see we now have one table called Customer, which has fields for ID, Name and ACN, along with an Index for our Primary Key. It has also created an Interbase Generator for us that it will use for generating ID field values.

However, our table is empty, so lets fix that. Click the Create Objects button and again the SQL Hourglass cursor should briefly appear. Now right-click on the Customer table in the Data Explorer and select View, you should see records for Acme and Code Partners, the two TCustomer instances we created and saved.

Let’s pause here for a second and think about what we’ve just done. Yes, this is a long article, but if you think about the actual code we’ve written, it’s not that much. However, in that little code, we’ve configured Aurelius to connect to our database, defined our first business object, told Aurelius to create the DB structure it needs to save that business object and lastly we’ve actually created a couple of instances and saved them. All that with remarkably little ceremony and with very nice separation between our business objects, our database and our UI. Our business objects are also nicely testable in isolation from either the UI or the DB.

Aurelius is a very sophisticated framework, with lots of places where you can dive in an change how things are done. However, it shields you from that sophistication until you really want it. This is a hard balance to maintain, and I’m quite impressed with how they’ve done it.

Anyway, on with the example. There’s not much point saving objects if we don’t also look at how we get them back out of the database. Here we’re going to look at two parts:

• Getting an object if you know its unique ID
• Querying for one of more objects that match some criteria

Loading an Object by its ID

Drop a TEdit, a TButton and a TLabel on the form and arrange them like this:

Then, in the TButton’s OnClick event, write the following code:

procedure TForm1.Button4Click(Sender: TObject);
var
  LCustomer : TCustomer;
begin
  LCustomer := ObjMgr.Find<TCustomer>(Edit1.Text);
  if Assigned(LCustomer) then
  begin
    Label1.Caption := LCustomer.Name;
    Label2.Caption := IntToStr(LCustomer.Contacts.Count);
  end
  else
    Label1.Caption := 'Not Found';
end;

As you can see, we’re calling Find on our TObjectManager. Note, Find is a generic method, so we’re telling it that we want it to return a TCustomer, and we’re supplying whatever has been typed into the TEdit as the ID.

Aurelius will generate the necessary SQL to query the database and look for a TCustomer with that ID. If it finds one, it will create an instance and load the data from the database. If it doesn’t find one, it will return null. We’re assigning the return value to a local variable, so we can check if it is assigned and then just referencing the properties of the object if it is.

Searching for Objects based on Criteria

As straightforward as that was, the times when you will just know the ID of an object are probably limited. A more common scenario is where you want to query for your objects based on some criteria, such as all TCustomer’s in a particular region, or those that have a Name property matching a certain string, etc. So let’s have a look at that. I’m actually going to do this two different ways, one using a TList and another using a dataset.

Drop another TEdit and TButton on the form, along with a TListbox, and arrange them like this:

Next, you’ll need to add a few units to the Implementation section’s uses clause: Aurelius.Criteria.Base, Aurelius.Criteria.Linq, and Generics.Collections. The first two give us access to Aurelius’s Query Criteria, and the last lets us define a TObjectList to hold the results.

In the button’s OnClick event, type in the following code:

procedure TForm1.Button5Click(Sender: TObject);
var
  LResults : TObjectList<TCustomer>;
  LCustomer : TCustomer;
begin
  ListBox1.Clear;
  LResults := ObjMgr.Find<TCustomer>
                    .Where(TLinq.Contains('Name', Edit2.Text))
                    .List;
 try
   for LCustomer in LResults do
     ListBox1.Items.Add(LCustomer.Name);
 finally
   LResults.Free;
 end;
end;

Let’s walk through it. We’re defining some local variables: a TObjectList of TCustomer, which will hold the results of our query, and a TCustomer, which we’ll use to iterate over the objectlist. Then, we’re using ObjectManager.Find again, but this time instead of just passing in a value for the ID, calling the Where method which allows us to specify a criteria. In this case we’re using the TLinq class to specify the Contains crtieria passing the property we want to test and the value (in this case the Name property and the value it should contain is whatever has been typed into Edit2). Note you can specify multiple criteria in order to build up more complex queries. After calling Where, we’re then calling the List function in order to get our results back as a list of TCustomer, which we’re then storing in our local variable.

We’re then iterating over the list of TCustomer obejcts using a for..in statement, adding the Name of each one to the listbox. Finally we’re freeing the list. As I mentioned before, Aurelius is responsible for managing the lifecycle of the objects themselves, so we don’t need to free the TCustomer objects in the list. We do however need to free the list itself.

Run the app and try some different strings in the Edit box and see which ones bring back which of our TCustomer objects.

If you want to get back a list of objects and then manipulate them in code, then this is perfect. Also, if you are using LiveBindings to bind from a list of objects to the UI, then this would also be fine. However, if you want to use data-aware controls in a  VCL app to display the list in the UI, getting back a list of Customers is not going to help so much.

Let’s change our example to deal with VCL databinding. Delete the Listbox and drop down a TDBGrid instead. Also drop down a TDataSource and a TAureliusDataset. Connect DBGrid1.DataSoure to DataSource1, and connect DataSource1.Dataset to AureliusDataset1. Change the code in your button OnClick event to look like this:

procedure TForm1.Button5Click(Sender: TObject);
var
  LCriteria : TCriteria;
begin
  LCriteria := ObjMgr.Find<TCustomer>
                     .Where(TLinq.Contains('Name', Edit2.Text));
  AureliusDataset1.Close;
  AureliusDataset1.SetSourceCriteria(LCriteria);
  AureliusDataset1.Open;
end;

We’re still using ObjectManager.Find.Where to define our query criteria, but instead of calling .List to retrieve the results, we’re telling the dataset to use that criteria as it’s source for data.

Running the app now should show the same behaviour as before, but you’ll notice the DBGrid is displaying all the fields, including one it uses for its own purposes called Self. No big problem, if this was a normal Delphi VCL app, how would you control the fields of a dataset? Well, you’d probably set some persistent fields on the dataset itself, right? Here it’s no different. Right-click on the AureliusDataset and bring up the Fields Editor. Create a New Field and set the Name to the name of the property you want to display. Also set the datatype of the property. Now when you run your app again, you should just see the single field displayed in the DBGrid.

As I’ve said a couple of times in this article, there is way more you can do with Aurelius than this, and in future posts I’ll explore the different ways you can start to exercise more control over the persistence of your objects. However, even taking the default AutoMapping settings as we have here, we’ve achieved a lot with very little code. Think about how much more code and configuration would be needed if we were going to do this with standard Delphi data access technologies. Our code would be littered with persistence details, distracting us from the business problem we’re trying to solve. I find Aurelius’ approach much cleaner and simpler.

In the next article, I want to extend our object model a bit further, adding another class and defining a relationship between it and our TCustomer. Then we’ll look at what we need to do to update our app to deal with this.

Remember, Code Partners has an exclusive offer on TMS Aurelius for readers of these posts. Click here to find out more.