The Alternate Names feature for both database objects and their fields lets completely substitute real names of objects for clearer descriptive names (aliases). User-friendly names make database schema easier to understand by end-users. The "complete substitution" means the replacement of objects names in both user interface, visible SQL expressions as well as in the SQL query text which also can be edited by end-users. Active Query Builder lets assign an alternate name to every object and field in the Metadata Container. You can get the SQL query text either with real or alternate names at any time:
- with real names to execute against a database server,
- with alternate names to display and let edit it by end-users.
Some customers even use this feature for the database schema refactoring: they assign existing queries to the component and get queries with new names of fields and objects in the result!
Component Setup (.NET Edition)
- The QueryBuilder.SQLFormattingOptions.UseAltNames property prescribes displaying of alternate names in the component's UI: database schema view, design pane, query columns list, etc. and determines the usage of alternate names in the SQL text returned by the QueryBuilder.FormattedSQL property.
- The QueryBuilder.SQLGenerationOptions.UseAltNames property determines the usage of alternate names in the SQL text returned by the QueryBuilder.SQL property.
Optional: instruct the component to assign aliases according to alternate names in order to get friendly column names in result dataset: QueryBuilder.BehaviorOptions.CreateColumnAliasesFromAltNames.
Additional: define additional sets of SQL formatting and generation options to get different versions of SQL text by creating new instances of the SQLFormattingOptions class.
Component Setup (VCL Edition)
Setting the VCL Edition up is similar to the setup of the .NET Edition, but component names are different: the analogue of SQLGenerationOptions and SQLFormattingOptions components is TacSQLBuilderPlainText. You must have two TacSQLBuilderPlainText components: one with the UseAltNames set to false to execute queries against the database server and another with the UseAltNames set to true to display query text to end-users.
Defining alternate names
Alternate names can be assigned in several ways.
Via the "alt_name" tag of fields and objects in the saved metadata XML file. You can use the Metadata Editor tool (as a standalone utility or by calling it from code) to create and edit metadata XML files.
Adding an object or field to the Metadata Container by code by assigning it to the AltName property.
When an object is added to the Metadata Container by handling the appropriate events. For example, you can use object descriptions loaded from the database as alternate names.
VCL and Java Editions: Use the QueryBuilder.MetadataContainer MetadataObjectLoaded (for objects) and ObjectMetadataLoaded (for fields) events during their load to the Metadata Container.
All .NET Editions: Use the QueryBuilder.MetadataContainer.ItemMetadataLoaded to change properties of newly added child items of the given item. See this event handler sample here: Adding fields and changing objects loaded in the Metadata Container.