I use SQL server for data storage. How can I organize
data access with SQL queries rather than direct tables looping?
You may follow in a number of ways. Below you will find two of them:
1. Create TQuery component for each of SQL statement you use in
template and return it by alias from OnGetTable event handler
as it was implemented in DelphiBDE and Builder samples.
This requires a bit of code but makes your program rigid for templates
development because of each new query requires program modification
2. Another way I would recommend is to create program-independent query
subsystem. The steps are:
- Create "variables" subsystem by inplementing
SET('VariableName', Value); tag in OnTag
event handler. SET registers VariableName into
internal collection and/or assign Value to it.
- At the end of OnTag handler implement searching through
collection and return values if requested variable
was found.
- In a handler of OnGetTable event search for requested
table name in variables collection. If such a variable is found,
dynamically create TQuery object and assign variable's value to
TQuery.SQL property. That's all
Working with a queries in a such manner give you a lot of attractive
features. First, you may use variables in your templates. Then, you may
access SQL database using this scenario ("hidden" text is grayed):
...
SET('CID',10);
...
Customer id: CID;
SET('Customer',strCat('SELECT * FROM customers
WHERE CustomerID=',CID));
Customer name: Customer.Name;
Customer address: Customer.Address;
...