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

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

Contents

• Why one may want to modify the content of the Metadata Container?
• Metadata Container Editor
• How to add new objects to an empty Metadata Container?
• How to add new objects to the already filled Metadata Container?

Related articles

• Methods to load, search, and remove objects in the Metadata Container
• Using events to add, modify and remove objects being loaded to the Metadata Container

Why one may want to modify the content of the 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 when added to the Design Pane.

 When it's not sensible to modify the Metadata Container?

It's incorrect to rearrange objects, having placed them in databases, packages, and schemas irrelevant to real ones. In the 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 in 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 quickly 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 to match names being used in the query text 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 the 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 the 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 to 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 in the root of the Metadata Container:

MetadataObject table = queryBuilder1.MetadataContainer.AddTable("Customers");

To add case-sensitive object names to the Metadata Container, you must quote them according to the quotation rules of your database server.

Note that it's 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 the 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 the root of the 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.