Metadata filtration, hiding unwanted objects from the tree
There are several ways to exclude unwanted objects from the Database Schema View.
1. You can define filters to exclude objects by name or mask. This mask will be applied during the metadata load from a database. This way is described below.
2. You can preload metadata from a database and save the content of the Metadata Container to an XML file. Then you can edit this file using any XML or text editor or the special Metadata Editor Tool to remove unwanted items. Then you must load the Metadata Container from this file instead of loading metadata from a database.
The Metadata Editor Tool can be downloaded from the download page (in the "Additional Downloads" section). When editing is finished, you can save the result to an XML file and then load metadata from this XML file instead of working with the live database connection. Loading metadata from the XML file will also reduce a load on your database and web servers (in the case of the ASP.NET Edition).
3. The third way is to keep the Metadata Container untouched, but use the Metadata Structure presentation layer to customize the Database Schema Tree, this way you can only hide objects from the tree, not to remove them from the Metadata Container.
4. Finally, you can use events of the Metadata Container to remove objects once they get loaded into the Metadata Container.
Using metadata filters, you can prevent the loading of objects with specific names or load objects with specific names only. You can also display only objects of certain types or from specified databases/schemas.
Note that you can also specify a metadata filter for a node of the Metadata Structure (MetadataStructureItem.MetadataFilter). You can read more about defining custom metadata structure in this article: Customizing Database Schema Tree structure.
You can specify metadata filters in two ways. It's easier to set up filters in the first way, but using the second way you can create more complex filters.
The simple way to define filters
The QueryBuilder.MetadataLoadingOptions.IncludeFilter lets define a set of visible objects (only the objects which satisfy it will be visible) and the QueryBuilder.MetadataLoadingOptions.ExcludeFilter allows defining a set of objects which should not be visible (the objects that satisfy it will be hidden). You can define both properties, and they will work together just fine.
Both properties are of the SimpleMetadataFilter class which has Names, Schemas, and Types fields. Using them you can specify masks for database object and schema names (read the rules of mask definition below), set types of objects to display or hide.
For example, in the result of the execution of the following statements, the user will see all views from the database, except those which reside in the "dbo" schema.
queryBuilder1.MetadataLoadingOptions.IncludeFilter.Types = [View]; queryBuilder1.MetadataLoadingOptions.ExcludeFilter.Schemas.Add("dbo");
queryBuilder1.InitializeDatabaseSchemaTree(); // for WinForms and WPF
// or queryBuilder1.MetadataStructure.Refresh(); // for ASP.NET
The advanced way to define filters
A Metadata Filter is a set of filtration rules. Each rule in the list is represented by the MetadataFilterItem object. An item can define objects to be set excluded or included in the result set according to the Exclude property. Metadata Filter can have multiple exclusion and inclusion items (rules). If Metadata Filter has no inclusion items, the exclusion items will be applied to the whole database schema. If Metadata Filter has some inclusion items, they will constitute a subset of the database schema, to which the exclusion items will be applied.
Each Metadata Filter Item has a mask for each type of metadata item: Server, Database, Schema, Package, Object, and Field. Each mask determines a subset of objects whose names match this mask. Those masks act similar to the "LIKE" SQL operator: you can use the percent character (%) to represent any sequence of characters; use the underscore character (_) to represent any single character. The CaseSensitive property determines the case sensitivity of masks. The ObjectTypes property determines which object types (Table, View, Synonym, Procedure, or Function) will be included or excluded.
You may treat those rules as SQL conditions combined with OR operators in the WHERE clause of the SELECT statement that retrieves the list of objects to be loaded to the Metadata Container. Conditions marked with the Exclude flag are prefixed with the NOT operator. The result SQL WHERE clause will look like the following: "([inculsion rule] OR [inclusion rule] OR ...) AND NOT ([exculsion rule] OR [exclusion rule] OR ...)". Masks of one rule are combined with the AND operator.
The following example adds a new filter item that excludes objects with names starting with "pre_":
MetadataFilterItem mfi = queryBuilder1.MetadataLoadingOptions.MetadataFilter.Add(); mfi.Exclude = true; mfi.Object = "pre_%"; mfi.CaseSensitive = true;
And the following example adds a new filter item that excludes all schemas except the "schema":
MetadataFilterItem mfi = queryBuilder1.MetadataLoadingOptions.MetadataFilter.Add(); mfi.Exclude = false; mfi.Schema = "dbo";
The following sample demonstrates filtration by object type:
MetadataFilterItem metadataFilterItem = queryBuilder1.MetadataLoadingOptions.MetadataFilter.Add(); metadataFilterItem.Exclude = false; metadataFilterItem.ObjectTypes = MetadataType.Table | MetadataType.View; metadataFilterItem.FlagSystem = false; metadataFilterItem.FlagUser = true; queryBuilder1.MetadataContainer.Items.Clear(); queryBuilder1.InitializeDatabaseSchemaTree();