Home → Active Query Builder → Quick Start Guide → Quick Start Guide for ASP.NET edition

1.2. Quick Start Guide for ASP.NET edition

1. Create a new solution and add a new project – ASP.NET Web Application.

2. Add references to the following assemblies:

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

   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.

   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

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.ActiveQueryBuilder.Web.Server"
        />
        <add path="handlers/sessonKeeper.axd" verb="*" 
           type="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server.Handlers.SessionKeeper,
           ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server"
        />
        <add path="handlers/ResourceHandler.axd" verb="*"
           type="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server.Handlers.ResourceHandler,
           ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server"
        />
     

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

     
        <add name="HttpModule" 
           type="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server.HttpModule,
           ActiveDatabaseSoftware.ActiveQueryBuilder.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.ActiveQueryBuilder.Web.Server" 
        />
        <add name="handlers/sessonKeeper.axd_*" preCondition="integratedMode"  
           path="handlers/sessonKeeper.axd" verb="*" 
           type="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server.Handlers.SessionKeeper,
           ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server" 
        />
        <add name="handlers/ResourceHandler.axd_*" preCondition="integratedMode" 
           path="handlers/ResourceHandler.axd" verb="*" 
           type="ActiveDatabaseSoftware.ActiveQueryBuilder.Web.Server.Handlers.ResourceHandler,
           ActiveDatabaseSoftware.ActiveQueryBuilder.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.ActiveQueryBuilder.Web.Server" />
     

   For MVC project, create the following empty files in the root of your web site:

   
   /handlers/exchange.axd
   /handlers/sessonKeeper.axd
   /handlers/ResourceHandler.axd
   

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.

   Active Query Builder ASP.NET Edition requires jQuery 1.9 or higher and jQueryUI 1.10 or higher.

   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>
   

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.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>

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;
   
       // ===========================================================
       // a) you can load metadata from the database connection using live database connection
       
       OleDbConnection connection = new OleDbConnection();
       connection.ConnectionString = "<your connection string here>";
   
       OLEDBMetadataProvider metadataProvider = new OLEDBMetadataProvider();
       metadataProvider.Connection = connection;
   
       queryBuilder.MetadataProvider = metadataProvider;
   
       // call the RefreshMetadata to load metadata from a database connection 
       queryBuilder.RefreshMetadata();
   
       // ===========================================================
       // b) or you can load metadata from the pre-generated XML file
       
       XmlDocument doc = new XmlDocument();
       doc.Load(Server.MapPath(ConfigurationManager.AppSettings["XmlMetaData"]));
   
       queryBuilder.OfflineMode = true;
       queryBuilder.MetadataContainer.LoadFromXML(doc.OuterXml);
   }

7. 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");
       }
   

8. 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();

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