Create a new ASP.NET Core project for .NET Core or .NET Framework.

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

Prerequisites

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

The whole list of supported database connectivity packages and appropriate connectors can be found here.

Embedding Visual Query Builder

1. The typical CSHTML code can be taken from this article or found in the demo projects for your development environment. You can use Razor HTML tags or built-in ASP.NET Core tag helpers as below.

   @model ActiveQueryBuilder.Web.Server.QueryBuilder
   
   <query-builder query-builder="@Model"></query-builder>
   <div class="qb-ui-layout">
       <div class="qb-ui-layout__top">
           <div class="qb-ui-layout__left">
               <div class="qb-ui-structure-tabs">
                   <div class="qb-ui-structure-tabs__tab">
                       <input type="radio" id="tree-tab" name="qb-tabs" checked />
                       <label for="tree-tab">Database</label>
                       <div class="qb-ui-structure-tabs__content">
                           <object-tree-view query-builder="@Model"></object-tree-view>
                       </div>
                   </div>
               </div>
           </div>
           <div class="qb-ui-layout__right">
               <sub-query-navigation-bar query-builder="@Model"></sub-query-navigation-bar>
               <aqb-canvas query-builder="@Model"></aqb-canvas>
               <status-bar query-builder="@Model"></status-bar>
               <grid query-builder="@Model"></grid>
           </div>
       </div>
       <div class="qb-ui-layout__bottom">
           <sql-editor query-builder="@Model"></sql-editor>
       </div>
   </div>

   Note that you must register AQB tag helpers by adding the following line to the "Views\_ViewImports.cshtml" file:

   @addTagHelper *, ActiveQueryBuilder.Web.Core.NETCore

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>();
   
           // 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. Pass an instance of the IQueryBuilderService to the constructor of your controller to get or create a new instance of the QueryBuilder object.
   While creating a new instance of it, 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 AspNetCoreDemoController : Controller
   {
       private readonly IQueryBuilderService _aqbs;
       
       // Use the IQueryBuilderService to get access to the server-side instances of QueryBuilder objects. 
       public AspNetCoreDemoController(IQueryBuilderService aqbs, IHostingEnvironment env)
       {
           _aqbs = aqbs;
           _env = env;
       }
   
       // Professional version feature: if you want to let the user simultaneously 
       // build several queries, you must assign different instance ids to them.
       // In the Standard version, this parameter is ignored.
       private string instanceId = "SimpleClient";
       
       public IActionResult Index()
       {
           // Get an instance of the QueryBuilder object
           var qb = _aqbs.GetOrCreate(instanceId, InitializeQueryBuilder);
   
           return View(qb);
       }
   
       private void InitializeQueryBuilder(QueryBuilder queryBuilder)
       {
           // Turn this property on to suppress parsing error messages when user types a non-SELECT statement.
           queryBuilder.BehaviorOptions.AllowSleepMode = false;
   
           // Assign an instance of the syntax provider which defines SQL syntax and metadata retrieval rules.
           queryBuilder.SyntaxProvider = new SQLiteSyntaxProvider();
   
           // ==== The 1st way: ==================================
           // Bind Active Query Builder to a live database connection. -- comment out to use the 2nd way
           queryBuilder.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: ==================================
           // Load MetaData from the pre-generated XML file. -- uncomment to use
   
           // Denies metadata loading requests during the work session.
           //queryBuilder.MetadataLoadingOptions.OfflineMode = true;
   
           //queryBuilder.MetadataContainer.ImportFromXML(pathToYourXMLFile);
           // ====================================================
   
           // Assign the initial SQL query text the user sees on the _first_ page load
           queryBuilder.SQL = @"Select o.Order_ID, c.ID As a1, c.First_Name, s.ID
                   From Orders o Inner Join
                       Customers c On o.Customer_ID = c.ID Inner Join
                       Shippers s On s.ID = o.Shipper_ID
                   Where o.Ship_City = 'A'";
       }
   }

    

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. Active Query Builder can be easily integrated with most of these controls providing the flexibility you need.

Please read the QueryTransformer and CriteriaBuilder guides for details.

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

   @Html.CriteriaBuilder((QueryTransformer)ViewBag.QueryTransformer).GetHtml()

    

2. Add the following initialization code to the ActionResult controller method:

   public class AspNetCoreDemoController : Controller
   {
       private readonly IQueryBuilderService _aqbs;
       private readonly IQueryTransformerService _aqts; 
       
       // Use the IQueryTransformerService to use the CriteriaBuilder control and take advantage of the QueryTransformer API. 
       public AspNetCoreDemoController(IQueryBuilderService aqbs, IQueryTransformerService aqts, IHostingEnvironment env)
       {
           _aqbs = aqbs;
           _aqts = aqts; 
           _env = env;
       }
   
       private string instanceId = "SimpleClient";
   
       public ActionResult Index()
       {
           // Get an instance of the QueryBuilder object
           var qb = _aqbs.GetOrCreate(instanceId, InitializeQueryBuilder); // see the previous code sample for the implementation
   
           // Get an instance of the QueryTransformer object
           ViewBag.QueryTransformer = _aqts.GetOrCreate(instanceId, q =>
               {
                   q.QueryProvider = qb.SQLQuery;
                   q.AlwaysExpandColumnsInQuery = true;
               });
           
           return View(qb);
       }
   }

    

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. Make sure to properly handle SQL query execution errors.

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?