How to show metadata from several databases or database servers?
Last modified:
The easiest way to display all databases registered on the server, as well as linked servers (MS SQL, Oracle), is to set the QueryBuilder.MetadataLoadingOptions.LoadDefaultDatabaseOnly property to False.
If you only need certain databases, schema or servers to load, you can use the following lines of code before initializing the Database Schema Tree.
To add a database:
MetadataItem database = queryBuilder1.MetadataContainer.AddDatabase("DbName");
To add a linked server and all its databases:
MetadataItem server = queryBuilder1.MetadataContainer.AddServer("ServerName");
To add a database from linked server:
MetadataItem server = queryBuilder1.MetadataContainer.AddServer("ServerName");
MetadataItem database = server.AddDatabase("DbName");
Note that adding a database or a server using this helper method prevents loading of other databases or servers from the parent node. In the same way, you can prevent loading of additional schemas from a database. It's quite simple for Oracle database server which does not support multiple databases:
MetadataItem schema = queryBuilder1.MetadataContainer.AddSchema("SchemaName");
For those database servers that support multiple databases, use the following code:
// load the first level of the Metadata Container which should have the default database
queryBuilder1.MetadataContainer.Items.Load(MetadataType.Database, false);
// get list of loaded databases
IList databases = queryBuilder1.MetadataContainer.Items.GetList(MetadataType.Database);
// find the default database in the list
foreach (MetadataItem database in databases)
{
if (database.Default)
{
MetadataItem schema = database.AddSchema("SchemaName");
}
}
Executing the code snippets above won't load metadata, but only add the root nodes for further loading on demand. After adding the necessary items, you can initialize the Database Schema Tree as usual:
queryBuilder1.InitializeDatabaseSchemaTree();
During the initialization process, the tree will be expanded according to the QueryBuilder.DatabaseSchemaTreeOptions.DefaultExpandLevel property. Note that increasing of this property value will lead to increased initialization time as metadata loading is performed on the expansion of the Database Schema Tree nodes.
Some users want to load metadata from different servers that are not linked to each other. Although it is possible to do this, and the component will be able to build a query using objects from different servers, note that there will be no way to execute such queries until you use a custom query execution engine. If this doesn't stop you, follow the steps below.
- Set the right syntax and metadata providers for the first database server and connect to it.
- Load Metadata by calling the MetadataContainer.LoadAll(true) method. (The withFields parameter set to true instructs to load fields for each database object that may take a long time for the large database schema, so please be patient.)
- Set the right syntax and metadata providers for the next database server and connect to it.
- Add new database (or server) as described above.
- Load Metadata from the database by calling the Metadataitem.LoadAll(true) method, where MetadataItem is the newly added database object.
Repeat steps 3-5 for all following databases and servers from which you want to load metadata. After that, you can save all metadata to the XML file and use it afterward.