This is the second article about working with the Metadata Container. Read the first article to learn the basics: How database schema information stored in the Metadata Container?.

This article describes, methods, properties and tools to manipulate with objects in the Metadata Container.

Contents

Why one may want to modify the content of Metadata Container?

Below are the reasons why one may want to modify the Metadata Container:
  • to delete unwanted objects from the user's field of view,
  • to add descriptions or alternate names for objects and fields,
  • to add virtual objects and fields.
  • to add missing relationships between objects, so the component will be able to join them automatically on adding to the Design Pane.
What one cannot do by means of metadata container modification:
  • It's incorrect to rearrange objects, having placed them in databases, packages and schemas irrelevant to real ones. In case of such changes the incorrect query text will be generated for these objects. Also it'll be impossible to load the fields list dynamically for displaying on the Design Pane. (But there's a way out: you can use the new MetadataStructure object which has a wide range of possibilities for customization of the Database Schema Tree)

Metadata Container Editor

The Metadata Editor tool lets easily delete and add objects, add descriptions, alternate names, etc. To call the Metadata Editor programmatically use the following code:
QueryBuilder.EditMetadataContainer(
   queryBuilder1.MetadataContainer, 
   queryBuilder1.MetadataStructure, 
   queryBuilder1.MetadataLoadingOptions);
It is also available as a standalone application for editing metadata XML files (download).

How to add new objects to the empty Metadata Container?

All objects in the Metadata Container must reside in appropriate namespaces (databases, schemas and so on). This lets match names that were used in a parsed query to real database objects, and to generate proper object names in the result query text.
For example, the "Northwind.dbo.Orders" table of MS SQL Server should be represented as the "Orders" metadata object, which resides in the "dbo" schema, which in turn resides in the "Northwind" database, which finally resides in Metadata Container root namespace:
MetadataItem database = queryBuilder1.MetadataContainer.AddDatabase("Northwind");
database.Default = true; // set to omit database name in query text
MetadataItem schema = database.AddSchema("dbo");
MetadataObject table = schema.AddTable("Orders");
For those database servers that don't provide support for multiple databases, like Oracle, the "HR.JOBS" table should be represented as the "JOBS" metadata object, which resides in the "HR" schema, which resides in Metadata Container root namespace:
MetadataItem schema = queryBuilder1.MetadataContainer.AddSchema("HR");
MetadataObject table = schema.AddTable("JOBS");

For servers like MySQL which can host multiple databases, but has no schemas, tables should be added directly into database nodes:

MetadataItem database = queryBuilder1.MetadataContainer.AddDatabase("sakila");
MetadataObject table = database.AddTable("films");

For databases like SQLite and MS Access that don't support any namespaces, all objects should be placed directly to the root of Metadata Container:

MetadataObject table = queryBuilder1.MetadataContainer.AddTable("Customers");
To add case sensitive object names to the Metadata Container you should quote them according to the quotation rules of your database server.
Note that its incorrect to rearrange objects, having placed them in databases, packages and schemas irrelevant to real ones for the above reasons. Instead, you can use the new MetadataStructure object which has a wide range of possibilities for customization of the Database Schema Tree.

How to add new objects to already filled Metadata Container?

The following code sample tries to find items that are already loaded into the Metadata Container, load them if necessary, and add them manually if they aren't found/exist. It also adds the found (or the newly added) table to root of Database Schema Tree.
// find or add database item
var northwind = metadataContainer.FindItem("Northwind", MetadataType.Database) ??
    metadataContainer.AddDatabase("Northwind");

// find or add schema item
var dbo = northwind.FindItem("dbo", MetadataType.Schema) ?? 
    northwind.AddSchema("dbo");

// find object or add view
var myView = dbo.FindItem("myView", MetadataType.Objects /* accept any object types: views, tables, synonyms, stored procs */) ??
    dbo.AddObject("myView", MetadataType.View);

// create new MetadataStructureItem for myView
var metadataStructureItem = new MetadataStructureItem {MetadataItem = myView};
queryBuilder1.MetadataStructure.Items.Add(metadataStructureItem);
Note that if a node of Metadata Container is already filled with some object, the component will not try to load other objects of this type on calling the FindItem method, as it thinks that such objects are already loaded.

How to edit objects being loaded from database or already existing in the Metadata Container?

First, you can use the sample above to find or add the needed objects, then edit them according to your needs.Second, you can use the ItemMetadataLoading and ItemMetadataLoaded events of the MetadataContainer. They are fired before and after loading child metadata items for an item (for example, when fields are loaded for a table, tables for a schema, etc). See the usage sample in this article: Adding fields and changing objects loaded in the Metadata Container.

How to load only needed objects from database schema?

To load all objects from the database schema to the Metadata Container (taking into account limitations specified by Metadata Filters), use the MetadataContainer.LoadAll method. The withFields parameter instructs whether to load fields for objects or not. Fields loading is the longest operation (separate query is executed for every database object), so the LoadAll(true) call might work very long on large databases. If you fill the metadata container with objects without fields, the fields will be loaded on demand when you add an object to a query.
Metadata Container is a tree-like structure, that groups objects by namespaces in a tree-like structure (servers, databases, schemas, packages - each level can be omitted if it's not applicable to specific database server). To load only one level of this hierarchy, or objects of specifiŅ type, use the MetadataItem.Items.Load method. For example the MetadataContainer.Items.Load(Database|Schema, true) call will load all database and schema levels of the metadata container.
The other methods allow to iterate items that are already loaded into the MetadataContainer and load them from database.
  • The MetadataList.Find* methods find and return matching metadata items or their indices (without loading them from database).
  • The MetadataList.GetItems and MetadataList.GetItemsRecursive methods return items from the list (and child items lists) without loading them from database.
  • The MetadataItem.Find* methods recursively load items of child hierarchy from database, and rerurn matching ones.
  • The MetadataStructure.GetAllMetadataItems method loads and returns all metadata items that might be visible in the Database Schema Tree (according to the current Metadata Structure).

How to remove objects from Metadata Container?

To remove an object from the Metadata Conainer, you should remove it from the list of parent metadata items:
    metadataItem.Parent.Items.Remove(metadataItem);

Is this article helpful for you?