The QueryBuilder component has SyntaxProvider and MetadataProvider properties. This article describes the purpose of these objects and explains which one of available syntax and metadata providers you must choose.

Syntax Provider

Syntax Providers define the rules of SQL query parsing and generation for a particular database server. They also determine the queries to retrieve metadata from a particular database server using system tables. The component cannot work without a syntax provider. Also, there are few generic syntax providers for different ANSI SQL standards that can be used with any database server that's compatible with the appropriate standard.

The best option is to use a specific syntax provider for your database server. They are named according to the supported database server families. For example, use MySQLSyntaxProvider for MySQL and MariaDB; use PostgreSQLSyntaxProvider for PostgreSQL, EnterpriseDB, Redshift and other members of the PostgreSQL family.

The ANSI SQL/89, SQL/92, SQL/2003 syntax providers can be used for Google BigQuery, Cassandra, Snowflake and many other database servers that state the compatibility with a corresponding ANSI SQL Standard.

You can use the AutoSyntaxProvider if you don't know to which database server the user will be connected. This syntax provider will send a series of server-specific queries to detect the underlying database server. After successful detection it will act as an appropriate database server syntax provider; otherwise, it will act as the Generic syntax provider.

The GenericSyntaxProvider is NOT recommended to work with the database servers for which Active Query Builder has a special syntax provider. This can only be an option when nothing else works. The Generic syntax provider implements just the ANSI SQL/92 syntax. It differs from the ANSI SQL/92 syntax as it implements the way to retrieve metadata from all the supported database servers. It was originally made for the Free Edition of Active Query Builder which didn't have syntax providers for specific database servers.

Metadata Provider

Metadata Providers are intended to link Active Query Builder to specific data access components. Their primary task is to execute the metadata retrieval queries sent by the component. The Metadata provider does not create database connectivity objects, but an instance of an appropriate database connection object should be assigned to the Connection property of the metadata provider.

The OLE DB, ODBC, and some other metadata providers also can retrieve metadata information using the GetSchema or other methods of appropriate data access components. If Active Query Builder fails to load metadata using the server-specific queries, it tries to load this information using a metadata provider.

UniversalMetadataProvider is suitable for all types of database connections as it accepts System.Data.Common.DbConnection object which is a common ancestor of all .NET DB connections, but this provider not always the best choice as it lacks the support for specific DB connection ways to retrieve metadata. I recommend you to use it only if there's no specific metadata provider for your DB connection.

The EventMetadataProvider provides a way to get connected to DB connections that aren't inherited from the DbConnection object. It might be suitable for a three-tier connection or a web service.

The component can work without a metadata provider. In this case, it will not be able to execute the metadata retrieval queries. Thus this information must be loaded in some other ways and the QueryBuilder.OfflineMode property should be set to True to avoid run-time exceptions.

Code Sample

The following .NET C# code illustrates the common way of initializing metadata and syntax providers:

var connection = new <YourDbConnection>();
connection.ConnectionString = "your connection string here";

var syntaxProvider = new xxxSyntaxProvider(); // choose syntax provider according to your database server

var metadataProvider = new xxxMetadataProvider(); // choose a metadata provider for your connection method
metadataProvider.Connection = connection;

queryBuilder.SyntaxProvider = syntaxProvider;
queryBuilder.MetadataProvider = metadataProvider;

Compatibility

Below is the list of supported DbConnection namespaces and appropriate metadata providers for Active Query Builder for .NET.

Namespace	Metadata Provider
System.Data.Common	UniversalMetadataProvider
System.Data.OleDb	OLEDBMetadataProvider
System.Data.Odbc	ODBCMetadataProvider
System.Data.SqlClient	MSSQLMetadataProvider
Microsoft.Data.SqlClient	MSSQLClientMetadataProvider (new)
System.Data.SqlServerCe	MSSQLCEMetadataProvider
System.Data.OracleClient (obsolete)	OracleMetadataProvider
Oracle.ManagedDataAccess	OracleNativeMetadataProvider (recommended)
MySql.Data	MySQLMetadataProvider
Npgsql	PostgreSQLMetadataProvider
FirebirdSql.Data.FirebirdClient	FirebirdMetadataProvider
iAnywhere.Data.SQLAnywhere	SybaseMetadataProvider
IBM.Data.Informix	InformixMetadataProvider
IBM.Data.DB2	DB2MetadataProvider Note: Using DB2 for AS/400 please turn the DB2SyntaxProvider.NamingConvention to System.
Advantage.Data.Provider	AdvantageMetadataProvider
VistaDB.5.NET40	VistaDB5MetadataProvider
-	EventMetadataProvider