HomeActive Query Builderprogramatically control the table the users see

programatically control the table the users see

I want to be able to display a subset of tables to a user. How do I promatically limit the set of tables a user sees? I'm using the OCX web version of the control.
Michael Thornton | email
November 14 2008, 06:12 PM
Dear Michael,

There are several ways to do that.

1) Metadata Filters:

Using them you may prevent loading objects to the Metadata Container from specific schema or with specific object name.

The following example excludes objects with names starting with "pre_":

MetadataFilterItem mfi = queryBuilder1.MetadataFilter.Add;
mfi.Exclude = true;
mfi.ObjectMask = 'pre_%';

And the following example excludes all schemas except the "schema":

MetadataFilterItem mfi = queryBuilder1.MetadataFilter.Add;
mfi.Exclude = false;
mfi.SchemaMask = 'schema';

The Exclude flag set to False instructs to load only objects that meet the mask. Being set to True, this mask instructs to load all objects except those that meet the mask.

Object and Schema masks act similar to the "LIKE" SQL operator added to the metadata fetching query.

You may define more than one filter at a time. In this case, filter items will be applied consistently.

Please note that metadata filters should be set BEFORE calling the RefreshMetadata method.

The LoadMetadataByMask method is an alternative way to load metadata from the database using filtration:

LoadMetadataByMask('%', '%', 'database');

First argument is an object name mask, second is a schema mask and the third argument is a database name, that could be omitted if you database server doesn't support multiple databases.

Usage of the LoadMetadataByMask method substitutes simple metadata filter definition (with the Exclude flag set to False) and call of the RefershMetadata method.

Please note that the RefreshMetadata method will clear the Metadata Container before loading, while the LoadMetadataByMask will not clear it.

2) Direct removal (or direct addition) from/to the Metadata Container:

Another way to exclude unwanted objects is to iterate through the MetadataContainer collection and remove or hide specific objects. This should be made AFTER call of the RefreshMetadata method.

If you already have the list of allowed objects, you may not call the RefreshMetadata method at all, but fill the Metadata Container by youself:

For Each AllowedTableName As String In AllowedTables
QueryBuilder.MetadataContainer.AddTable("", AllowedTableName, False, "")

Fisrt parameter of the AddTable method is a schema name, fourth is a database name, third parameter is a flag for system objects.

3) Loading metadata from the XML file:

You may pre-create the XML file that stores all necessary metadata information and load it on start of the Query Builder. This way you may:
a) speed up the metadata loading process.
b) exclude unwanted objects from the database.
c) add missing information to the database schema (for example, some databases have missed the foreign key information, so joins will not be created automatically)
d) add descriptions or alternate names for database objects and fields.

To build an XML file, you may:
a) use the standalone Metadata Editor tool at http://www.activequerybuilder.com/files/metadata_editor.zip
b) use the same tool included in the component by calling the MetadataContainer.Edit method.
c) use the sample code below to create it programmatically:

// =========================
// Part one: creation of initial XML file
// ========================

// set this option to True BEFORE retrieving metadata from the database.
// setting this option will slower metadata retrieval procedure,
// but allows you to extract fields of all database objects at once.
ActiveQueryBuilderX1.WorkOffline = True; // This property is called "OfflineMode" in the .NET Edition

// setup database connection
ActiveQueryBuilderX1.ConnectionString = 'your ADO connection string here';

// Retrieve metadata from the database and load it to the Metadata Container
ActiveQueryBuilderX1.Connect; //Other editions should use the RefreshMetadata method instead

// save metadata container content to the XML file
ActiveQueryBuilderX1.MetadataContainer.SaveToXMLFile('your filename.xml');

// or retrieve the same information as a string:
mystring = ActiveQueryBuilderX1.MetadataContainer.XML;

// =========================
// Part two: loading metadata from XML file
// ========================

// you should also set the offline mode when working without database connection
// to prevent retrieval of additional metadata information from the database
ActiveQueryBuilderX1.WorkOffline = True;

// load metadata container container from XML file or from URL
ActiveQueryBuilderX1.MetadataContainer.LoadFromXMLFile('your filename or URL.xml');

// or load the same information from string
ActiveQueryBuilderX1.MetadataContainer.XML = mystring;

d) Create the file by following the rules of XSD Schema available at http://www.activequerybuilder.com/schemas/metadata.xsd

To deny parsing of queries with objects that aren't present in the Metadata Container, you should set the ParsingErrorOnUnknownObjects property to True. Another way is to check for illegal objects before execution using the QueryStatistics.UsedDatabaseObjects collection.

Hope my explanation will help you to cope with your task.
Sergey Smagin | email
7 hours, 8 mins since original post
This topic is closed