Active Query Builder doesn't require the preliminary metadata loading. All you need to do at the beginning of your work with the component is to initialize the Database Schema Tree using the QueryBuilder.InitializeDatabaseSchemaTree method.
- Structure of the Metadata Container
- Metadata loading process
- Methods and events of the Metadata Container
- Saving and loading content of Metadata Container to XML
Metadata Container is a tree-like structure that allows for reflection of any modern database server schema sctructure without any distortion. Metadata Container acts as a cache of the underlying server's metadata catalog, and it uses the same naming rules as the underlying server.
There are three types of elements of this structure: namespaces, objects and sub-object items. Namespace nodes reflect object grouping in the database server. They can represent linked servers, databases, schemas and packages. Objects can be tables, views, synonyms, stored procedures or functions (only those procedures and functions that return dataset and can be used as data sources in the SELECT queries). Currently the component deals with three types of sub-object items: fields, parameters and foreign keys, as these types of items are needed by the component to build queries and to represent them visually.
MetadataContainer is the root node of the tree. It stores the MetadataLoadingOptions set of properties, has the OfflineMode property, provides methods to save and load metadata to XML format and events to handle items loading.
MetadataItem is the base type for all metadata items. The following classes are derived from it: MetadataContainer, MetadataNamespace, MetadataObject, MetadataField, MetadataParameter and MetadataForeignKey. The exact type of MetadataNamespace and MetadataObject is determined by the Type property.
Metadata Container loads objects from the current database (according to the connection settings) by default and hides objects from other databases and linked servers. If you want to show all available databases as well as from linked servers (if any), you can set the QueryBuilder.MetadataLoadingOptions.LoadDefaultDatabaseOnly property to false.
If you want to show metadata from specific databases or linked servers only or you want to limit the schemas visible to the end-user, you can instruct the component to do this using a few simple calls that described in this article.
Fine-tuning adjustment of the object's visibility can be achieved by means of the Metadata Structure or Metadata Filters. Also you can add the necessary objects programmatically.
For MS SQL Server, the default structure of Metadata Container can look as follows.
Metadata Container (root) `-- Database (default) |-- schema1 | `-- database objects `-- schema2 `--
For MS SQL Server with the LoadDefaultDatabaseOnly property set to false, the structure can look as follows.
Metadata Container (root) |-- Linked Server (if any) | `-- Database(s) | `-- schema(s) | `-- database objects |-- Database (default) | |-- schema1 | | `-- database objects | `-- schema2 | `-- database objects `-- Database(s) |-- schema1 | `-- database objects `-- schema2 `-- database objects
For MS Access that does not suppport any namespaces, it will look as follows.
Metadata Container (root) `-- database objects
The algorithm of filling child nodes of Metadata Container root node is the following:
- If server supports linked servers - trying to find linked servers - adding them to the Metadata Container.
- If server supports multiple databases - retrieving the list of databases - adding them to the Metadata Container.
- If server doesn't support databases, but supports schemas - retrieving the list of schemas - adding them to the Metadata Container.
- If server doesn't support databases and schemas - retrieving the list of database objects - adding them to the Metadata Container.
Metadata Container loads metadata from the database by demand. This happens when the user expands a node of the Database Schema Tree, when database object is added to the query and when the component associates objects in the parsed query with objects in the database. Learn more: Possible reasons for delays on loading metadata.
Metadata Container (as well as any other metadata item) can be forced to load all child items with the LoadAll method. It has the withFields parameter that determines whether to load fields or not as loading fields may take a long time in case of large database schema.
MetadataItem holds the list of child item within the Items property of the MetadataList class. It has the necessary methods (Find*) to find objects and to load them if they aren't loaded yet in the child items hierarchy. If you want to make searching over the loaded items only, you can get the list of loaded items of specific type using the Items.Find*, Items.GetItems and Items.GetItemsRecursive methods. Read more about them in this article.
The ItemMetadataLoading and ItemMetadataLoaded events of the MetadataContainer allows to perform pre- and post-processing when child metadata items are requested to load from the database. For example, you can override loading of fields or foreign keys for database objects or change their properties when loading is finished. You can find sample handlers of these events here.
Saving the content of the Metadata Container to XML file or string allows for building queries without the necessity to be connected to the database directly. Call the LoadAll and ExportToXML methods of the MetadataContainer object to save the content of Metadata Container in full. The LoadAll method has the "withFields" Boolean parameter which determines the necessity of database objects fields load.
MetadataContainer.LoadAll(<true or false>); // with or without fields MetadataContainer.ExportToXML(<filename or stream>); // to file or to stream
To load metadata back from XML file or string use the MetadataContainer.ImportFromXML method.
If you have already prepared XML file with metadata for the previous version, you can convert it into a new format by means of the command-line utility "XmlConverter.exe" that is shipped with the component.
Customer support service by UserEcho