HomeActive Query Builder - IntroductionQuick Start GuidesQuick Start Guide for ASP.NET edition: MVC 2.0 environment

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

Related Pages
This page was: Helpful | Not Helpful