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

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 2

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.