HomeActive Query Builder - IntroductionPrinter Friendly Version

Active Query Builder - Introduction

Common information about all versions and editions of Active Query Builder component.

1. Quick Start Guides

1.1. Quick Start Guide for .NET WinForms Edition 2.x

  1. Place the QueryBuilder component on the form.

    using ActiveDatabaseSoftware.ActiveQueryBuilder;
    ...
    QueryBuilder queryBuilder1 = new QueryBuilder();
  2. Place required metadata and syntax provider components on the form. Define a proper database connection object for the metadata provider.

    OleDbConnection connection = new OleDbConnection();
    connection.ConnectionString = "<your connection string here>";
    
    OLEDBMetadataProvider metadataProvider = new OLEDBMetadataProvider();
    metadataProvider.Connection = connection;
    
    GenericSyntaxProvider syntaxProvider = new GenericSyntaxProvider();
  3. Link the components above to the QueryBuilder by setting MetadataProvider and SyntaxProvider properties

    queryBuilder1.MetadataProvider = metadataProvider;
    queryBuilder1.SyntaxProvider = syntaxProvider;
  4. Place an SQLBuilder component on the form to get SQL code generated by the QueryBuilder component with formatting. Link it to the QueryBuilder object by setting the QueryBuilder property.

    PlainTextSQLBuilder sqlBuilder = new PlainTextSQLBuilder();
    sqlBuilder.QueryBuilder = queryBuilder1;
  5. Add the TextBox or any other text editing component to a form.

  6. Now you should establish connection between SQLBuilder and the TextBox components.
    Enter the following code to the Validate event of TextBox component:

    private void textBox1_Validating(object sender, System.ComponentModel.CancelEventArgs e)
    {
    	try
    	{
    		// Update the query builder with manually edited query text:
    		queryBuilder.SQL = textBox1.Text;
    	}
    	catch (SQLParsingException ex)
    	{
    		e.Cancel = true;
    		
    		// Set caret to error position
    		textBox1.SelectionStart = ex.ErrorPos.pos;
    		// Report error
    		MessageBox.Show(ex.Message, "Parsing error");
    	}
    }

    Enter the following code to SQLUpdated event of SQLBuilder component:

    textBox1.Text = sqlBuilder.SQL;
    
  7. Call the following method to activate Active Query Builder's database schema tree:
    queryBuilder1.InitializeDatabaseSchemaTree();
    
  8. That's all! Now you can run your application.

1.2. Quick Start Guide for ASP.NET edition: Classic ASP.NET environment

Important:

This guide is for Classic ASP.NET environment only!

Please refer to the appropriate guides for MVC environments:

Follow the steps below to install Active Query Builder ASP.NET Edition to your project. If you already have the component installed in your project and need further information about getting or setting the SQL query text, go to the Mini F.A.Q. section of this guide.

Easy installation
by using the NuGet package:

  1. Add the NuGet Package Manager Visual Studio extension.
  2. Add the "Active Query Builder ASP.NET Edition" NuGet package to your project.
  3. Add typical HTML code and the component's initialization code to your web page.

Traditional installation
by following the steps of this guide:

  1. Add the necessary assemblies.
  2. Add the necessary HTTP handlers and modules according to your IIS version.
  3. Add the necessary JavaScript and CSS files.
  4. Load JavaScript libraries manually or using the Script Manager.
  5. Add typical HTML code and the component's initialization code to your web page.
  1. Create a new solution and add a new project – ASP.NET Web Application.

    The steps below can be skipped in case of using the "Active Query Builder ASP.NET Edition" NuGet package.

    Proceed to the final steps in case of using it.

  2. Add references to the following assemblies:

    • \assemblies\.NET 2.0\ActiveDatabaseSoftware.ActiveQueryBuilder2.dll
    • \assemblies\.NET 2.0\ActiveDatabaseSoftware.ActiveQueryBuilder2.Web.Control.dll
    • \assemblies\.NET 2.0\ActiveDatabaseSoftware.ActiveQueryBuilder2.Web.Server.dll
    • \assemblies\third-party\Newtonsoft.Json.dll
    • \assemblies\third-party\log4net.dll

    You may also need to add the assemblies for specific metadata providers to retrieve metadata from a database, e.g.:

    • \assemblies\.NET 2.0\ActiveDatabaseSoftware.OLEDBMetadataProvider.dll

    Is it necessary to set the CopyLocal property of "ActiveDatabaseSoftware.ActiveQueryBuilder2.*" assemblies to True. This isn't done automatically in case of instructing to install the component to the Toolbox because assemblies are installed to GAC in this case.

    Active Query Builder requires Newtonsoft Json assembly version not earlier than 4.5. Apache log4net.dll is required only in debug configuration. Active Query Builder is binded to the log4net binary version labeled as "oldkey" for compatibility with earlier versions of this library.

  3. Add the necessary HTTP handlers and modules to the "web.config" file according to your web server version.

    • For IIS 6, add the following handlers in the "configuration/system.web/httpHandlers" section:
      
         <add path="handlers/exchange.axd" verb="*" 
            type="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server.Handlers.Exchange,
            ActiveDatabaseSoftware.ActiveQueryBuilder2.Web.Server"
         />
      
      and the following module to the "configuration/system.web/modules" section:
      
         <add name="HttpModule" 
            type="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server.HttpModule,
            ActiveDatabaseSoftware.ActiveQueryBuilder2.Web.Server"
         />
      
    • For IIS 7, add the following handlers in the "configuration/system.webServer/handlers" section:
      
         <add name="handlers/exchange.axd_*" preCondition="integratedMode"  
            path="handlers/exchange.axd" verb="*" 
            type="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server.Handlers.Exchange,
            ActiveDatabaseSoftware.ActiveQueryBuilder2.Web.Server" 
         />
      

      and the following module to the "configuration/system.webServer/modules" section:

      
         <add name="HttpModule" preCondition="integratedMode"
            type="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server.HttpModule,
            ActiveDatabaseSoftware.ActiveQueryBuilder2.Web.Server" />
      
  4. Get content from the "web_parts" directory ("css", "img" and "js" sub-folders) and put it in the directory of the web page or the parent control. Alternatively you can set paths to specific locations of these directories using the QueryBuilderControl.DirectoryJS and the QueryBuilderControl.DirectoryCSS properties.

    The necessary JavaScript libraries are loaded automatically unless a different behavior is not determined by the QueryBuilderControl.LoadJScript property. Setting this property to false might be needed in case of using the Script Manager or some other dynamic JavaScript loading tool.

    To load the necessary libraries using Script Manager having set the LoadJScript to false, usе this code:

    
    <asp:ScriptManager runat="server">
       <Scripts>
          <asp:ScriptReference Name="jquery" />
          <asp:ScriptReference Name="jquery.ui.combined" />
          <asp:ScriptReference Path="~/js/release/usr_v0_x_x.js" />
    

    To load the necessary libraries manually having set the LoadJScript to false, add the following code to the HEAD section of your web page:

    
    <script src="js/release/jquery.js" type="text/javascript"></script>
    <script src="js/release/jquery-ui.js" type="text/javascript"></script>
    <script src="js/release/usr_v0_x_x.js" type="text/javascript"></script>
    

    Active Query Builder ASP.NET Edition requires jQuery 1.11 or higher, and jQueryUI 1.10.4 or higher (both from the 1.xx branch).

  5. Typical HTML code can be found in the "web_parts\!(C# or VB)" folder, in "Default.aspx" and "QueryBuilder.ascx" files, or you can simply add the following content to your web-page.

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Client.Default" %>
    <%@ Register Assembly="ActiveDatabaseSoftware.ActiveQueryBuilder2.Web.Control" Namespace="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Control" TagPrefix="AQB" %>
    <html>
        <head runat="server">
            <title></title>
        </head>
        <body>
            <form id="Form1" runat="server">
                <AQB:QueryBuilderControl ID="QueryBuilderControl1" runat="server" OnInit="QueryBuilderControl1_Init" />
                <div id="all">
                    <div id="content-container">
                        <div id="qb-ui">
                            <AQB:ObjectTree ID="ObjectTree1" runat="server" ShowFields="true" />
                            <div id="center">
                                <AQB:Canvas ID="Canvas1" runat="server" />
                                <AQB:Grid ID="Grid1" runat="server" />
                                <AQB:StatusBar ID="StatusBar1" runat="server" />
                            </div>
                            <div class="clear"></div>
                        </div>
                    </div>
                    <AQB:SqlEditor ID="SQLEditor1" runat="server"></AQB:SqlEditor>
                </div>
            </form>
    
        </body>
    </html>
  6. Place your initialization code in the QueryBuilderControl.Init event handler. There you should create Metadata and Syntax provider components and link these providers to the QueryBuilder object by setting the MetadataProvider and SyntaxProvider properties. Define a proper database connection object as a source for the Metadata provider or load metadata from the XML file.

    The server side of the component is based on the WinForms .NET Edition of Active Query Builder. There are two object references available to get access to their methods and properties: QueryBuilderControl.QueryBuilder and QueryBuilderControl.PlainTextSQLBuilder.

    private void QueryBuilderControl1_OnInit(object sender, EventArgs e)
    {
        // Get instance of the QueryBuilder object
        QueryBuilder queryBuilder = QueryBuilderControl1.QueryBuilder;
    
        // Create an instance of the proper syntax provider for your database server. 
        // - use AutoSyntaxProvider if you want to detect your database server automatically 
        //   (autodetection works in case of live database connection only);
        // - use ANSI or Universal syntax provider only if you can't find the right syntax 
        //   provider for your database server.
        MSSQLSyntaxProvider syntaxProvider = new MSSQLSyntaxProvider(); 
    
        queryBuilder.SyntaxProvider = syntaxProvider;
    
        // Metadata source definition. Choose one of the following ways:
        // ===========================================================
        // a) you can load metadata from live database connection
        
        OleDbConnection connection = new OleDbConnection {ConnectionString = "<your connection string here>"};
    
        queryBuilder.MetadataProvider = new OLEDBMetadataProvider {Connection = connection};
    
        // ===========================================================
        // b) or you can load metadata from the pre-generated XML file
        
        sting pathToXml = Server.MapPath("~\Path\to\file.xml");
    
        // Denies metadata loading requests from the metadata provider
        queryBuilder.OfflineMode = true;
    
        queryBuilder.MetadataContainer.ImportFromXML(pathToXml);
    
        // end of b)
        // ===========================================================
    
        // Initialization of the Metadata Structure object that's
        // responsible for representation of metadata in a tree-like form
        try
        {
            // Clears and loads the first level of the metadata structure tree  
            queryBuilder.MetadataStructure.Refresh();
        }
        catch (Exception ex)
        {
            Logger.Error("Error loading metadata.", ex);
        }
    }
  7. That's all! Now you can run your application.

Mini F.A.Q.: Getting and setting the SQL query text

  • To retrieve the query text from the query builder control use the following code:

    protected void btnGetQuery_Click(object sender, System.EventArgs e)
    {
        // First variant:
        // a) if your code is at the page that contains the QueryBuilderUC User Control:
        QueryBuilderControl control = (QueryBuilderControl) QueryBuilderUC1.FindControl("QueryBuilderControl1");
        QueryBuilder queryBuilder = control.QueryBuilder;
        PlainTextSQLBuilder sqlBuilder = control.PlainTextSQLBuilder;
    
        // b) if your code is inside the QueryBuilderUC User Control:
        QueryBuilder queryBuilder = QueryBuilderControl1.QueryBuilder;
        PlainTextSQLBuilder sqlBuilder = QueryBuilderControl1.PlainTextSQLBuilder;
    
        // Second variant: globally available static helpers
        QueryBuilder queryBuilder = SessionStore.Current.QueryBuilder;
        PlainTextSQLBuilder sqlBuilder = SessionStore.Current.PlainTextSQLBuilder;
    
        // Common part:
        string plainSQL = queryBuilder.SQL; // unformatted text
        string formattedSQL = sqlBuilder.SQL; // formatted text
    }
  • To retrieve the query text from the text editor, use the following code:

    protected void btnGetQuery_Click(object sender, System.EventArgs e)
    {
        // if your code is at the page that contains the QueryBuilderUC User Control:
        SQLEditor editor = (SQLEditor) QueryBuilderUC1.FindControl("SQLEditor1");
        string textSQL1 = editor.SQL;
    
        // if your code is inside the QueryBuilderUC User Control:
        string textSQL2 = SQLEditor1.SQL;
    }

    If you want to parse the final modifications made to SQL text by the end-user before the query execution, additionaly use the following code:

    
        try
        {
            queryBuilder.SQL = editor.SQL;
    
            // execute the query here
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Parsing error");
        }
    
  • To assign SQL query text to the control use the following code:

    protected void btnSetQuery_Click(object sender, System.EventArgs e)
    {
        // if your code is at the page that contains the QueryBuilderUC User Control:
        SQLEditor editor = (SQLEditor) QueryBuilderUC1.FindControl("SQLEditor1");
        editor.SQL = "SELECT * FROM ...";
    
        // if your code is inside the QueryBuilderUC User Control:
        SQLEditor1.SQL = "SELECT * FROM ...";
    }

    If you want to do this without reloading a web page, additionaly use the following JavaScript code:

    QB.Web.Application.fullUpdate();
  • To update the SQL text on the client use the following JavaScript code:

    QB.Web.Application.importSQL('Select * From ...');
    QB.Web.Application.refreshSql();

1.3. Quick Start Guide for ASP.NET edition: MVC 2.0 environment

Important:

This guide is for MVC 2.0 environment only!

Please refer to the appropriate guides for Classic and MVC 4.0 environments:

Follow the steps below to install Active Query Builder ASP.NET Edition to your project. If you already have the component installed in your project and need further information about getting or setting the SQL query text, go to the Mini F.A.Q. section of this guide.

Easy installation
by using the NuGet package:

  1. Add the NuGet Package Manager Visual Studio extension.
  2. Add the "Active Query Builder ASP.NET Edition" NuGet package to your project.
  3. Add typical HTML code and the component's initialization code to your web page.

Traditional installation
by following the steps of this guide:

  1. Add the necessary assemblies.
  2. Add the necessary modules according to your IIS version and routes.
  3. Add the necessary JavaScript and CSS files.
  4. Load JavaScript libraries manually or using the Script Manager.
  5. Add typical HTML code and the component's initialization code to your web page.
  1. Create a new solution and add a new project – ASP.NET Web Application.

    The steps below can be skipped in case of using the "Active Query Builder ASP.NET Edition" NuGet package.

    Proceed to the final steps in case of using it.

  2. Add references to the following assemblies:

    • \assemblies\.NET 2.0\ActiveDatabaseSoftware.ActiveQueryBuilder2.dll
    • \assemblies\.NET 2.0\ActiveDatabaseSoftware.ActiveQueryBuilder2.Web.Control.dll
    • \assemblies\.NET 2.0\ActiveDatabaseSoftware.ActiveQueryBuilder2.Web.Server.dll
    • \assemblies\third-party\Newtonsoft.Json.dll
    • \assemblies\third-party\log4net.dll

    You may also need to add the assemblies for specific metadata providers to retrieve metadata from a database, e.g.:

    • \assemblies\.NET 2.0\ActiveDatabaseSoftware.OLEDBMetadataProvider.dll

    Is it necessary to set the CopyLocal property of "ActiveDatabaseSoftware.ActiveQueryBuilder2.*" assemblies to True. This isn't done automatically in case of instructing to install the component to the Toolbox because assemblies are installed to GAC in this case.

    Active Query Builder requires Newtonsoft Json assembly version not earlier than 4.5. Apache log4net.dll is required only in debug configuration. Active Query Builder is binded to the log4net binary version labeled as "oldkey" for compatibility with earlier versions of this library.

  3. Add the necessary modules to the "web.config" file according to your web server version.

    • For IIS 6, add the following module to the "configuration/system.web/modules" section:
         <add name="HttpModule" 
            type="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server.HttpModule,
            ActiveDatabaseSoftware.ActiveQueryBuilder2.Web.Server"
         />
      
    • For IIS 7, add the following module to the "configuration/system.webServer/modules" section:

       

         <add name="HttpModule" preCondition="integratedMode"
            type="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server.HttpModule,
            ActiveDatabaseSoftware.ActiveQueryBuilder2.Web.Server" />
      
  4. Add the following route to the to the RegisterRoutes method in the Global.asax.cs file:

    routes.Add(ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Mvc.Routing.GetRoute());
    

    If you want to change the default HTTP handler URL for this route ("/ActiveQueryBuilderHandler.axd"), you can specify it as a parameter for the GetRoute method.

  5. Get content from the "web_parts" directory ("css", "img" and "js" sub-folders) and put it in the directory of the web page or the parent control. Alternatively you can set paths to specific locations of these directories using the QueryBuilderControl.DirectoryJS and the QueryBuilderControl.DirectoryCSS properties.

    The necessary JavaScript libraries are loaded automatically unless a different behavior is not determined by the QueryBuilderControl.LoadJScript property. Setting this property to false might be needed in case of using the Script Manager or some other dynamic JavaScript loading tool.

    To load the necessary libraries using Script Manager having set the LoadJScript to false, usе this code:

    <asp:ScriptManager runat="server">
       <Scripts>
          <asp:ScriptReference Name="jquery" />
          <asp:ScriptReference Name="jquery.ui.combined" />
          <asp:ScriptReference Path="~/js/release/usr_v0_x_x.js" />
    

    To load the necessary libraries manually having set the LoadJScript to false, add the following code to the HEAD section of your web page:

    <script src="js/release/jquery.js" type="text/javascript"></script>
    <script src="js/release/jquery-ui.js" type="text/javascript"></script>
    <script src="js/release/usr_v0_x_x.js" type="text/javascript"></script>
    

    Active Query Builder ASP.NET Edition requires jQuery 1.11 or higher, and jQueryUI 1.10.4 or higher (both from the 1.xx branch).

  6. Typical HTML code can be found in the "web_parts\!(C# or VB)" folder, in "Default.aspx" and "QueryBuilder.ascx" files, or you can simply add the following content to your web-page.

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Client.Default" %>
    <%@ Register Assembly="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Control" Namespace="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Control" TagPrefix="AQB" %>
    <html>
        <head runat="server">
            <title></title>
        </head>
        <body>
            <form id="Form1" runat="server">
                <AQB:QueryBuilderControl ID="QueryBuilderControl1" runat="server" OnInit="QueryBuilderControl1_Init" />
                <div id="all">
                    <div id="content-container">
                        <div id="qb-ui">
                            <AQB:ObjectTree ID="ObjectTree1" runat="server" ShowFields="true" />
                            <div id="center">
                                <AQB:Canvas ID="Canvas1" runat="server" />
                                <AQB:Grid ID="Grid1" runat="server" />
                                <AQB:StatusBar ID="StatusBar1" runat="server" />
                            </div>
                            <div class="clear"></div>
                        </div>
                    </div>
                    <AQB:SqlEditor ID="SQLEditor1" runat="server"></AQB:SqlEditor>
                </div>
            </form>
    
        </body>
    </html>
  7. Place your initialization code in the QueryBuilderControl.Init event handler. There you should create Metadata and Syntax provider components and link these providers to the QueryBuilder object by setting the MetadataProvider and SyntaxProvider properties. Define a proper database connection object as a source for the Metadata provider or load metadata from the XML file.

    The server side of the component is based on the WinForms .NET Edition of Active Query Builder. There are two object references available to get access to their methods and properties: QueryBuilderControl.QueryBuilder and QueryBuilderControl.PlainTextSQLBuilder.

    private void QueryBuilderControl1_OnInit(object sender, EventArgs e)
    {
        // Get instance of the QueryBuilder object
        QueryBuilder queryBuilder = QueryBuilderControl1.QueryBuilder;
    
        // Create an instance of the proper syntax provider for your database server. 
        // - use AutoSyntaxProvider if you want to detect your database server automatically 
        //   (autodetection works in case of live database connection only);
        // - use ANSI or Universal syntax provider only if you can't find the right syntax 
        //   provider for your database server.
        MSSQLSyntaxProvider syntaxProvider = new MSSQLSyntaxProvider(); 
    
        queryBuilder.SyntaxProvider = syntaxProvider;
    
        // Metadata source definition. Choose one of the following ways:
        // ===========================================================
        // a) you can load metadata from live database connection
        
        OleDbConnection connection = new OleDbConnection {ConnectionString = "<your connection string here>"};
    
        queryBuilder.MetadataProvider = new OLEDBMetadataProvider {Connection = connection};
    
        // ===========================================================
        // b) or you can load metadata from the pre-generated XML file
        
        sting pathToXml = Server.MapPath("~\Path\to\file.xml");
    
        // Denies metadata loading requests from the metadata provider
        queryBuilder.OfflineMode = true;
    
        queryBuilder.MetadataContainer.ImportFromXML(pathToXml);
    
        // end of b)
        // ===========================================================
    
        // Initialization of the Metadata Structure object that's
        // responsible for representation of metadata in a tree-like form
        try
        {
            // Clears and loads the first level of the metadata structure tree  
            queryBuilder.MetadataStructure.Refresh();
        }
        catch (Exception ex)
        {
            Logger.Error("Error loading metadata.", ex);
        }
    }
  8. That's all! Now you can run your application.

Mini F.A.Q.: Getting and setting the SQL query text

  • To retrieve the query text from the query builder control use the following code:

    protected void btnGetQuery_Click(object sender, System.EventArgs e)
    {
        // First variant:
        // a) if your code is at the page that contains the QueryBuilderUC User Control:
        QueryBuilderControl control = (QueryBuilderControl) QueryBuilderUC1.FindControl("QueryBuilderControl1");
        QueryBuilder queryBuilder = control.QueryBuilder;
        PlainTextSQLBuilder sqlBuilder = control.PlainTextSQLBuilder;
    
        // b) if your code is inside the QueryBuilderUC User Control:
        QueryBuilder queryBuilder = QueryBuilderControl1.QueryBuilder;
        PlainTextSQLBuilder sqlBuilder = QueryBuilderControl1.PlainTextSQLBuilder;
    
        // Second variant: globally available static helpers
        QueryBuilder queryBuilder = SessionStore.Current.QueryBuilder;
        PlainTextSQLBuilder sqlBuilder = SessionStore.Current.PlainTextSQLBuilder;
    
        // Common part:
        string plainSQL = queryBuilder.SQL; // unformatted text
        string formattedSQL = sqlBuilder.SQL; // formatted text
    }
  • To retrieve the query text from the text editor, use the following code:

    protected void btnGetQuery_Click(object sender, System.EventArgs e)
    {
        // if your code is at the page that contains the QueryBuilderUC User Control:
        SQLEditor editor = (SQLEditor) QueryBuilderUC1.FindControl("SQLEditor1");
        string textSQL1 = editor.SQL;
    
        // if your code is inside the QueryBuilderUC User Control:
        string textSQL2 = SQLEditor1.SQL;
    }

    If you want to parse the final modifications made to SQL text by the end-user before the query execution, additionaly use the following code:

    
        try
        {
            queryBuilder.SQL = editor.SQL;
    
            // execute the query here
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Parsing error");
        }
    
  • To assign SQL query text to the control use the following code:

    protected void btnSetQuery_Click(object sender, System.EventArgs e)
    {
        // if your code is at the page that contains the QueryBuilderUC User Control:
        SQLEditor editor = (SQLEditor) QueryBuilderUC1.FindControl("SQLEditor1");
        editor.SQL = "SELECT * FROM ...";
    
        // if your code is inside the QueryBuilderUC User Control:
        SQLEditor1.SQL = "SELECT * FROM ...";
    }

    If you want to do this without reloading a web page, additionaly use the following JavaScript code:

    QB.Web.Application.fullUpdate();
  • To update the SQL text on the client use the following JavaScript code:

    QB.Web.Application.importSQL('Select * From ...');
    QB.Web.Application.refreshSql();

1.4. Quick Start Guide for ASP.NET edition: ASPX view engine, MVC 4.0

Important:

This guide is for MVC 4.0 environment, ASPX view engine only!

Please refer to the appropriate guides for other environments:

Follow the steps below to install Active Query Builder ASP.NET Edition to your project. If you already have the component installed in your project and need further information about getting or setting the SQL query text, go to the Mini F.A.Q. section of this guide.

Easy installation
by using the NuGet package:

  1. Add the NuGet Package Manager Visual Studio extension.
  2. Add the "Active Query Builder ASP.NET Edition" NuGet package to your project.
  3. Add typical HTML code and the component's initialization code to your web page.

Traditional installation
by following the steps of this guide:

  1. Add the necessary assemblies.
  2. Add the necessary modules according to your IIS version and routes.
  3. Add the necessary JavaScript and CSS files.
  4. Load JavaScript libraries manually or using the BundleConfig.
  5. Add typical HTML code and the component's initialization code to your web page.
  1. Create a new solution and add a new project - ASP.NET MVC 4 Web Application, select the "ASPX" option for the "View engine" setting.

    The steps below can be skipped in case of using the "Active Query Builder ASP.NET Edition" NuGet package.

    Proceed to the final steps in case of using it.

  2. Add the following assembly references to your project:

    • \assemblies\.NET 2.0\ActiveDatabaseSoftware.ActiveQueryBuilder2.dll
    • \assemblies\.NET 2.0\ActiveDatabaseSoftware.ActiveQueryBuilder2.Web.Control.dll
    • \assemblies\.NET 2.0\ActiveDatabaseSoftware.ActiveQueryBuilder2.Web.Server.dll
    • \assemblies\third-party\Net40\Newtonsoft.Json.dll
    • \assemblies\third-party\log4net.dll

    You may also need to add the assemblies for specific metadata providers to retrieve metadata from a database, e.g.:

    • \assemblies\.NET 2.0\ActiveDatabaseSoftware.OLEDBMetadataProvider.dll

    Is it necessary to set the CopyLocal property of "ActiveDatabaseSoftware.ActiveQueryBuilder2.*" assemblies to True. This isn't done automatically in case of instructing to install the component to the Toolbox because assemblies are installed to GAC in this case.

  3. Apply the necessary changes to the "web.config" file according to your web server version.

    • For IIS 6 or Visual Studio Development Server, add the following module to the "configuration/system.web/modules" section:

      <add name="HttpModule"
      	type="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server.HttpModule,
      	ActiveDatabaseSoftware.ActiveQueryBuilder2.Web.Server"/>
      

      and the following assembly to the "configuration/system.web/compilation" section:

      <assemblies>
      	<add assembly="System.Windows.Forms, Version=2.0.0.0, Culture=neutral,
      	PublicKeyToken=B77A5C561934E089" />
      </assemblies>
      
    • For IIS 7, add the following module to the "configuration/system.webServer/modules" section:

      <remove name="Session" />
      <add name="Session" type="System.Web.SessionState.SessionStateModule"
      	preCondition="managedHandler" />
      <add name="HttpModule" preCondition="integratedMode"
      	type="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server.HttpModule,
      	ActiveDatabaseSoftware.ActiveQueryBuilder2.Web.Server" />
      

      and the following assembly to the "configuration/system.web/compilation" section:

      <assemblies>
      	<add assembly="System.Windows.Forms, Version=2.0.0.0, Culture=neutral,
      	PublicKeyToken=B77A5C561934E089" />
      </assemblies>
      
  4. Add the following route to the RegisterRoutes method in the RouteConfig file:

    routes.Add(ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Mvc.Routing.GetRoute());
    

    If you want to change the default HTTP handler URL for this route ("/ActiveQueryBuilderHandler.axd"), you can specify it as a parameter for the GetRoute method.

  5. Get the content from the "web_parts" directory ("css", "img" and "js" sub-folders) and put it in the directory of the web page or the parent control.

  6. To load the necessary JavaScript libraries and stylesheets set the QueryBuilderControl.LoadJScript property to False and add the following code to the <HEAD> tag of your master page:

    <%: Scripts.Render("~/bundles/ActiveQueryBuilder") %>
    <%: Styles.Render("~/css/ActiveQueryBuilder") %>
    

    Add the following code to the RegisterBundles method of the BundleConfig class if your project already uses the jQuery and jQueryUI JavaScript libraries:

    bundles.Add(new ScriptBundle("~/bundles/ActiveQueryBuilder").Include(
    	"~/js/release/usr_v0_8_0.js"));
    
    bundles.Add(new StyleBundle("~/css/ActiveQueryBuilder").Include(
    	"~/css/base.css",
    	"~/css/qb/qb.ui.grid.css",
    	"~/css/qb/qb.ui.table.css",
    	"~/css/qb/qb.ui.tree.css",
    	"~/css/jquery.contextMenu.css",
    	"~/css/jquery.jPaginate.css""));
    

    Or the following code if your project does not use the jQuery library:

    bundles.Add(new ScriptBundle("~/bundles/ActiveQueryBuilder").Include(
    	"~/js/release/jquery.js",
    	"~/js/release/jquery-ui.js",
    	"~/js/release/usr_v0_8_0.js"));
    
    bundles.Add(new StyleBundle("~/css/ActiveQueryBuilder").Include(
    	"~/css/base.css",
    	"~/css/qb/qb.ui.grid.css",
    	"~/css/qb/qb.ui.table.css",
    	"~/css/qb/qb.ui.tree.css",
    	"~/css/jquery.contextMenu.css",
    	"~/css/jquery.jPaginate.css",
    	"~/css/themes/jquery-ui.css"));
    

    Active Query Builder ASP.NET Edition requires jQuery 1.11 or higher, and jQueryUI 1.10.4 or higher (both from the 1.xx branch).

  7. Add the following content to your \Views\SomeController\SomeAction.aspx file. Also you can find typical HTML code in the "web_parts\!MVC" folder.

    <%@ Page Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage" %>
    
    <%@ Import Namespace="System.Data.OleDb" %>
    <%@ Import Namespace="ActiveDatabaseSoftware.ActiveQueryBuilder" %>
    <%@ Register Assembly="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Control" Namespace="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Control" TagPrefix="AQB" %>
    
    <script runat="server">
    public void QueryBuilderControl1_Init(object sender, EventArgs e)
    {
    	// Get instance of the QueryBuilder object
    	var queryBuilder = QueryBuilderControl1.QueryBuilder;
    
    	// create an instance of the proper syntax provider for your database server.
    	// - use AutoSyntaxProvider if you want to detect your database server automatically
    	//   (autodetection works in case of live database connection only);
    	// - use ANSI or Universal syntax provider only if you can't find the right syntax
    	//   provider for your database server.
    	var syntaxProvider = new MSSQLSyntaxProvider();
    
    	queryBuilder.SyntaxProvider = syntaxProvider;
    	
    	// Metadata source definition. Choose one of the following ways:
    	// ===========================================================
    	// a) you can load metadata from live database connection
    	var connection = new OleDbConnection {ConnectionString = "<your connection string here>"};
    
    	queryBuilder.MetadataProvider = new OLEDBMetadataProvider {Connection = connection};
    
    	// ===========================================================
    	// b) or you can load metadata from the pre-generated XML file
    	var pathToXml = Server.MapPath("~\Path\to\file.xml");
    
    	// Denies metadata loading requests from the metadata provider
    	queryBuilder.OfflineMode = true;
    	
    	queryBuilder.MetadataContainer.ImportFromXML(pathToXml);
    
    	// end of b)
    	// ===========================================================
    
    	// Initialization of the Metadata Structure object that's
    	// responsible for representation of metadata in a tree-like form
    	try
    	{
    		// Clears and loads the first level of the metadata structure tree
    		queryBuilder.MetadataStructure.Refresh();
    	}
    	catch (Exception ex)
    	{
    		Logger.Error("Error loading metadata", ex);
    	}
    }
    </script>
    
    
    <asp:Content ID="indexTitle" ContentPlaceHolderID="TitleContent" runat="server">
    	Home Page - My ASP.NET MVC Application
    </asp:Content>
    
    <asp:Content ID="indexContent" ContentPlaceHolderID="MainContent" runat="server">
    	<AQB:QueryBuilderControl ID="QueryBuilderControl1" runat="server" LoadJScript="False" OnInit="QueryBuilderControl1_Init" />
    	<div id="all">
    		<div id="content-container">
    			<div id="qb-ui">
    				<AQB:ObjectTree ID="ObjectTree1" runat="server" />
    				<div id="center">
    					<AQB:SubQueryNavigationBar ID="SubQueryNavigationBar1" runat="server" />
    					<AQB:Canvas ID="Canvas1" runat="server" />
    					<AQB:Grid ID="Grid1" runat="server" />
    					<AQB:StatusBar ID="StatusBar1" runat="server" />
    				</div>
    				<div class="clear">
    				</div>
    			</div>
    		</div>
    		<AQB:SqlEditor ID="SQLEditor1" runat="server"></AQB:SqlEditor>
    	</div>
    </asp:Content>
    

    The initialization code should be placed in the QueryBuilderControl.Init event handler. There you should create Metadata and Syntax provider components and link these providers to the QueryBuilder object by setting the MetadataProvider and SyntaxProvider properties. Define a proper database connection object as a source for the Metadata provider or load metadata from the XML file.

    The server side of the component is based on the WinForms .NET Edition of Active Query Builder. There are two object references available to get access to their methods and properties: QueryBuilderControl.QueryBuilder and QueryBuilderControl.PlainTextSQLBuilder.

    Targeting the project for .NET Framework 4.5, it is necessary to set the QueryBuilderControl.HttpCompressionEnabled property to False.

  8. That's all! Now you can run your application.

Mini F.A.Q.: Getting and setting the SQL query text

  • To retrieve the query text from the query builder control use the following code:

    protected void btnGetQuery_Click(object sender, System.EventArgs e)
    {
    	// First variant:
    	// a) if your code is at the page that contains the QueryBuilderUC User Control:
    	QueryBuilderControl control = (QueryBuilderControl) QueryBuilderUC1.FindControl("QueryBuilderControl1");
    	QueryBuilder queryBuilder = control.QueryBuilder;
    	PlainTextSQLBuilder sqlBuilder = control.PlainTextSQLBuilder;
    
    	// b) if your code is inside the QueryBuilderUC User Control:
    	QueryBuilder queryBuilder = QueryBuilderControl1.QueryBuilder;
    	PlainTextSQLBuilder sqlBuilder = QueryBuilderControl1.PlainTextSQLBuilder;
    
    	// Second variant: globally available static helpers
    	QueryBuilder queryBuilder = SessionStore.Current.QueryBuilder;
    	PlainTextSQLBuilder sqlBuilder = SessionStore.Current.PlainTextSQLBuilder;
    
    	// Common part:
    	string plainSQL = queryBuilder.SQL; // unformatted text
    	string formattedSQL = sqlBuilder.SQL; // formatted text
    }
  • To retrieve the query text from the text editor, use the following code:

    protected void btnGetQuery_Click(object sender, System.EventArgs e)
    {
    	// if your code is at the page that contains the QueryBuilderUC User Control:
    	SQLEditor editor = (SQLEditor) QueryBuilderUC1.FindControl("SQLEditor1");
    	string textSQL1 = editor.SQL;
    
    	// if your code is inside the QueryBuilderUC User Control:
    	string textSQL2 = SQLEditor1.SQL;
    }

    If you want to parse the final modifications made to SQL text by the end-user before the query execution, additionaly use the following code:

    try
    {
    	queryBuilder.SQL = editor.SQL;
    
    	// execute the query here
    }
    catch (Exception ex)
    {
    	MessageBox.Show(ex.Message, "Parsing error");
    }
    
  • To assign SQL query text to the control use the following code:

    protected void btnSetQuery_Click(object sender, System.EventArgs e)
    {
    	// if your code is at the page that contains the QueryBuilderUC User Control:
    	SQLEditor editor = (SQLEditor) QueryBuilderUC1.FindControl("SQLEditor1");
    	editor.SQL = "SELECT * FROM ...";
    
    	// if your code is inside the QueryBuilderUC User Control:
    	SQLEditor1.SQL = "SELECT * FROM ...";
    }

    If you want to do this without reloading a web page, additionaly use the following JavaScript code:

    QB.Web.Application.fullUpdate();
  • To update the SQL text on the client use the following JavaScript code:

    QB.Web.Application.importSQL('Select * From ...');
    QB.Web.Application.refreshSql();

1.5. Quick Start Guide for ASP.NET edition: Razor view engine, MVC 4.0

Important:

This guide is for MVC 4.0 environment, Razor view engine only!

Please refer to the appropriate guides for other environments:

Follow the steps below to install Active Query Builder ASP.NET Edition to your project. If you already have the component installed in your project and need further information about getting or setting the SQL query text, go to the Mini F.A.Q. section of this guide.

Easy installation
by using the NuGet package:

  1. Add the NuGet Package Manager Visual Studio extension.
  2. Add the "Active Query Builder ASP.NET Edition" NuGet package to your project.
  3. Add typical HTML code and the component's initialization code to your web page.

Traditional installation
by following the steps of this guide:

  1. Add the necessary assemblies.
  2. Add the necessary modules according to your IIS version and routes.
  3. Add the necessary JavaScript and CSS files.
  4. Load JavaScript libraries manually or using the BundleConfig.
  5. Add typical HTML code and the component's initialization code to your web page.
  1. Create a new solution and add a new project - ASP.NET MVC 4 Web Application, select the "Razor" option for the "View engine" setting.

    The steps below can be skipped in case of using the "Active Query Builder ASP.NET Edition" NuGet package.

    Proceed to the final steps in case of using it.

  2. Add the following assembly references to your project:

    • \assemblies\.NET 2.0\ActiveDatabaseSoftware.ActiveQueryBuilder2.dll
    • \assemblies\.NET 2.0\ActiveDatabaseSoftware.ActiveQueryBuilder2.Web.dll
    • \assemblies\.NET 2.0\ActiveDatabaseSoftware.ActiveQueryBuilder2.Web.Server.dll
    • \assemblies\third-party\Net40\Newtonsoft.Json.dll
    • \assemblies\third-party\log4net.dll

    You may also need to add the assemblies for specific metadata providers to retrieve metadata from a database, e.g.:

    • \assemblies\.NET 2.0\ActiveDatabaseSoftware.OLEDBMetadataProvider.dll

    Is it necessary to set the CopyLocal property of "ActiveDatabaseSoftware.ActiveQueryBuilder2.*" assemblies to True. This isn't done automatically in case of instructing to install the component to the Toolbox because assemblies are installed to GAC in this case.

  3. Apply the necessary changes to the "web.config" file according to your web server version.

    • For IIS 6 or Visual Studio Development Server, add the following module to the "configuration/system.web/modules" section:

      <add name="HttpModule"
      	type="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server.HttpModule,
      	ActiveDatabaseSoftware.ActiveQueryBuilder2.Web.Server"/>
      

      and the following assembly to the "configuration/system.web/compilation" section:

      <assemblies>
      	<add assembly="System.Windows.Forms, Version=2.0.0.0, Culture=neutral,
      	PublicKeyToken=B77A5C561934E089" />
      </assemblies>
      
    • For IIS 7, add the following modules to the "configuration/system.webServer/modules" section:

      <remove name="Session" />
      <add name="Session" type="System.Web.SessionState.SessionStateModule"
      	preCondition="managedHandler" />
      <add name="HttpModule" preCondition="integratedMode"
      	type="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server.HttpModule,
      	ActiveDatabaseSoftware.ActiveQueryBuilder2.Web.Server" />
      

      and the following assembly to the "configuration/system.web/compilation" section:

      <assemblies>
      	<add assembly="System.Windows.Forms, Version=2.0.0.0, Culture=neutral,
      	PublicKeyToken=B77A5C561934E089" />
      </assemblies>
      
  4. Add the following line to the to the beginning of the RegisterRoutes method in the RouteConfig file:

    routes.Add(ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Mvc.Routing.GetRoute());
    

    If you want to change the default HTTP handler URL for this route ("/ActiveQueryBuilderHandler.axd"), you can specify it as a parameter for the GetRoute method.

  5. Get the content from the "web_parts" directory ("css", "img" and "js" sub-folders) and put it in the directory of the web page or the parent control.

  6. Add the following directives to the <head> tag of the layout file (for example, "_Layout.cshtml").

    @using ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Mvc.UI
    
    @Html.ActiveQueryBuilder().GetCSS(settings =>
    	{
    		// Instructs to load the jQueryUI CSS files
    		settings.LoadJQuery = true;
    		// Determines path to the component's CSS files
    		settings.Path = "~/css/";
    	})
    
    @Html.ActiveQueryBuilder().GetScripts(settings =>
    	{
    		// Instructs to load the jQuery and jQueryUI JavaScript libraries
    		settings.LoadJQuery = true;
    		// Determines path to the component's JavaScript libraries
    		settings.Path = "~/js/release/";
    	})
    

    Active Query Builder ASP.NET Edition requires jQuery 1.11 or higher, and jQueryUI 1.10.4 or higher (both from the 1.xx branch).

  7. Add the following code to your CSHTML file. Also you can find typical HTML code in the "web_parts\!Razor" folder.

    @using System.Data.SqlClient
    @using System.Web.UI.WebControls
    @using ActiveDatabaseSoftware.ActiveQueryBuilder
    @using ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Mvc.UI
    @using ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server  
    
    @Html.ActiveQueryBuilder(settings =>
    	{
    		settings.HttpCompressionEnabled = false;
    		settings.PersistentConnection = false;
    		settings.Language = "auto";
    		settings.UseCustomLanguageFiles = false;
    		settings.CustomLanguagePath = "~/Language Files/";
    
    		settings.Init += (sender, e) =>
    		{
    			// Get instance of the QueryBuilder object
    			var sessionStoreItem = sender as SessionStoreItem;
    			var queryBuilder = sessionStoreItem.QueryBuilder;
    
    			// Create an instance of the proper syntax provider for your database server.
    			queryBuilder.SyntaxProvider = new MSSQLSyntaxProvider();
    
    			// Metadata source definition. Choose one of the following ways:
    			// ===========================================================
    			// a) you can load metadata from the live database connection
    
    			var connection = new SqlConnection { ConnectionString = @"<Your connection string here>"};
    
    			queryBuilder.MetadataProvider = new MSSQLMetadataProvider {Connection = connection};
    
    			// ===========================================================
    			// b) or you can load metadata from the pre-generated XML file
    
    			// Denies metadata loading requests from the metadata provider
    			queryBuilder.OfflineMode = true;
    
    			var pathToXml = Server.MapPath("~\Path\to\file.xml");
    			queryBuilder.MetadataContainer.ImportFromXML(pathToXml);
    
    			// end of b)
    			// ===========================================================
    
    			// Initialization of the Metadata Structure object that's
    			// responsible for representation of metadata in a tree-like form
    			try
    			{
    				// Clears and loads the first level of metadata structure tree  
    				queryBuilder.MetadataStructure.Refresh();
    			}
    			catch (Exception ex)
    			{
    				Logger.Error("Error loading metadata", ex);
    			}
    		};
    	}).GetHtml()
    
    
    <div id="all">
    	<div id="content-container">
    		<div id="qb-ui">
    			@Html.ActiveQueryBuilder().ObjectTree(settings =>
    				{
    					settings.ShowFields = false;
    					settings.ShowDescriptons = false;
    					settings.SortingType = ObjectsSortingType.None;
    					settings.GroupingType = ObjectsGroupingType.None;
    					settings.VisiblePaginationLinksCount = 6;
    					settings.ItemsPerPage = 24;
    					settings.PreloadedPagesCount = 3;
    					settings.ShowAllItemInGroupingSelectLists = true;
    				}).GetHtml()
    
    			<div id="center">
    				@Html.ActiveQueryBuilder().SubQueryNavigationBar(settings =>
    					{
    						settings.UnionNavBarVisible = true;
    					}).GetHtml()
    				@Html.ActiveQueryBuilder().Canvas(settings =>
    					{
    						settings.AllowLinkManipulations = ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server.Models.LinkManipulations.Allow;
    						settings.DefaultDatasourceWidth = "auto";
    						settings.Direction = ContentDirection.LeftToRight;
    						settings.DisableDatasourcePropertiesDialog = false;
    						settings.DisableLinkPropertiesDialog = false;
    						settings.DisableQueryPropertiesDialog = false;
    						settings.MaxDefaultDatasourceHeight = "144";
    						// settings.FieldListOptions
    					}).GetHtml()
    				@Html.ActiveQueryBuilder().Grid(settings =>
    					{
    						settings.OrColumnCount = 2;
    					}).GetHtml()
    				@Html.ActiveQueryBuilder().StatusBar().GetHtml()
    			</div>
    			<div class="clear">
    			</div>
    		</div>
    	</div>
    	@Html.ActiveQueryBuilder().SqlEditor().GetHtml()
    </div>

    The initialization code should be placed in the ActiveQueryBuilder.settings.Init event handler. There you should create Metadata and Syntax provider components and link these providers to the QueryBuilder object by setting the MetadataProvider and SyntaxProvider properties. Define a proper database connection object as a source for the Metadata provider or load metadata from the XML file.

    The Init event handler can get access to two object references to get access to their methods and properties: (sender as SessionStoreItem).QueryBuilder and (sender as SessionStoreItem).PlainTextSQLBuilder.

    The rest of the properties provided in the code above are initialized with their default values, they are listed here for reference, so you can remove them in your code unless you want to initialize them with some other value. The complete property reference is available here. Note that this reference was generated for the ASPX view engine. The properties and events of the QueryBuilderControl ASPX control can be accessed via the ActiveQueryBuilder helper in Razor.

    Targeting the project for .NET Framework 4.5, you should avoid setting of the HttpCompressionEnabled property to True. You can turn on HTTP compression in IIS as described here

  8. That's all! Now you can run your application.

Mini F.A.Q.: Getting and setting the SQL query text

  • To retrieve the query text from the query builder use the following code:

    protected void btnGetQuery_Click(object sender, System.EventArgs e)
    {
    	QueryBuilder queryBuilder = SessionStore.Current.QueryBuilder;
    	PlainTextSQLBuilder sqlBuilder = SessionStore.Current.PlainTextSQLBuilder;
    
    	string plainSQL = queryBuilder.SQL; // unformatted text
    	string formattedSQL = sqlBuilder.SQL; // formatted text
    }
  • To retrieve the query text from the text editor, use the following code:

    protected void btnGetQuery_Click(object sender, System.EventArgs e)
    {
    	string textSQL2 = SessionStore.Current.ClientSQL;
    }

    If you want to parse the final modifications made to SQL text by the end-user before the query execution, additionaly use the following code:

    try
    {
    	SessionStore.Current.QueryBuilder.SQL = SessionStore.Current.ClientSQL;
    
    	// execute the query here
    }
    catch (Exception ex)
    {
    	MessageBox.Show(ex.Message, "Parsing error");
    }
    
  • To assign SQL query text to the control use the following code:

    protected void btnSetQuery_Click(object sender, System.EventArgs e)
    {
    	SessionStore.Current.QueryBuilder.SQL = "SELECT * FROM ...";
    }

    If you want to do this without reloading a web page, additionaly use the following JavaScript code:

    QB.Web.Application.fullUpdate();
  • To update the SQL text on the client use the following JavaScript code:

    QB.Web.Application.importSQL('Select * From ...');
    QB.Web.Application.refreshSql();

1.6. Quick Start Guide for Java edition

  1. Drop com.adbs.querybuilder.QueryBuilder on a form using a visual form designer or create an instance programmatically.

    
    import com.adbs.querybuilder.*;
    ...
    QueryBuilder queryBuilder1 = new QueryBuilder();
  2. Add any text control on your form (for example, JTextPane). Handle its Focus event and put changed query text to the query builder.

    
    jTextPane1.addFocusListener(new FocusAdapter()
    {
       public void focusLost(FocusEvent evt)
       {
          try
          {
             queryBuilder1.setSQL(jTextPane1.getText());
          }
          catch (QueryBuilderException ex)
          {
             JOptionPane.showMessageDialog(this, ex.getMessage());
          }
       }
    });
  3. Create PlainTextSQLBuilder instance and bind it to the query builder instance.

    
    PlainTextSQLBuilder sqlBuilder = new PlainTextSQLBuilder();
    sqlBuilder.setQueryBuilder(queryBuilder1);
  4. Handle SQLUpdated event of the PlainTextSQLBuilder to receive notification about query text changes.

    
    sqlBuilder.addSQLUpdatedEventListener(new SQLUpdatedEventListener()
    {
       public void sqlUpdatedEventOccurred(SQLUpdatedEvent event)
       {
          try
          {
             // set the query text to a text control
             jTextPane1.setText(sqlBuilder.getSQL());
          }
          catch (QueryBuilderException ex)
          {
             JOptionPane.showMessageDialog(this, ex.getMessage());
          }
       }
    });
  5. Create metadata provider and supply it with SQL server connection. Set corresponding syntax provider and call the RefreshMetadata method to load objects from a database.

    
    try
    {
       JdbcMetadataProvider metadataProvider = new JdbcMetadataProvider();
       Class.forName("com.mysql.jdbc.Driver");
       metadataProvider.setConnection(DriverManager.getConnection("jdbc:mysql://host:port/database", "user", "password"));
    
       MySQLSyntaxProvider syntaxProvider = new MySQLSyntaxProvider();
    
       queryBuilder1.setSyntaxProvider(syntaxProvider);
       queryBuilder1.setMetadataProvider(metadataProvider);
    
       queryBuilder1.refreshMetadata();
    }
    catch (ClassNotFoundException ex)
    {
       JOptionPane.showMessageDialog(this, "Failed to load JDBC driver: \n" + ex.getMessage());
    }
    catch (Exception ex)
    {
       JOptionPane.showMessageDialog(this, ex.getMessage());
    }
  6. That's all! Now you can run your application.

1.7. Quick Start Guide for VCL edition (Delphi)

  1. Place the TacQueryBuilder component on the form.

  2. Place required metadata and syntax provider components on the form. Define database connection for the metadata provider.

  3. Link the components above to the TacQueryBuilder by setting MetadataProvider and SyntaxProvider properties

  4. Place the TacSQLBuilderPlainText component on the form to get SQL code generated by the Query Builder with formatting. Link it to the TacQueryBuilder component by setting it's QueryBuilder property.

  5. Add the TMemo or any other text editing component (for example, TSynEdit) to a form.

  6. Now you should establish connection between the TacSQLBuilderPlainText and the TMemo components.
    Enter the following code to OnExit event of TMemo component:

    acQueryBuilder1.SQL := Memo1.Text;
    

     

    Enter the following code to OnSQLUpdated event of TacSQLBuilder component:

    Memo1.Text := acSQLBuilderPlainText1.SQL;
    

     

  7. Execute the following code to load metadata and activate Active Query Builder:
    acQueryBuilder1.RefreshMetadata;
    

     

  8. That's all! Now you can run your application.

    Don't forget to activate your database connection component.

1.8. Quick Start Guide for ActiveX edition

  1. Place the ActiveQueryBuilderX control on the form.

  2. Initialize the ActiveQueryBuilderX.ConnectionString property with the right ADO (OLE DB) or ODBC connection string and to turn the Connected property to True.

    You can find samples of connection strings for many popular database servers at ConnectionStrings.com. Please refer to the "OLE DB providers" sections for the samples of ADO (OLE DB) strings and the "ODBC drivers" sections for ODBC strings.

  3. If you want to let your end-users to combine SQL query text editing with visual query building, add the TextBox or any other text editor control to a form. In case of using the TextBox control you should set its MultiLine property to True.

    Now you should establish connection between the ActiveQueryBuilderX and the TextBox controls.
    Enter the following code to Leave event of TextBox control:

    ActiveQueryBuilderX1.SQL = TextBox1.Text
    

    Enter the following code to OnSQLChanged event of ActiveQueryBuilderX control:

    TextBox.Text := ActiveQueryBuilderX1.FormattedSQL
    
  4. That's all! Now you can run your application.

2. Introduction to the version 2.0

2.1. What's new in Active Query Builder 2?

Visual changes:

  • Hiding the Sub-query Tabs above the Design Pane is possible now.
  • The Database Schema Tree is capable to display database object fields now.
  • The Add Object Form lets search for database objects.
  • Now it's possible to hide the Design Pane and build queries without it.
  • Swapping the Query Structure Tree and the Database Schema Tree with each other is possible now.

API changes:

  • QueryBuilder properties are reorganized into several options groups. Read more...
  • Metadata Container is completely reworked to eliminate the need for pre-loading of metadata. All the necessary information about database structure is loaded on demand now. Container is capable to store all possible types of artefacts, such as linked servers, packages, custom aggregates, etc. Read more...
  • Metadata Structure object is made to abstract from the physical structure of the database when displaying objects in the Database Schema Tree. Read more...
  • The new Metadata Container and Structure editor allows you to setup the database objects representation for the end-user the way you like.
  • The list of used database objects and fields (QueryStatistics) can be obtained now for most of the query objects: sub-queries, datasources, query column list expressions.
  • SyncSQL property is removed. The SQL property now updates the visual controls immediately, not on the next message cycle.
  • The last but not the least: the property reference is avaialble for most parts of the component.

2.2. Upgrading from the previous major version

Installation of the Active Query Builder 2 .NET Edition does not affect on the installation of the previous major version, so they can be installed simultaneously.

To migrate your project to the Active Query Builder 2 .NET Edition you should remove the reference to the old assemblies and add the appropriate assemblies of the new version. The "2" suffix was added to the names of all assemblies, but the namespace (ActiveDatabaseSoftware.ActiveQueryBuilder) remains the same.

After that you should change the source code of your project according to changes in the new version. There are two main changes that require changes in the source code:

1. QueryBuilder properties are reorganized into several options groups. You can read more about the QueryBuilder property groups here.

We've made the Form Designer conversion command line utility that can help you to replace old properties and events in your .designer.cs and .designer.vb files. You can find the FormDesignerConverter.exe file in the installation directory. Pass the directory to recursively scan for .designer files or the file name as the first parameter. The "AQB Form Designer conversion log.txt" file will be saved to the current directory or to the Documents directory. Backup copies of modified files will be saved to the same directory with the .bak extension.

2. QueryBuilder.MetadataContainer object is re-worked completely. You can read about the new Metadata Container here. If you did not do any customizations in the metadata, you'll need just to replace the call to the QueryBuilder.RefreshMetadata() method with the QueryBuilder.InitializeDatabaseSchemaTree() method.

We've made the Metadata XML file conversion command line utility to convert XML files with metadata from the old to new format. You can find the XmlConverter.exe file in the installation directory. Pass the filename of the old XML file with metadata as the first parameter and the filename to save new XML file as the second parameter.

2.3. Property groups in Active Query Builder 2

Properties of the QueryBuilder component are grouped within the following options groups now.

  • MetadataLoadingOptions: Determines rules of loading database schema information to the Metadata Container. Definition of these rules results in limitation of the list of database objects displayed in the tree. It may also help to reduce metadata loading time for some database servers (eg. Oracle).
  • BehaviorOptions: Allows you to tune the way Active Query Builder parses and builds SQL queries. These properties determine the way Active Query Builder process SQL query parsing errors, define rules of working with joins (links between query objects), etc.
  • SQLGenerationOptions: Determines SQL query text generation rules: displaying of object names and expressions during the visual query building process.
  • PanesConfigurationOptions: Allows you to configure layout of the main parts of the component: Design Pane, Query Columns List, Database Schema Tree and Query Structure Tree.
  • AddObjectFormOptions: Determines appearance of the Add Object Form that lets you search and add objects to the query.
  • DatabaseSchemaTreeOptions: Contains various properties of the Database Schema Tree control. Determines sorting and default expand level of the tree.
  • MetadataStructureOptions: Contains various properties that affect on displaying of the Metadata Structure. There you may find properties to control grouping of objects by Database, Schema, Object Type, etc.
  • DesignPaneOptions: Contains various properties of the Design Pane, such as objects snapping and links style.
  • DataSourceOptions: Determines appearance of the objects and their field lists at the Design Pane.
  • QueryColumnListOptions: Contains various properties of the Query Column List control, such as grid columns width.
  • VisualOptions: Determine appearance of sub-query tabs and borders.

Below is the list of changes from the first version:

The QueryBuilder.AddObjectFormOptions group:

  • AddObjectFormOptions.VisualGrouping is deleted. N/A due to changes in the Add Form Options control.
  • AddObjectFormOptions.*ImageIndex bunch of properties is deleted. Use the MetadataStructureOptions.*ImageIndex properties instead.
  • AddObjectFormOptions.Show* bunch of properties is deleted. Use the MetadataLoadingOptions.MetadataFilter to filter by object types.

The QueryBuilder.CriteriaListOptions group is renamed to the QueryColumnListOptions:

  • CriteriaListOptions.CriteriaListFont is renamed to QueryColumnListOptions.Font.
  • CriteriaListOptions.ShowNullOrderingInSortTypeColumn is renamed to QueryColumnListOptions.NullOrderingInOrderBy.
  • QueryBuilder.UseCustomExpressionBuilder is moved to QueryColumnListOptions.UseCustomExpressionBuilder.
  • The AffectedColumns enum that's applicable to the UseCustomExpressionBuilder and the DisableDirectEditingIn properties is replaced with the same-named set:
    • AffectedColumns.Nowhere: AffectedColumns.None;
    • AffectedColumns.ExpressionColumn: AffectedColumns.ExpressionColumn;
    • AffectedColumns.ConditionColumns: AffectedColumns.ConditionColumns;
    • AffectedColumns.Both: AffectedColumns.ExpressionColumn | AffectedColumns.ConditionColumns.

The QueryBuilder.FieldListOptions group is renamed to the DataSourceOptions:

  • FieldListOptions.DefaultFieldListWidth is renamed to DataSourceOptions.DefaultWidth.
  • FieldListOptions.DescriptionColumnOptions.UseLongDescription is deleted. There's only the Description property left in the MetadataItem class.
  • QueryBuilder.CheckedFieldsCountInCaption is moved to DataSourceOptions.SelectedFieldsCountInCaption.
  • QueryBuilder.DiagramObjectColor is moved to DataSourceOptions.BackgroundColor.
  • QueryBuilder.FocusedDiagramObjectColor is moved to DataSourceOptions.FocusedBackgroundColor.
  • QueryBuilder.DiagramObjectFont is moved to DataSourceOptions.Font.
  • QueryBuilder.DefaultDiagramObjectWidth is moved to DataSourceOptions.DefaultWidth.
  • QueryBuilder.MaxDiagramObjectHeight is moved to DataSourceOptions.MaxHeight.
  • QueryBuilder.SortDiagramObjectFields is moved to DataSourceOptions.SortType.

The QueryBuilder.MetadataTreeOptions is splitted to the MetadataStructureOptions and the DatabaseSchemaTreeOptions:

  • MetadataTreeOptions.EnableEditing is deleted. It is not possible now to edit captions directly from the Database Schema Tree control. Use the Metadata Editor to edit captions or object names.
  • MetadataTreeOptions.ShowUser* properties are deleted. Use the MetadataLoadingOptions.MetadataFilter to filter by object types.
  • MetadataTreeOptions.ShowSystem* properties are deleted. Use the MetadataLoadingOptions.LoadSystemObjects property to filter out system objects.
  • MetadataTreeOptions.SynonymsNodeText is renamed to MetadataStructureOptions.SynonymsFolderText.
  • MetadataTreeOptions.TablesNodeText is renamed to MetadataStructureOptions.TablesFolderText.
  • MetadataTreeOptions.ViewsNodeText is renamed to MetadataStructureOptions.ViewsFolderText.
  • MetadataTreeOptions.ProceduresNodeText is renamed to MetadataStructureOptions.ProceduresFolderText.
  • MetadataTreeOptions.*NodeImageIndex properties are deleted. There's one image for all types of objects: MetadataStructureOptions.FolderImageIndex.
  • MetadataTreeOptions.SystemProceduresImageIndex is renamed to MetadataStructureOptions.SystemProcedureImageIndex.
  • MetadataTreeOptions.SystemSynonymsImageIndex is renamed to MetadataStructureOptions.SystemSynonymImageIndex.
  • MetadataTreeOptions.SystemTablesImageIndex is renamed to MetadataStructureOptions.SystemTableImageIndex.
  • MetadataTreeOptions.SystemViewsImageIndex is renamed to MetadataStructureOptions.SystemViewImageIndex.
  • MetadataTreeOptions.UserProceduresImageIndex is renamed to MetadataStructureOptions.UserProcedureImageIndex.
  • MetadataTreeOptions.UserSynonymsImageIndex is renamed to MetadataStructureOptions.UserSynonymImageIndex.
  • MetadataTreeOptions.UserTablesImageIndex is renamed to MetadataStructureOptions.UserTableImageIndex.
  • MetadataTreeOptions.UserViewsImageIndex is renamed to MetadataStructureOptions.UserViewImageIndex.
  • MetadataTreeOptions.GroupingType is replaced with the MetadataStructureOptions.GroupBy* bunch of properties.
  • MetadataTreeOptions.HideSingleDatabaseNode is deleted. Use the MetadataStructureOptions.GroupByDatabases property to disable grouping by databases.
  • MetadataTreeOptions.SortingType is moved to DatabaseSchemaTreeOptions.SortingType.
  • MetadataTreeOptions.DefaultExpandLevel is moved to DatabaseSchemaTreeOptions.DefaultExpandLevel.
  • QueryBuilder.TreeFont is moved to DatabaseSchemaTreeOptions.Font.

The QueryBuilder.QueryStructureTreeOptions group:

  • QueryStructureTreeOptions.ExpandFieldsNodes is renamed to ExpandExpressionNodes.
  • QueryStructureTreeOptions.ExpandFromNodes is renamed to ExpandDataSourceNodes.
  • QueryStructureTreeOptions.FieldsImageIndex is renamed to ExpressionsNodeImageIndex.
  • QueryStructureTreeOptions.FromImageIndex is renamed to DataSourcesNodeImageIndex.
  • QueryStructureTreeOptions.FieldImageIndex is renamed to ExpressionImageIndex.
  • QueryStructureTreeOptions.FromObjImageIndex is renamed to DataSourceImageIndex.
  • QueryStructureTreeOptions.FieldsNodeText is renamed to ExpressionsNodeText.
  • QueryStructureTreeOptions.FromNodeText is renamed to DataSourcesNodeText.
  • QueryStructureTreeOptions.ShowFields is renamed to ShowExpressions.
  • QueryStructureTreeOptions.ShowFrom is renamed to ShowDataSources.

The QueryBuilder.LinkOptions group is moved to the BehaviorOptions:

  • LinkOptions.CreateLinksByIdenticalFieldNames is renamed to BehaviorOptions.CreateLinksByIdenticalFieldNames.
  • LinkOptions.CreateLinksFromRelations is renamed to BehaviorOptions.CreateLinksFromForeignKeys.
  • LinkOptions.LinkManipulations is renamed to BehaviorOptions.LinkManipulations.
  • LinkOptions.AddLinkedObjects is renamed to BehaviorOptions.AddLinkedObjects.
  • LinkOptions.DeleteUnusedObjects is renamed to BehaviorOptions.DeleteUnusedObjects.

The QueryBuilder properties moved to the BehaviorOptions group:

  • QueryBuilder.UseAltNames is moved to BehaviorOptions.UseAltNames
  • QueryBuilder.AllowSleepMode is moved to BehaviorOptions.AllowSleepMode.
  • QueryBuilder.LoadJoinsFromWhereClause is moved to BehaviorOptions.LoadJoinsFromWhereClause.
  • QueryBuilder.FilterTabsByActiveQueryPart is moved to BehaviorOptions.FilterTabsByActiveQueryPart.
  • QueryBuilder.ParsingErrorOnUnknownObjects is moved to BehaviorOptions.ParsingErrorOnUnknownObjects.

The QueryBuilder properties moved to the SQLGenerationOptions group:

  • QueryBuilder.ObjectPrefixSkipping is moved to SQLGenerationOptions.ObjectPrefixSkipping.
  • QueryBuilder.QuoteAllIdentifiers is moved to SQLGenerationOptions.QuoteAllIdentifiers.
  • QueryBuilder.AsKeywordInExpressionAlias is moved to SQLGenerationOptions.AsKeywordInExpressionAlias.
  • QueryBuilder.AsKeywordInObjectAlias is moved to SQLGenerationOptions.AsKeywordInObjectAlias.
  • QueryBuilder.InnerKeywordInLinks is moved to SQLGenerationOptions.InnerKeywordInLinks.
  • QueryBuilder.OuterKeywordInLinks is moved to SQLGenerationOptions.OuterKeywordInLinks.

The QueryBuilder properties moved to the DesignPaneOptions group:

  • QueryBuilder.SnapDiagramObjects is moved to DesignPaneOptions.SnapToGrid.
  • QueryBuilder.SnapSize is moved to DesignPaneOptions.GridSize.
  • QueryBuilder.LinkPainter is moved to DesignPaneOptions.LinkStyle.
  • QueryBuilder.DiagramPaneColor is moved to DesignPaneOptions.BackColor.

The QueryBuilder properties moved to the MetadataLoadingOptions group:

  • QueryBuilder.MetadataFilter is moved to MetadataLoadingOptions.MetadataFilter.

The QueryBuilder properties moved to the VisualOptions group:

  • QueryBuilder.FlatBorders is moved to VisualOptions.FlatBorders.
  • QueryBuilder.ShowTabIcons is moved to VisualOptions.ShowTabIcons.
  • QueryBuilder.TabsAlignment is moved to VisualOptions.TabsAlignment.
  • QueryBuilder.TabsAppearance is moved to VisualOptions.TabsAppearance.
  • QueryBuilder.TabsImageList is moved to VisualOptions.TabsImageList.

Various properties moved to the PanesConfigurationOptions group:

  • QueryBuilder.UnionNavBarVisible is moved to PanesConfigurationOptions.UnionNavBarVisible.
  • CriteriaListOptions.CriteriaListHeight is moved to PanesConfigurationOptions.QueryColumnsPaneHeight.
  • CriteriaListOptions.CriteriaListVisible is moved to PanesConfigurationOptions.QueryColumnsPaneVisible.
  • QueryStructureTreeOptions.TreeVisible is moved to PanesConfigurationOptions.RightTreePaneVisible.
  • QueryStructureTreeOptions.TreeWidth is moved to PanesConfigurationOptions.RightTreePaneWidth.
  • MetadataTreeOptions.TreeVisible is moved to PanesConfigurationOptions.LeftTreePaneVisible.
  • MetadataTreeOptions.TreeWidth is moved to PanesConfigurationOptions.LeftTreePaneWidth.

Changes in the QueryBuilder events:

  • QueryBuilder.CriteriaListControlInitialization is deleted. Use the ControlCreated event instead.
  • QueryBuilder.Filter* events are deleted. The new events to implement this functionality will be added to the MetadataContainer soon.
  • QueryBuilder.Get*ToolTipText events are deleted. The replacement events will be added soon.
  • QueryBuilder.DiagramObjectAdded is renamed to DataSourceAdded.
  • QueryBuilder.DiagramObjectAdding is renamed to DataSourceAdding.
  • QueryBuilder.DiagramObjectDeleting is renamed to DataSourceDeleting.

2.4. Customizing Database Schema tree

Metadata Structure was introduced at the instance of many users wishing to have a possibility to define objects grouping in the Database Schema Tree independently in accordance with subject areas or with some other characteristics. Other users wanted to save grouping objects by physical database structure but to add an own folder into the tree for saving frequently used sub-queries there (for example, as virtual objects). Now it's very simple to realize such wishes.

Metadata Structure object can be accessed via the QueryBuilder.MetadataStructure property. In automatic mode it's responsible for grouping of objects in the tree only. You may adjust database objects grouping in the Database Schema Tree by means of the QueryBuilder.MetadataStructureOptions group of properties. However, it's easy to change the structure beyond recognition programmatically or by means of metadata editor.

Like a usual tree, the metadata structure consists of nodes any of which can be static or dynamic. A node is static when it leads to appearance of only one node at the current level of the Database Schema Tree. A static node can be just a node for grouping of objects, or it can represent a definite object of the metadata container. Provided that this object can be not only a database object, but also a database, a schema, etc. While adding such a node into the tree, it'll expand the whole underlying database structure automatically (if such expansion is not disabled by the AllowChildAutoItems property). Dynamic node is actually a node with MetadataFilter. Addition of dynamic node into the structure leads to appearance of all objects (meeting the filter conditions) from the container into the appropriate Database Schema Tree node.

The Metadata Editor lets add three types of nodes: Folder, Object and Filter. The first two types are static nodes which differ from each other in the icon only. Later, the node icon can be substituted for any other from the available icons list. Filter is a dynamic node. You may learn more about metadata filters here.

The new Metadata Structure demo project provides the necessary code samples of building custom metadata structure. All available properties and methods of metadata structure objects are documented in detail.

2.5. Working without the Design Pane

This mode was made upon a customer request. The customer wished to hide the Design Pane and to add fields to the Query Columns Grid directly from the Database Schema Tree. This allows for easier query building process for relatively small databases in which the relations between database objects are clear enough so they won't require to be visualized.

To cope with this task, we had to teach the component to join objects in the query automatically, even if they aren't joined directly with each other. Suppose that the user builds a query for the well-known Northwind demo database in this mode, and he/she has added some fields from the Orders and the Products tables to the query wishing to get the list of orders that contain a specific product. The Orders and Products aren't joined directly, but there is a many-to-many relationship through the "Order details" table. Our task was to detect such relationships and to add the necessary intermediate objects automatically to get the correct query in result. And we had to implement a reverse scenario, in which the unnecessary intermediate tables will be removed in case the ending objects are removed from the query.

In order to allow this we've add the new properties to the component. The QueryBuilder.BehaviorOptions.AddLikedObjects property instructs to add the necessary intermediate objects automatically, while the QueryBuilder.BehaviorOptions.DeleteUnusedObjects property instructs to remove them.

To hide the Design Pane, set the QueryBuilder.PanesConfigurationOptions.DesignPaneVisible property to False. To show fields in the Database Schema Tree, set the QueryBuilder.MetadataStructureOptions.ShowFields property to True.

3. Common Questions

3.1. How to setup SQL text formatter?

To get the formatted SQL text, you should add the PlainTextSQLBuilder object and assign a QueryBuilder object to the PlainTextSQLBuilder.QueryBuilder property. After that you can handle the SQLUpdated event to get the formatted SQL text. The links below point to the lists of available properties to setup the formatting:

PlainTextSQLBuilder inherits BaseSQLBuilder which has separate sets of properties for Main query, expression sub-queries, derived tables and Common Table Expressions.

A set of formatting options for a single query is reprtesented by the SQLBuilderSelectFormat class.

3.2. What are the Syntax and Metadata providers for?

The QueryBuilder component has SyntaxProvider and MetadataProvider properties. They must be set to the instances of appropriate objects in order to work properly. This article describes the purpose of these objects.

Syntax Providers define the rules of SQL query parsing and generation for a specific database server. It also determines the queries to retrieve metadata from a specific database server using system tables. The component cannot work without a syntax provider. There are few generic syntax providers for different ANSI SQL standards that do not relate to any specific database server, thus they might be used with any database server that's compatible with the appropriate standard.

The AutoSyntaxProvider may be used if you don't know which database server the user will be connected to. This syntax provider will send a series of server specific queries to detect the underlying database server. After successful detection it will act as appropriate database server syntax provider; otherwise it will act as Universal syntax provider.

The UniversalSyntaxProvider is recommended to work with those database servers only for which Active Query Builder does not provide a special syntax provider. The Universal syntax provider supports only ANSI SQL/92 syntax and additionaly 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 lacks support of specifiс database server SQL syntax extensions.

Metadata Providers are intended to link Active Query Builder to specific data access components. Their main task is to execute the metadata retrieval queries sent by the component. The Metadata provider does not create necessary database connectivity objects, but an instance of appropriate database connection object should be assigned to the Connection property of the a 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 by means of metadata provider.

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.

3.3. How to retrieve result SQL query text?

Since the retrieval of the resulting SQL query text is the main task of the component, it can be performed by different ways subject to the current situation and needs.

  1. QueryBuilder component properties

    The QueryBuilder.SQL property determines the retrieval of the ordinary unformatted query text. To track any changes of this property you should use the QueryBuilder.SQLUpdated event. To disable the firing of this event during the programmatic change of the query, use BeginUpdate and EndUpdate methods.

    The SQL property also lets you load the query into the component. The component needs the SQL text only to load the query. Just assign the SQL text to the QueryBuilder.SQL property and the user will get its visual presentation on the screen.

    Still there's another QueryBuilder.LayoutSQL property in the component. It allows to save and to load the location of objects within the diagram along with the query text to save the layout between work sessions.

    If you need to analyze the query immediately after its assignment, for example, while loading it from the file, use the QueryBuilder.SyncSQL property. In this case the update of internal query representation objects and query statistics will occur immediately after the assignment.

    Some notes:

    • After the assignment of the query text to SQL and LayoutSQL properties, the update of the internal query representation objects won't occur immediately but asynchronously in the next message processing cycle. The SQLUpdated event will be fired after the update. This was made to allow the user work with both the SQL text and graphic query presentation simultaneously using the text editor visible at the same time on a form to avoid impacts during possible simultaneous programming and visual change of the query, but now these problems are gone. In the next major version all SQL assignment properties will work synchronously.
    • The SQLUpdated event won't occur if you try to load the query text identical to the query that is loaded into the component already. While assigning the query text to any properties described above, the component is trying to parse the query text. During the parsing process all elements irrelevant for the parser (spaces, line breaks and even comments) are ignored. And the so-called "abstract syntax tree" (AST) is being built from the query text. In general, AST is a set of tokens. After that this set is compared with the one loaded into the component before, and if they equal, the substitution of the old tree for the new one doesn't occur. Therefore the multiple assignment of the same syntactically correct query won't lead to the multiple firing of the SQLUpdated event, but the multiple assignment of the incorrect query will lead to the multiple firing of the SQLError event.

     

  2. PlainTextSQLBuilder component properties

    The main task of the PlainTextSQLBuilder component is acquisition of the query text formatted in accordance with user-specified settings, though it has some other useful properties in the last versions of the component.

    To work with PlainTextSQLBuilder you need assign an instance of the query building component to the PlainTextSQLBuilder.QueryBuilder property. After that you'll be able to get the formatted query text from the PlainTextSQLBuilder.SQL property.

    In the general case you can use two and more PlainTextSQLBuilder components connected to one QueryBuilder component. It might be useful when you want to show the user the query text that is easier to read and understand, and when you want to use the unmodified variant of the same query for execution on the server. To ease understanding of queries by an end user you can use Alternate names and Virtual objects and fields. While using these features the control over the query text generation is performed with the help of UseAltNames, ExpandVirtualObjects and ExpandVirtualFields properties.

    The last version of the component lets change the sub-queries text independently of the main query. Surely, when we use visual builder, we may talk about altering of the active (currently visible) sub-query text. For this purpose the PlainTextSQLBuilder component has the PlainTextSQLBuilder.SQLUpdated event which is similar to the QueryBuilder.SQLUpdated event, but it is additionally fired on changing of the currently active sub-query when PlainTextSQLBuilder.TargetQueryPart property is set to SubQuery or UnionSubQuery value. In such a mode the assignment to the PlainTextSQLBuilder.SQL property will lead to the change of the currently active sub-query text only but not of the whole query. You may read about this feature in detail here.

3.4. What is the Sleep mode and how it helps to deal with unsupported SQL statements?

The Sleep mode is made to allow the end-users to write SQL statements that are not supported by Active Query Builder (DML, DDL) in the text editor.

This mode is enabled by default, but might be disabled by setting the QueryBuilder.AllowSleepMode property to False. Switching to this mode is done automatically on assigning a not supported SQL query to the query builder. During this assignment no parsing exceptions are thrown, but the QueryBuilder.SleepModeChanged event is fired.

Being in the Sleep Mode the component shows empty diagram pane and query columns grid. It may be activated by assigning the SELECT SQL statement to it or by starting to build a new query visually. On starting to build a new query visually being in the sleep mode, the QueryBuilder.QueryAwake event is fired. By handling this event you may ensure that the user really wants to start build a new query (this action will replace the previous non-supported SQL statement with a new one) and allow or deny switching to the normal mode.

3.5. Working with parameters

It is possible to include parameters in the following notations to the query:

= :param (named notation)

= ? (unnamed notation)

= @Param (for MS SQL only)

Active Query Builder does not execute SQL queries, but it provides all necessary information about parameters that were used in the query. You should examine the QueryBuilder.Parameters collection before query execution and display a dialog prompt for parameter values based on the information from this collection. An example of such usage is provided in the sample projects that included in the installation package.

Please note that some data access components may not support all of the mentioned parameter notations; please refer to the documentation to your data access components for details.

Please note that the Parameters collection is not available in the Free version. Use the Trial or Standard version to work with parameters.

3.6. Determination of unknown objects in the query


Important:

This article might be obsolete!

It is valid for Java, ActiveX, VCL editions, and for previous version of Active Query Builder .NET Edition (1.x).

The appropriate article for Active Query Builder 2 .NET Editon can be found here: How to detect incorrect object and field names in the query?.


Active Query Builder can parse queries with objects not loaded in the Metadata Container. To determine if query contains unknown objects, you may examine the QueryBuilder.QueryStatistics.UsedDatabaseObjects collection. Those items of this collection that have a null reference in the MetadataObject property were failed to find a corresponding object in the Metadata Container. The same way you may check for unknown fields using the QueryBuilder.QueryStatistics.UsedDatabaseObjectColumns collection and checking for a null reference in the MetadataField property.

For some reasons you may deny parsing of such queries by setting the QueryBuilder.ParsingErrorOnUnknownObjects property to True.

3.7. Validating SQL query (syntax analysis only)

To validate a query against specific SQL syntax with Active Query Builder .NET Edition, use the the ValidateQuery() method of appropriate syntax provider:

MSSQLSyntaxProvider syntax = new MSSQLSyntaxProvider();
string errorInfo = "";

if (!syntax.ValidateQuery("select * from Orders", ref errorInfo))
{
   MessageBox.Show(errorInfo);
}

Having VCL or ActiveX Edition of Active Query Builder, you may assign SQL query text to the QueryBuilder.SyncSQL property in a try-except (try-catch) block.

try
  acQueryBuilder.SQL:='select * from Orders';
except
  on e:EacSQLError do
    ShowMessage('Unexpected token "'+e.ErrorTokenValue+'" in line '+IntToStr(e.ErrorPos.line);
end;

Please note that Active Query Builder does not perform validation against specific database context, i.e. presence of table fields, function parameters, constants, etc. We believe that the best and only possible validator is the database server itself.

The fastest way to do that is to execute the query without data retrieval by calling the following method:

SqlCommand.ExecuteReader(CommandBehavior.SchemaOnly).

Of course, you can check if such database objects and fields are present in the database (using the QueryBuilder.QueryStatistics set of collections), but still there are many things we can recognize if a query is correct in terms of specific database or not.

You may instruct the component to generate parsing error for queries with unknown database objects using the QueryBuilder.ParsingErrorOnUnknownObjects property

Active Query Builder can parse SELECT statements only. For the rest of statements, it switches to the Sleep Mode. You may deny switching to this mode by setting the QueryBuilder.AllowSleepMode to false. Also you may set the event handler for the SleepModeChanged to perform necessary actions.

3.8. Database server autodetection (SQL syntax)

Use the AutoSyntaxProvider to detect database syntax automatically.

By setting this object as syntax provider for a query builder object, database syntax will be detected on retrieving metadata from your database (using RefreshMetadata method).

You do not have to change syntax provider after detection. AutoSyntaxProvider will act as detected syntax provider. You may know the result of database server detection by examining the DetectedSyntaxProvider property.

To detect database syntax without metadata retrieval, use the following code:

autoSyntaxProvider1.DetectSyntaxProvider(queryBuilder1.SQLContext);

 

Auto syntax provider detects database server using live database connection, by sending server-specific queries and analyzing result. It can not detect database syntax by analyzing arbitrary SQL queries.

 

For the ActiveX Edition, use the "SQLSyntax = xsAuto" setting and examine the UsedSQLSyntax property.

4. Designing user-friendly query building environment

4.1. Alternate database object and field names

The Alternate Names feature for both database objects and their fields lets completely substitute real names of objects for clearer descriptive names.  So it's possible to simplify understanding of database structure and consequently building of queries to end users. 'Complete substitution' means substitution of objects names in both user interface, SQL expressions and in the query text which can be edited manually by end user along with visual query building. Active Query Builder lets assign an alternate name to every object and field of the database object loaded to the metadata container. On parsing a query, the component is able to recognize both real and alternate names of fields. Therefore this component is able to build visual presentation of a query with both types of names. This allows represent to an end user clearer presentation of existing queries.

Generation of the query text may be made using both real and alternative names; the UseAltNames property of PlainTextSQLBuilder component determines what names to use. Whereas several PlainTextSQLBuilder components may be connected to one QueryBuilder component, you may get two variants of query text simultaneously: one for an end user and another for executing query on the server. The same named property of the QueryBuilder component is responsible for displaying of real or alternate names in user interface.

Some customers even use this peculiarity for refactoring in database structure running the suite of existing queries through the component and getting as a result queries with new names of fields and objects.

It is possible to assign alternate names in several ways.

  1. Save metadata into XML file, add alternate names into it (manually using the "alt_name" tag for fields and objects or with the help of the metadata editor) and then load metadata from this file instead of retrieving metadata from the connection with database.

  2. Use the QueryBuilder.MetadataContainer MetadataObjectLoaded and ObjectMetadataLoaded events to assign alternate names to objects and their fields (respectively) during their loading to the metadata container. For example, you may use descriptions of the objects being loaded by the component from database as alternate names.

4.2. Virtual objects and fields

Virtual fields and objects are the feature letting simplify building of queries with the use of frequently used SQL expressions, derived tables and correlated sub queries. The idea is to substitute derived tables for virtual objects which won't differ from ordinary database objects for the end user. Substitution of such objects for derived tables is performed seamlessly for the end user same way as alternate names.

For example, if you have a possibility to save queries for further use in your software, you may add them into the metadata container as virtual objects therefore having provided their repeated use in other queries.

In the same way complex SQL expressions and correlated sub queries may be shown to the end-user as usual fields names. For example, we may add virtual field "Customer" into the Orders table having assigned the expression "(SELECT c.Customer_Name FROM Customers c WHERE c.Customer_Id = Orders.Customer_Id)" for it. Then this expression will be correctly converted and substituted into the final query for execution on the database server.

To get the query text for execution on the database server it's necessary to make an additional PlainTextSQLBuilder component, having set its properties ExpandVirtualFields and ExpandVirtualObjects to True. As opposed to alternate names, where real names in the query may be converted into alternate ones and inversely, inverse conversion of SQL expressions into virtual objects and fields is impossible.

You may add virtual objects and fields to the metadata container in the same way as usual objects and fields, additionally having assigned SQL expression (being substituted by the given field or object) to the Expression property.

4.3. Modification of sub-queries independently of the main query

Now it is possible to modify the sub-query SQL text independently of the main query. The UnionSubQuery.SQL and SubQuery.SQL properties can be used to read and write the sub-query SQL text.

In addition to this feature we've made the option to filter the sub-query tabs in visual user interface in order to show only the sub-queries tabs that were used in the currently active sub-query. Another option is to let the PlainTextSQLBuilder retrieve the formatted text of the currently active sub-query. These options can be used independently, but supplement each other very well. Using both options the user will be able to view and edit the currently active sub-query as an independent query and easily get back to the main query when the editing is finished. Working in the sub-query editing mode, the programmer should assign the user edited query text to the PlainTextSQLBuilder.SQL property as it's always mapped to the active sub-query in this mode. And use the PlainTextSQLBuilder.SQLUpdated event to update the text editor as this event is fired not only when the query is changed in the Query Builder but also when the active sub-query is changed.

There are two variants of sub-query editing mode: editing single union sub-queries or editing sets of sub-queries with unions. I.e. if a sub-query contains unions, the user will edit each union sub-query independently in the first case and the whole set of union sub-queries in the second case. Thus the possible values of the PlainTextSQLBuilder.TargetQueryPart property that turn on this mode are the following:

  • Query (default) – instructs to generate SQL text for the whole query;
  • SubQuery – instructs to generate SQL text for the currently active sub-query with unions;
  • UnionSubQuery – instructs to generate SQL text for the currently active union sub-query.

The QueryBuilder.FilterTabsByActiveQueryPart property with the same possible values instructs the component to filter sub-query tabs accordingly.

The "Show Query By Parts" demo project illustrates this functionality.

5. Localizing Active Query Builder

5.1. Available languages

Available languages for .NET and Java Editions:

  • Arabic (Saudi Arabia)
  • English
  • Spanish
  • French
  • German
  • Italian
  • Portuguese (Brazil)
  • Polish
  • Swedish
  • Turkish
  • Russian

Available languages for VCL and ActiveX Editions:

  • English
  • Spanish
  • French
  • German
  • Italian
  • Chinese
  • Japanese
  • Portuguese (Brazil)
  • Czech
  • Swedish
  • Bulgarian
  • Korean
  • Russian
  • Turkish

5.2. Selecting language in run-time

There are two ways to select language. First is to select it from the list of available languages. If you can not find your language in this list, you may localize the component by yourself and load your language from the language file.

  • Selecting language in VCL Edition (Delphi):

     
         // use this unit to get access to acQBLanguageManager global object
         uses acQBLocalizer;
     
         // show languages
         for i:=0 to acQBLanguageManager.LanguagesCount-1 do
         begin
             ShowMessage(acQBLanguageManager.Language[i].LanguageName);
         end;
     
         // set built-in default language
         acQBLanguageManager.CurrentLanguageIndex := -1;
     
         // set language #0
         acQBLanguageManager.CurrentLanguageIndex := 0;
     
         // load and apply custom language from file (lng is integer)
         lng := acQBLanguageManager.AddLanguageFromFile('MyLanguage', 'MyLang.lng');
         acQBLanguageManager.CurrentLanguageIndex := lng;
     
         // load and apply language from resource 
         // (lng is integer, MyLang is resource name, LANGUAGES is resource type)
         lng := acQBLanguageManager.AddLanguageFromResource('MyLanguage', 'MyLang', 'LANGUAGES');
         acQBLanguageManager.CurrentLanguageIndex := lng;
     
         // load and apply language from string (lng is integer, sLanguage is string)
         lng := acQBLanguageManager.AddLanguageFromString('MyLanguage', sLanguage);
         acQBLanguageManager.CurrentLanguageIndex := lng;
     
         // load and apply language from TStrings (lng is integer, ssLanguage is TStrings)
         lng := acQBLanguageManager.AddLanguageFromStrings('MyLanguage', ssLanguage);
         acQBLanguageManager.CurrentLanguageIndex := lng;
    
  • Selecting language in ActiveX Edition (VB.NET):

     
        ' show languages
        For i = 0 To ActiveQueryBuilderX1.Localizer.Count - 1
            MsgBox "Language #" + Str(i) + ": " + ActiveQueryBuilderX1.Localizer.LanguageName(i)
        Next i
     
        ' set built-in default language
        ActiveQueryBuilderX1.Localizer.CurrentLanguage = -1
     
        ' set language #0
        ActiveQueryBuilderX1.Localizer.CurrentLanguage = 0
     
        ' load and apply custom language from file
        lng = ActiveQueryBuilderX1.Localizer.AddLanguageFromFile("MyLanguage", "MyLang.lng")
        ActiveQueryBuilderX1.Localizer.CurrentLanguage = lng
     
        ' load and apply custom language from string (sLanguage is string)
        lng = ActiveQueryBuilderX1.Localizer.AddLanguageFromString("MyLanguage", sLanguage)
        ActiveQueryBuilderX1.Localizer.CurrentLanguage = lng
    
  • Selecting language in ActiveX Edition (C#):

    
        // show languages
        for (int i = 0; i < axActiveQueryBuilderX1.Localizer.Count; ++i)
        {
            MessageBox.Show("Language #" + i.ToString() + ": " + 
            axActiveQueryBuilderX1.Localizer.get_LanguageName(i));
        }
     
        // set built-in default language
        axActiveQueryBuilderX1.Localizer.CurrentLanguage = -1;
     
        // set language #0
        axActiveQueryBuilderX1.Localizer.CurrentLanguage = 0;
     
        // load and apply custom language from file
        int lng = axActiveQueryBuilderX1.Localizer.AddLanguageFromFile("MyLanguage", "MyLang.lng");
        axActiveQueryBuilderX1.Localizer.CurrentLanguage = lng;
     
        // load and apply custom language from string (sLanguage is string)
        int lng = axActiveQueryBuilderX1.Localizer.AddLanguageFromString("MyLanguage", sLanguage);
        axActiveQueryBuilderX1.Localizer.CurrentLanguage = lng;
    
  • Selecting language in .NET Edition (C#):

     
        // How to load a language from an external source:
     
        // load language from specified file
        ActiveDatabaseSoftware.ActiveQueryBuilder.Helpers.Localizer.LoadLanguageFromFile(string languageFile);
        // load language from a XML string
        ActiveDatabaseSoftware.ActiveQueryBuilder.Helpers.Localizer.LoadLanguageFromString(string xmlString);
        // load language from a stream containing an XML string
        ActiveDatabaseSoftware.ActiveQueryBuilder.Helpers.Localizer.LoadLanguageFromStream(Stream languageStream);
     
        // How to get a list of available languages:
        foreach (string lng in ActiveDatabaseSoftware.ActiveQueryBuilder.Helpers.Localizer.Languages)
        {
            Trace.WriteLine(lng);
        }
     
        // The language list always contains "Auto" and "Default" strings.
        // - "Auto" means the language will be detected automatically according to CultureInfo specified for 
        //   UI culture of the current thread (System.Threading.Thread.CurrentThread.CurrentUICulture).
        // - "Default" means usage of default strings containing in embedded resources.
     
        // How to set current language:
        queryBuilder1.Language = "es-MX";
        // If specified language doesn't exist, Localizer will try to fallback to the base language ("es")
        // If it doesn't exist too, the Localizer will use the "Default" language.
    
  • Selecting language in ASP.NET Edition (C#):

    To get list of available languages, use the code above for WinForms .NET Edition.

    Use the QueryBuilderControl.Language property to specify required language for the control. Language will be detected automatically according to the "Accept-Language" HTTP request header field if this property is set to "Auto".

    To load language from external language file, place it to the "Language Files" at the root of your web site and set the QueryBuilderControl.UseCustomLanguageFiles. Language file will be determined according to the Language property value.

  • Selecting language in Java Edition:

     
        // How to load a language from an external source:
     
        // load language from specified file
        com.adbs.utils.Helpers.localizer.loadLanguageFromFile(String languageFile);
        // load language from a XML string
        com.adbs.utils.Helpers.localizer.loadLanguageFromString(String xmlString);
        // load language from a stream containing an XML string
        com.adbs.utils.Helpers.localizer.loadLanguageFromStream(InputStream languageStream);
     
        // How to get a list of available languages:
        for (String lng : com.adbs.utils.Helpers.localizer.languages)
        {
            System.out.println(lng);
        }
     
        // The language list always contains "Auto" and "Default" strings.
        // - "Auto" means the language will be detected automatically according to information
        // from java.util.Locale.getDefault()
        // - "Default" means usage of default (English) strings containing in embedded resources.
     
        // How to set current language:
        queryBuilder1.setLanguage("es-MX");
        // If specified language doesn't exist, Localizer will try to fallback to the base language ("es")
        // If it doesn't exist too, the Localizer will use the "Default" language.
    

5.3. Localizing Active Query Builder .NET and Java Edition

If you didn't find your language in the list of supported languages, you may create your own localization.

Please find the 'en.xml' file in the installation directory and translate it to your language using any XML editor you like.

The <language id=""> attribute value should be set according to your language code. You may find the list of codes here.

5.4. Localizing Active Query Builder VCL and ActiveX Edition

We recommend the following way to localize Active Query Builder VCL and ActiveX Edition:

  • Download and install the component and find the 'English.lng' file in 'Languages' directory.

  • Download and install IniTranslator application for fast and easy translation.

  • Open 'English.lng' file as Original. We also recommend you to set highlighted IniTranslator options as shown on the screenshot:
    IniTranslator options

  • Now you may start to translate. Below are the form screenshots intended to ease translation process. Each section of .lng file represents a form (window), except the [Active Query Builder] section that contains common strings used in Active Query Builder (popup menus, grid captions, error messages, etc.).

  • If you find that some of translated strings doesn't fit the form, try to cut the string, or tell us about this case so we can allocate more space on the form for it.

  • There are two strings in the [General Settings] section that should be set in exact matching to your language: 'CharSet' and 'BiDiMode'. First item specifies required character set of the font and second specifies the bi-directional mode of controls. All possible values are listed in the comments in 'English.lng' file, so all you need is to uncomment settings that fit your language and copy them to your translation file.

Sections of .lng file

[TacDatasourcePropsForm] - This form represents properties of any data source object in the query.
Datasource Properties

[TacLinkPropsForm] - This form represents options of JOIN clause.
Link Properties

[TacQueryPropsFbForm], [TacQueryPropsIbForm] - These forms represent common properties of InterBase and Firebird SQL queries.
InterBase/Firebird Query Properties

[TacQueryPropsSbForm] - This form represents common properties of Sybase SQL queries.
Sybase Query Properties

[TacQueryPropsMSForm] - This form represents common properties of MS SQL Server queries.
SQL Server Query Properties

[TacQueryPropsMYForm] - This form represents common properties of MySQL queries.
MySQL Query Properties

[TacAddObjectForm] - This form is used to add objects to the query.
Add Object Form

[TacProcedureParams] - This form required to get input parameter values of stored procedures and functions.

Actually, this will be enough to localize end-user interface of Active Query Builder, so you may stop here, if you don't want to show any of setup windows listed below to end-users. Just copy the rest of the original file to your translation, and that's all.

[TfMetadataContainerEditor] - This form is used to manage objects stored in Metadata Container.

Metadata Container Editor

[TfacQBMetadataContainerLoad] - This wizard is used to load metadata to Metadata Container. It consists of the following steps:

Step 1. Welcome message.
Load Metadata Wizard Step 1

Step 2. Selecting SQL Syntax.
Load Metadata Wizard Step 2

Step 3. Selecing Connection Type.
Load Metadata Wizard Step 3

Step 4. Specifying connection options.
Load Metadata Wizard Step 4

Step 5. Selecting databases.
Load Metadata Wizard Step 5

Step 6. Specifying additional fintration.
Load Metadata Wizard Step 6

Step 7. Loading Metadata.
Load Metadata Wizard Step 7

[TfrAcQBBDEConnectionEdit] - BDE Connection Options. This frame is used internally in the Load Metadata Wizard on the fourth step.
BDE Connection Options

[TfrAcQBADOConnectionEdit] - ADO Connection Options. This frame is used internally in the Load Metadata Wizard on the fourth step.
ADO Connection Options

[TfrAcQBdbExConnectionEdit] - dbExpress Connection Options. This frame is used internally in the Load Metadata Wizard on the fourth step.
dbExpress Connection Options

[TfrAcQBIBExConnectionEdit] - IbExpress Connection Options. This frame is used internally in the Load Metadata Wizard on the fourth step.
IbExpress Connection Options
IbExpress Connection Options

[TfrFilterEditor], [TfEditMetadataFilter] - This form is intended to define metadata filters. You should call the EditMetadataFilter method to open this form.
Edit Metadata Filter Form

[TfmAQBOptions] - This form is the second page of the Properties window. You should call the EditProperties method to open this form.
Visual Options

[TSQLFormatFrame], [TfmSQLBuilderOptions] - This form and its frame are the third page of the Properties window.
SQL Builder Options

[TfmAQBOptions2] - This form is the first page of the Properties window.
Non-visual Options