Follow the steps below to embed Active Query Builder ASP.NET Edition into your project. 

Add the ActiveQueryBuilder.Web.MVC NuGet package to your project OR add the references to the following assemblies from the component installation folder:

For ASP.NET Core over .NET Framework project (requires minimal .NET Framework version 4.6.2):

• \assemblies\ActiveQueryBuilder.Core.dll
• \assemblies\ActiveQueryBuilder.Web.Server.dll
• \assemblies\ActiveQueryBuilder.Web.Core.dll

For ASP.NET Core over .NET Core project:

• \assemblies_NETCore\ActiveQueryBuilder.Core.dll
• \assemblies_NETCore\ActiveQueryBuilder.Web.Server.NETCore.dll
• \assemblies_NETCore\ActiveQueryBuilder.Web.Core.NETCore.dll

Active Query Builder requires Newtonsoft.Json assembly version 6.0 or higher. You can get it by installing the Newtonsoft.Json NuGet package or download it directly from the Newtonsoft JSON website.

You may also need to add one or more assemblies to get connected to your database(s) to retrieve metadata from it. For example, if you work with SQLite, add the assembly containing SQLite Metadata Provider:

• \assemblies\ActiveQueryBuilder.SQLiteMetadataProvider.dll

You can find the complete list of supported database connectivity packages and connectors here.

1. Add the following HTML markup to your web page:

   @model ActiveQueryBuilder.Web.Server.QueryBuilder
   @{
       ViewBag.Title = "Simple Client Rendering Demo";
   }
   
   <div class="col-md-12">
       <div id="qb"></div>
       <div class="qb-ui-layout">
           <div class="qb-ui-layout__top">
               <div class="qb-ui-layout__left">
                   <div id="treeview"></div>
               </div>
               <div class="qb-ui-layout__right">
                   <div id="navbar"></div>
                   <div id="canvas"></div>
                   <div id="statusbar"></div>
                   <div id="grid"></div>
               </div>
           </div>
           <div class="qb-ui-layout__bottom">
               <div id="sql"></div>
           </div>
       </div>
   </div>
   
   @section scripts
   {
       <script src="aqb_client.js"></script>
   
       <script>
           // identifies query builder instance in the session; must be the same on the server side
           var qbId = 'SimpleClient';
   
           $(function () {
               AQB.Web.UI.QueryBuilder(
                   qbId,  // instance identifier
                   $('#qb'),  // id or path to HTML DOM element
               );
               AQB.Web.UI.ObjectTreeView(qbId, $('#treeview'));
               AQB.Web.UI.SubQueryNavigationBar(qbId, $('#navbar'));
               AQB.Web.UI.Canvas(qbId, $('#canvas'));
               AQB.Web.UI.StatusBar(qbId, $('#statusbar'));
               AQB.Web.UI.Grid(qbId, $('#grid'));
               AQB.Web.UI.SqlEditor(qbId, $('#sql'));
   
               AQB.Web.UI.autoInit(qbId);
           });
       </script>
   }

   Alternatives: You can initialize the component on the server-side later if the query builder UI is not shown right on the page load. Here, you need to specify the second parameter to the startApplication or autoInit method. This can be either
   a) a path to the component's new instance creation method on the server (example), or
   b) a function that fires a request to create the component and handles errors (example).

    

2. Modify the "Startup.cs" file by adding the following directives:

   using Microsoft.AspNetCore.Builder;
   using Microsoft.AspNetCore.Hosting;
   using Microsoft.AspNetCore.Http;
   using Microsoft.Extensions.Configuration;
   using Microsoft.Extensions.DependencyInjection;
   using ActiveQueryBuilder.Web.Core;
   
   // ...
   public class Startup
   {
       // ...
       
       public void ConfigureServices(IServiceCollection services)
       {
           // Active Query Builder requires support for Session HttpContext unless you use the Corporate version. 
           services.AddSession();
           services.AddSingleton<IHttpContextAccessor, HttpContextAccessor>();
           services.AddScoped<IQueryBuilderInstanceFactory, QueryBuilderFactory>();
   
           // This service provides access to instances of QueryBuilder and QueryTranformer objects on the server.
           services.AddActiveQueryBuilder();
           
           // ...
       }
   
       public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
       {
           // Active Query Builder requires support for Session HttpContext.
           app.UseSession();
           
           // Active Query Builder server requests handler.
           app.UseActiveQueryBuilder();
           
           // ...
       }
   }

    

3. Create a factory to create and initialize new instances of the QueryBuilder object.
   Initialize the MetadataProvider and SyntaxProvider properties with proper objects. Define a proper DBConnection object and pass it to the MetadataProvider.Connection property or load metadata from the XML file.

       public class QueryBuilderFactory: IQueryBuilderInstanceFactory
       {
           private readonly IQueryBuilderService _qbService;
   
           public QueryBuilderFactory(IQueryBuilderService qbService)
           {
               _qbService = qbService;
           }
   
           public QueryBuilder Create(string name) // name of the instance (instance Id)
           {
               var qb = _qbService.Create(name);
   
               // specify the right instance of the syntax provider to define proper 
               // SQL syntax rules for your database server.
               // Using the Generic syntax provider is the worst option!
               qb.SyntaxProvider = new GenericSyntaxProvider();
   
               // load metadata to an instance - there are more than two ways to do this.
               // ==== The 1st way: ==================================
               // Bind Active Query Builder to a live database connection. -- comment out to use the 2nd way
               qb.MetadataProvider = new SQLiteMetadataProvider
               {
                   // Assign an instance of DBConnection object to the Connection property.
                   Connection = new SQLiteConnection(@"Data Source=C:\Northwind.db;Version=3;") 
               };
   
               // ==== The 2nd way: ==== (comment oud the first method and uncomment this part) =======
               // Load MetaData from the pre-generated XML file. -- uncomment to use
   
               // Denies metadata loading requests during the work session.
               //qb.MetadataLoadingOptions.OfflineMode = true;
   
               //qb.MetadataContainer.ImportFromXML(pathToYourXMLFile);
               // ====================================================
   
               qb.MetadataStructure.Refresh();
   
               return qb;
           }
       }

   Alternative: You can automate the initialization of the QueryBuilder object (syntax and metadata providers, database connection or metadata loading from the XML file) by using the special configuration file. This way is useful when you want to run the backend in a Docker container. Learn the details in this article.

   In the controller, you can get access to the right instance of QueryBuilder object using the IQueryBuilderService.Get(instanceId) method.

4. If you want to use the CriteriaBuilder control on your page, you will need an instance of the QueryTransformer object. Get it by passing an instance of the IQueryTransformerService to the constructor of your controller.

Embedding Criteria Builder in your data browsing UI

The QueryTransformer component and the accompanying CriteriaBuilder visual control let build a full-featured user interface to browse query result data. Note that Active Query Builder doesn't provide any means to display data, as there are plenty of free and paid controls that can cope better with this task. You can easily integrate  Active Query Builder with most of these controls that will give you the flexibility you need.

Please read the QueryTransformer and CriteriaBuilder guides for details.

1. Render the CriteriaBuilder control on the page. You can place it on the same page with the QueryBuilder control or on a separate page.

   <div id="cb"></div>
   
   @section scripts
   {
       <script src="aqb_client.js"></script>
   
       <script>
           // identifies the query builder instance in the session; must be the same on the server side
           var qbId = 'SimpleClient';
   
           $(function () { 
               // ...
   
               AQB.Web.UI.CriteriaBuilder(qbId, $('#cb'));
   
               // ...
               AQB.Web.UI.autoInit(qbId); // only once
           });
       </script>
   }

    

2. Add the following factory:

       public class QueryTransformerFactory: IQueryTransformerInstanceFactory
       {
           private readonly IQueryTransformerService _qtService;
   
           public QueryTransformerFactory(IQueryTransformerService qbService)
           {
               _qtService = qtService;
           }
   
           public QueryTransformer Create(string name) // name of the instance (instance Id)
           {
               var qb = _qbService.Get(name) ?? __________(name);
               var qt = _qtService.Create(name);
   
               qt.QueryProvider = qb.SQLQuery;
               qt.AlwaysExpandColumnsInQuery = true;
   
               return qt;
           }
       }

3. Handle CriteriaBuilder events to integrate it with your data grid:

           $(function () {
               AQB.Web.onCriteriaBuilderReady(subscribeToChanges);
           });
           
           function subscribeToChanges(cb) {
               createGrid(); // user proc to create or initialize grid
   
               cb.on(cb.Events.AfterSyncCriteriaBuilder, function () {
                     updateGridColumns(cb.columns); // user proc to update list of query columns
                     updateGridData(); // user proc to update grid data
               });
   
               cb.loadColumns(); // trigger the initial query columns loading
   
               // request data from the server when the user changes the filter
               cb.on(cb.Events.CriteriaBuilderChanged,
                   function () {
                       onCriteriaBuilderChanged(cb, updateGridData); 
                   });
           }
           
           function onCriteriaBuilderChanged(cb, callback) {
   
               // check if there aren't any incomplete conditions in the CriteriaBuilder
               if (cb.isValid()) {
               
                   // send the control state to the server to update the query
                   cb.transformSql(function () { 
                       callback(); // a callback to use (usually run) the transformed query
                   });
               } 
           }

    

4. The updateGridData procedure should perform the AJAX request to the server, which will execute the query read from the QueryTransformer.SQL property and return data to the client. Handle SQL query execution errors properly.

5. You can also let users change sorting, apply grouping and aggregations, calculate totals and paginate the resultset in your data browsing UI using the QueryTransformer API.

See the Query Results demo project for details (included in the installation package and available on GitHub).

What to read next?

• Learn how to get the SQL query text from the component and load it back.
• Learn more about Metadata and Syntax providers and how to choose the right one.
• How to prompt the end-user for query parameters?