One of the powerful features that help to build friendly end-user environment with Active Query Builder is the Alternate Names feature. It was primarily made to display clear names for database objects which real names might be absolutely unclear or just poorly legible to the user. But some of our customers have found unusual ways of using this feature to cope with their tasks.
One of such applications is a procedure that helps to keep user queries up-to-date after performing multiple renamings in the database schema. As you know, an alternate name can be assigned to any item in the Metadata Container, including database, schema or field. The idea was to save database schema before refactoring it, then perform the refactoring and assign the new names as alternate names for appropriate database objects. The rest is simple: load every query to the query builder, read the updated SQL with alternate names, and replace the original query with it.
Recenlty we've got a new idea from a customer. You know that some databases contain self-referred objects (for example, a table that stores a hierarchical structure having the id and parent_id fields), or tables that can play different roles in a query, like table of orders that contains several links to employees table referring to several different employees: initiator, a manager who's completed a deal, etc. When the user adds such tables to the query, he sees a mess of links between them. In the first case, two tables referring to each other (parent_id to id and vice versa); in the second case, the employee table gets linked to each referencing field of the orders table. This happens because Active Query Builder doesn't know which one of them the user wants to get. The customer has a bright idea to add tables to the metadata container several times: one for each role.
For example, a table with hierarchical structure can be added twice with "parent items" and "child items" alternate names. Then the second table refers to the first one, indicating the alternate name in a reference. When the user adds both tables to the query, he gets the following query (and appropriate query diagram):
SELECT
*
FROM
"parent items" p INNER JOIN
"child items" c ON c.parent_id = p.id(you can also alias the field names if you wish) And the programmer gets the SQL with real names for execution:
SELECT * FROM h_structure p INNER JOIN h_structure c ON c.parent_id = p.idWe've made it possible with a small change in the component by allowing to use alternate names in the names of referring objects of foreign key relationships. This change is instantly available in all of the .NET editions, versions 2 and 3.
Please don't hesitate to share your ideas and suggestions with us. It might be hard to find appropriate solutions for some of them, but quite easy for the others. Anyway, you've got nothing to lose.
Comments by Disqus:
We have been using Active Query Builder for over a year and must say that both the product and support have been outstanding!
We chose Active Query Builder due to its flexibility and features, but have been truly pleased by its power and hidden capabilities. ...
In summary Active Query Builder provides excellent components, great support and a very flexible feature set. It has allowed us to provide features to our end users that I did not think would be possible in the first release of our new tools and in a timeframe that was much shorter than planned. I would recommend that anyone dealing with databases in the .Net world should be aware of this component and its capabilities!