What are the best practices for database development with Delphi?

Question

1. How can I use the RAD way productively (reusing code). Any samples, existing libraries, basic crud generators?
2. How can I design the OOP way? Which design patterns to use for connection, abstracting different engines/db access layers (bde-dbexpress-ado), basic CRUD operations.

Answer 1

Be very careful if you use the „put every DB objects into one big data module” (or "few big datamodules" in huge applications) approach. This can make your project having data module so big, that you will have to use HD monitor to see all TXDataset on this datamodule
Bottom line: switch to using specialized classes for business logic instead of big global data modules. Use global data modules with logic ONLY in very small projects.

Answer 2

Well, I strongly suggest you to use Actions (TActionList) when designing your user interface. There are many predefined actions including Next/Prev/Insert/Delete/Edit/Update operations that can be performed on datasets, so it is a good practice to use these actions and link them to buttons/menus on your forms. This prevents repeated code for UI logic.

There is no need for a CRUD generator for Delphi!! Add TDataSource, TDBGrid and TActionList to a form, add predefined data source actions to the action list, link those actions to buttons or menus, and you are done!

Answer 3

Use VFI (visual form inheritance). Design a standart DB form. For example, empty DataSet, DataSource, a PageControl consisting of 2 sheets. First will be empty, later on you'll add edit controls to manipulate data at child forms. Add DBGrid to the second sheet. Beware, this isn't the OOP way though, but it's easy and fast.

Answer 4

I have my own Delphi/MySQL framework that lets me add 'new screens' very rapidly. I won't share it, but I can describe the approach I take:

I use a tabbed interface with a TFrame based hierarchy. I create a tab and link a TFrame into it.

I take care of all the crud plumbing, and concurrency controls using a standard mysql stored procedure implementation. CustomerSEL, CustomerGET, CustomerUPD, CustomerDEL, etc...

My main form essentially contains navbar panel and a panel containing TPageControl

An example of the classes in my hierarchy

TFrame TMFrame - my derivation, with interface implementations capturing OnShow, OnHide, and some other particulars

--TWebBrowserFrame --TDataAwareFrame --TObjectEditFrame --TCustomerEditFrame --TOrderEditFrame etc... --TObjectListFrame --TCustomerListFrame

etc...

and some dialogs..

TDialog TMDialog --TDataAwareDialog --TObjectEditDialog -- TContactEditDialog etc.. --TObjectSelectDialog --TContactSelectDialog

etc...

When I add a new object to manage, it could be a new attribute of customers, let's say we want to track which vehicles a customer owns.

create table CustomerVehicles I run my special sproc generator that creates my SEL, GET, UPD, DEL test those...

Derive from the base classes I mentioned above, drop some controls. Add a tab to the TCustomerEdit.

Delphi has always the Dataset as the abstract layer, expose this to your GUI via DataSources. Add the dataset to the customer data module, and "register it". My own custom function in my derived datamodule class, TMDataModule

Security control is similarly taken care of in the framework.. I 'Register' components that require a security flag to be visible or enabled.

I can usually add a new object, build the sprocs, add the maintenance screens within an hour.

Of course, that is usually just the start, usually when you add something, you use it for more than tracking. If this a garage application, we want to add the vehicle the customer brought into the garage, id it so we can track the history. But even so, it is fast.

I have tried subcontracting to younger guys using 'newer development tools', and they never seem to believe me when I say I can do this all ten times faster with Delphi! I can do in two hours bug-free what it seems to take them two days and they still have bugs...

DO - Be careful planning your VFI! As someone mentioned, if you want to change the name of a component on one of your parent classes, be prepared for trouble. You will need to open and 'edit' each child in the hierarchy, even if you clean DCU you can still have some DFM hell. I can assure you in 2006 this is still a problem.

DON'T create one monster datamodule

DO take your time in the upfront design, refactoring after you have created a ton of dependents can be a fun challenge, but a nightmare when you have to get something new working quickly!

Answer 5

I would take a look at Data Abstract from Remobjects.

Answer 6

For large applications, I use the tiopf object persistance framework. That lets me deal with objects rather than datasets and swap databases easily. Most of my business logic moves into the business object model (BOM) and my forms are pretty dumb. tiopf has a few ways to connect the BOM to forms; persistance aware controls, Ttidataset for data-aware controls and Mogel Gui Mediator classes for connecting to normal controls.

For small and quick apps, I just use data modules and database components. The main things to remember are:

• Put as much code in the data modules (and as little in the forms) as possible.
• Do multiple data modules broken down by functionality eg the email module, the income module, the invoicing module...
• Test, test, test