One of the beautiful things about using ECO is the fact that the database and its table structure are abstracted away. There is no need to be thinking in both the “object” world and the “relational database” world. You can focus purely on your business objects and their relationships with each other, and forget about how they are persisted to storage.
One of the regular questions on the newsgroup is about how to report in an ECO application. Traditionally all report writing was done on the persistent data, which would mean having to understand how your objects are persisted and having to navigate the tables and make many tedious joins to view the data.
Thankfully, many report tools in the .NET world understand that not all reporting is going to be on the persistent data. Many .NET report tools allow you to use a collection of objects as the data source for reporting. This is done by binding to a list of objects that implements one of the following .NET interfaces: IList: ICollection or IEnumerable. These are simply structures that implement methods that allow them to be navigated through .NET reflection, then enables the objects within the list to be accessed.
In ECO, both an ExpressionHandle and a OCLPSHandle implement IList, meaning they can be used as the data source for a report tool.
In my Ski Hire application, I have used DevExpress’s XtraReports to report on my ECO objects. I will show you how easy this is using XtraReports. Any report tool that recognizes a collection that implements IList as a data source can be used instead of XtraReports.
Creating the Model
Firstly, let’s create a simple ECO model. Using Delphi 2006, go “File”, “New”, “Other”, select “Delphi for .NET Projects”, “ECO WinForms Application”. Call the new project “TimeSheet”.
Click the Model View tab, navigate down to the Package_1 folder, the select the Package_1 icon under the folder, right click and select “Open Diagram”
Using the tool palette, drag 3 classes, name them and add the attributes as shown in the diagram. Create 3 associations and set the cardinality and name the ends as in the diagram.
The model uses 2 derived attributes (a calculated, non-persistent attribute), Staff.FullName and Project.TotalMinutes. The images below show the OCL statements for these derived attributes.
Persisting the Objects
Let’s create a database to persist our objects to. If you are using MS SQL Server or MSDE:
open a command prompt, click “start”, “run” then type “cmd” and enter.
type osql /Usa –Psa (substitute “sa” with your username and password)
type create database TimeSheetDB
type go to execute the create database statement
type exit to leave the osql command line
type exit again to leave the Windows command line
To persist our model, in the Project Manager, select TimeSheetEcoSpace.pas, then click on the design tab. Next click on the Data Explorer tab, under BDP, click MSSQL, right click and select “Add New Connection”. Give the connection the name “TimeSheet” and click OK. Expand the MSSQL node, click on TimeSheet, then right click and select “Modify Connection”. Change the Database property to “TimeSheetDB”, set both the username and password properties (in my example both are “sa”). You may have to set “OSAuthentication” to true if MSSQL was configured that way. Click the “Test” button to ensure your connection is correct. Now simply drag the “TimeSheet” connection from the Data Explorer tab onto the EcoSpace design surface.
We need another component to handle the object – relational mapping for use. It is called the PersistenceMapperBDP. Click on the title bar of the Tool Palette, type “Pe” and you will see the list of components filtered so that a selection of PersistenceMappers are visible. Click and drag the PersistenceMapperBDP onto the EcoSpace design surface. Set the Connection property to BDPConnection1, and set the SqlDatabaseConfig property by clicking “SQL Server Setup” in the grey panel below the main object inspector panel.
The next step is to generate the schema in the database. To do this click the “Generate Schema” toolbar button at the base of the EcoSpace designer. It is the 4th button along.
Now our model has been created and persistence has been configured. Let’s create a basic user interface so we can enter some data. Because the main goal of this article is look at using a report tool with ECO, we won’t spend much time on the interface.
Creating Some Objects
We need some objects to report on. Rather than spend a lot of time creating a GUI, we will simply add the objects when the application starts (but only if the list of work objects is empty).
In the project manager, select WinForm.pas, slect design view, in the Tool Palette , select an ExpressionHandle (click on the text “Tool Palette”, which makes it appear in a bold font, then type “Ex”) and drag into onto the WinForm. Set its name property to “ehWork”, it’s RootHandle to “rhRoot” and it’s Expression to “Work.allInstances”.
Switch to code view of the WinForm.pas class, and locate the constructor code. Add the following lines to the bottom of the constructor code:
if (ehWork.Element.GetAsCollection.Count = 0) then
CreateSampleObjects;
The first line is checking to see how many objects are in the ExpressionHandle ehWork. If there are none, we will add some, as well as some projects and staff.
Add a new private procedure called “CreateSampleObjects”. Here is the code to create all of the sample objects…
procedure TWinForm.CreateSampleObjects;
var
Mal, Tim, Peter, Dick: Staff;
EcoWhitePaper, EcoAspDotNetExample: Project;
aWork: Work;
begin
{ Create 4 staff members. }
Mal := Staff.Create(EcoSpace);
Mal.FirstName := 'Malcolm';
Mal.LastName := 'Groves';
Tim := Staff.Create(EcoSpace);
Tim.FirstName := 'Tim';
Tim.LastName := 'Jarvis';
Peter := Staff.Create(EcoSpace);
Peter.FirstName := 'Peter';
Peter.LastName := 'Morris';
Dick := Staff.Create(EcoSpace);
Dick.FirstName := 'Dick';
Dick.LastName := 'Walker';
{ Create 2 projects. }
EcoWhitePaper := Project.Create(EcoSpace);
EcoWhitePaper.Name := 'Eco White Paper';
EcoWhitePaper.Manager := Dick;
EcoAspDotNetExample := Project.Create(EcoSpace);
EcoAspDotNetExample.Name := 'Eco ASP.NET Example';
EcoAspDotNetExample.Manager := Peter;
{ Create some work objects. }
aWork := Work.Create(EcoSpace);
aWork.StartTime := DateTime.Create(2005, 12, 19, 20, 10, 0);
aWork.MinutesWorked := 120;
aWork.Staff := Dick;
aWork.Project := EcoWhitePaper;
aWork := Work.Create(EcoSpace);
aWork.StartTime := DateTime.Create(2005, 12, 20, 13, 05, 0);
aWork.MinutesWorked := 155;
aWork.Staff := Mal;
aWork.Project := EcoWhitePaper;
aWork := Work.Create(EcoSpace);
aWork.StartTime := DateTime.Create(2005, 12, 21, 9, 35, 0);
aWork.MinutesWorked := 165;
aWork.Staff := Dick;
aWork.Project := EcoWhitePaper;
aWork := Work.Create(EcoSpace);
aWork.StartTime := DateTime.Create(2005, 12, 15, 9, 5, 0);
aWork.MinutesWorked := 145;
aWork.Staff := Peter;
aWork.Project := EcoAspDotNetExample;
aWork := Work.Create(EcoSpace);
aWork.StartTime := DateTime.Create(2005, 12, 15, 14, 20, 0);
aWork.MinutesWorked := 90;
aWork.Staff := Peter;
aWork.Project := EcoAspDotNetExample;
aWork := Work.Create(EcoSpace);
aWork.StartTime := DateTime.Create(2005, 12, 16, 11, 15, 0);
aWork.MinutesWorked := 125;
aWork.Staff := Tim;
aWork.Project := EcoAspDotNetExample;
aWork := Work.Create(EcoSpace);
aWork.StartTime := DateTime.Create(2005, 12, 16, 20, 20, 0);
aWork.MinutesWorked := 160;
aWork.Staff := Mal;
aWork.Project := EcoAspDotNetExample;
{ Persist all of the objects to the database. }
EcoSpace.UpdateDatabase;
MessageBox.Show('Sample Objects Created');
Adding a Report
If you are using DevExpress XtraReports (which is available in the DevExpress component evalutation download from DevExpress and on your Delphi 2006 partner DVD), select File, New, Other, Delphi Projects, Delphi Files, XtraReport V3 Delphi .NET Class. If you are using another report tool, add a new form.
Hooking the Report Form and EcoSpace Together
Now we have a report page, we need to add access to the EcoSpace. To do this we need to add a local variable to the EcoSpace, a new constructor so we can pass a reference to the EcoSpace that was instantiated by the main form, and set the local variable to the passed in reference.
Add these lines to the Implementation uses clause:
TimeSheetEcoSpace,
Borland.Eco.Handles,
Add this constructor signature to the interface section and mark the existing constructor with the “overload” directive:
constructor Create; overload;
constructor Create(aEcoSpace: TTimeSheetEcoSpace); overload;
Add this variable to the private interface section
FEcoSpace: TTimeSheetEcoSpace;
Add this property to the interface public section, below the constructors:
property EcoSpace: TTimeSheetEcoSpace read FEcoSpace;
Add this constructor to the implementation section
constructor TXtraReport1.Create(aEcoSpace: TTimeSheetEcoSpace);
begin
inherited Create;
InitializeComponent;
FEcoSpace := aEcoSpace;
rhRoot.EcoSpace := FEcoSpace;
end;
Normally all of these steps are generated for you when you select a new ECO enabled WinForms unit from the file, new option. However the XtraReports forms have their own inheritance hierarchy, so we need to add the Eco references. These same steps apply to any form that you wish to Eco-enable.
Configuring the Report
We need to add some Eco components to access and select our objects:
Go to design view
Add an Eco ReferenceHandle, set it’s name to “rhRoot”, set it’s EcoSpaceType to “Eco2TestEcoSpace.TEco2TestEcoSpace”, which will be available in the combobox.
Go back to the new constructor and add this line to the bottom:
Add an Eco ExpressionHandle, set it’s RootHandle to “rhRoot”, set it’s name to “ehWork”, set it’s Expression to
“Work.allInstances->orderby(e|e.Project.Name)”.
The “e|e.Project.Name” looks a little odd, it is basically an iterator variable and is a great way of resolving the difference between a class name and an association end name. We want to order by the project name, because we will group the report by project name.
Save and build the project.
Click the report, and set DataSource property to “ehWork”. This will be visible in the combobox.
Now we can add databound labels to the report bands. Firstly we need to add some extra columns to our ExpressionHandle to make some attributes of the related objects visible to the report tool. This is similar to creating a view in a database, as we are creating a virtual object representation.
Click on the ExpressionHandle named ehWork
Click the columns property, and click the button to enter the “AbstractColumn Collection Editor”
Click the “Add” button, name the new column “Fullname” and in the expression property add “self.Staff.FullName”.
Click the “Add” button again, name the new column “ProjectName” and in the expression property add “self.Project.Name”.
Click the “Add” button again, name the new column “Manager” and in the expression property add “self.Project.Manager.FullName”.
Click the “Add” button again, name the new column “TotalMinutes” and in the expression property add “self.Project.TotalMinutes”.
Now we can use all of those object properties in the report design surface. The fields can either be dragged and dropped onto a band, or a databound label dragged and dropped and then bound to the data. In the case of XtraReports, an XRLabel can be dragged onto the report.
We will add a group header band to the report, so right click on the detail band, select “Insert Band” then select “Group Header”. Now click the GroupHeader1 on the report and in the object inspector select the “GroupFields” property and click the button to view the “GroupFields Collection Editor”. Click “Add” and in the “FieldName” property, select “ProjectName” from the combobox.
Add a XRLabel to the GroupHeader1 band, in the object inspector, expand the “databindings” property, expand “text” and select “binding”. Now select “Project” which will put the value “ehWork – ProjectName” into the object inspector. Also add labels for “Manager” and “TotalMinutes” to GroupHeader1 and set their bindings.
In the detail band add labels for “Fullname”, “StartTime” and “MinutesWorked”.
With a few extra labels and lines for presentation our report is finished. The finished design should look like this:
Accessing the Report
To be able to view the report at runtime, add a button to the WinForm.pas design surface, name it “btnViewReport”, set the text to “View Report”.
Add “XtraReport1”, to the uses clause of WinForm.pas
Double click the button and add the following code:
var
aReport: TXtraReport1;
begin
aReport := TXtraReport1.create(EcoSpace);
aReport.ShowPreview;
end;
The Finished Product
Now run the application, click the button and we have a basic report!
