HomeActive Query Builder - Metadata handling and filtrationWorking with metadata in .NET versionAdding objects to the Metadata Container programmatically

1.4. Adding objects to the Metadata Container programmatically

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 executable file for editing metadata XML files.

Programmatic modification of Metadata Container

To match names that were used in a parsed query to the real database objects, and to generate proper object names in the result query text, all metadata objects must reside in appropriate namespaces (databases, schemas and so on).

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");
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 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.

If you want to omit unnecessary database and schema prefixes in the query text, you should mark the appropriate namespaces as default by setting the Default property to true. Note that the query with objects with omitted prefixes could be executed successfully only in case of executing it against the database server and connection with matching default settings.

To get the default database and schema names from the underlying database server use appropriate static methods of the MetadataNamesFetcher object: GetDefaultDatabaseName, GetDefaultSchemaNames.

To find the default namespace objects in the Metadata Container use the following code:

// load the first level of the Metadata Container which should have the default database 
// in case of connecting to the database server that supports multiple databases
queryBuilder1.MetadataContainer.Items.Load(MetadataType.Database, false);

// get list of loaded databases
List<MetadataNamespace> databases = queryBuilder1.MetadataContainer.Items.GetItems<MetadataNamespace> (MetadataType.Database);

// find the default database in the list
foreach (MetadataNamespace database in databases)
{
    if (database.Default)
    {
        database.Items.Load(MetadataType.Schema, false);
        List<MetadataNamespace> schemas = database.Items.GetItems<MetadataNamespace> (MetadataType.Schema);

        foreach (MetadataNamespace schema in schemas)
        {
            if (schema.Default)
            {
                MetadataObject table = schema.AddTable("Demo Table");
                // ...
            }
        }
    }
}

This page was: Helpful | Not Helpful