Customizing the Database Schema Tree structure
Metadata Structure maps items of the Metadata Container into a tree-like structure and displays it in the Database Schema View control. It allows abstracting from the physical database schema.
There are wide possibilities of the Metadata Structure customization starting from the ordinary grouping of objects by namespaces and their object types up to the complete regrouping of objects by subject areas or with some other characteristics. One may want to keep objects grouped by physical database structure, but add a special folder for saving frequently used database objects or displaying virtual objects in it.
- Visual customizations, such as rearrangement of objects in the tree, or use the Metadata Editor tool.
The Metadata Editor can be used to construct a custom metadata structure for your needs. You can open it as a dialog from your application using the QueryBuilder.EditMetadataContainer static method or by downloading the Standalone Metadata Editor tool from the download page.
Short sample: How to customize database schema tree with the Metadata Container Editor.
- Programmatic modifications are performed by adding custom nodes to the Metadata Structure.
The Metadata Structure object can be accessed via the QueryBuilder.MetadataStructure property.
By default, the structure just groups objects in the tree by namespaces. You can adjust database objects grouping using the QueryBuilder.MetadataStructureOptions group of properties.
The new Metadata Structure demo project provides the necessary code samples for building a custom metadata structure.
Metadata Structure is a tree-like structure consisting of MetadataStructureItem nodes, which can be static or dynamic.
Static nodes are the nodes added programmatically or loaded from the Metadata Structure XML file created by the Metadata Editor tool. They are displayed in the tree irrespective of the content of the Metadata Container.
Dynamic nodes are the nodes that were generated automatically upon expansion of parent nodes (static or dynamic).
A node generates child items automatically if its AllowChildAutoItems property is set to True.
So, this is how the Metadata Structure works by default: child nodes are generated automatically for each node starting from the root node up to the table or field nodes according to the content of the Metadata Container and the grouping options.
A static node can represent a particular object of the Metadata Container by referring to it via the MetadataStructureItem.MetadataItem or MetadataName properties. The first property is a link to the in-memory object while the latter is a string so it can be used to store the relation in a saved XML file or when the Metadata Container is not fully loaded. You can set a reference using any of these properties, but not both at a time (an error will arise in the case of using both). Dynamic nodes get references to appropriate Metadata Container objects when created.
A node that generates child items automatically can have a Metadata Filter defined. It allows narrowing the list of objects displayed in a particular node. You can learn more about metadata filters here.
If a static node does not have a reference to an object in the Metadata Container itself, it acts like a folder. A folder node that has the AllowChildAutoItems property set to True gets child items from the nearest parent node for which a reference to the Metadata Container object is defined. This lets split the child items of a particular node into groups.
This is how database objects are divided in the tree by their type. A node that refers to the schema object in the Metadata Container has the AllowChildAutoItems property set to false and has static child nodes, one for each database object type (table, view, synonym, and procedure). Each child node has the AllowChildAutoItems set to true and contains a filter that prescribes to display only objects of the specific type in it.
There are two basic scenarios of using a static node in the Metadata Structure:
- Set the AllowChildAutoItems property to false and populate it with child items by yourself;
- Set the AllowChildAutoItems property to true and link it with the specific item of Metadata Container (MetadataStructureItem.MetadataItem property) to let it be populated with child items automatically.
- Combination of these techniques: allow loading of child items complementing them with static nodes (items or folders).
Anyway, there should be some static nodes below the root which are filled with other static items or allowed for the generation of dynamic items. To add a static node below the dynamic one, use the MetadataStructure.ItemAdded event for this purpose (for example, to add the "Favorite" node to each database or schema).
If you want to remove some of the dynamic child items programmatically, use the MetadataStructure.ItemBound event which is fired after the node is populated with child items.
Creating an item that corresponds to the namespace (database, schema, etc.):
MetadataStructureItem msi = new MetadataStructureItem(); msi.MetadataName = "Database2"; // AQB will search for this name in child items of the Metadata Item referred in the parent Metadata Structure Item msi.AllowChildAutoItems = false; // deny automatic fill for this node to add only the needed objects further queryBuilder.MetadataStructure.Items.Add(msi);
Creating a custom folder:
MetadataStructureItem folder = new MetadataStructureItem(); folder.Caption = "Favorites";
folder.AllowChildAutoItems = false; queryBuilder.MetadataStructure.Items.Add(folder);
Creating a folder with metadata filter:
MetadataStructureItem viewsItem = new MetadataStructureItem(); viewsItem.Caption = "Views"; var filter = viewsItem.MetadataFilter.Add(); filter.ObjectTypes = MetadataType.View; queryBuilder.MetadataStructure.Items.Add(viewsItem);
Referring to a database object by its name:
MetadataStructureItem tableItem = new MetadataStructureItem(); tableItem.MetadataName = "Northwind.dbo.Orders"; // AQB will search for this full-qualified name in the entire Metadata Container queryBuilder.MetadataStructure.Items.Add(tableItem);
Adding a particular MetadataObject to the structure (this way you can add any type of metadata objects: namespaces, tables, fields, etc):
MetadataItem myMetadataObject = new queryBuilder.MetadataContainer.AddTable("Table1"); // ... MetadataStructureItem structureItem = new MetadataStructureItem(); structureItem.MetadataItem = myMetadataObject; queryBuilder.MetadataStructure.Items.Add(structureItem);