Lesson 1 - Add the Query Builder to a Project | ASP.NET MVC Extensions

Nov 08, 2021
5 minutes to read

This lesson illustrates how to create a new ASP.NET MVC application in Microsoft Visual Studio and add a Query Builder control to it.

Create a New Application

Create a new project by selecting FILE | New | Project… in the main menu or by pressing CTRL+SHIFT+N.
Select the DevExpress v22.1 Web App Template Gallery and click Next.
Specify the project name and location and click Create.
In the invoked DevExpress Template Gallery, select Web Application under the ASP.NET MVC category and click Run Wizard.
In the DevExpress ASP.NET MVC Project Wizard, switch to the Suites page, enable the Query Builder suite, and click Create Project.

This creates an application that can use DevExpress ASP.NET MVC Extensions and registers the styles and scripts in the _Layout.cshtml file.

_Layout.cshtml

@Html.DevExpress().GetStyleSheets(
    ...
    new StyleSheet { ExtensionSuite = ExtensionSuite.QueryBuilder }
)
@Html.DevExpress().GetScripts( 
    ...
    new Script { ExtensionSuite = ExtensionSuite.QueryBuilder }
)

Add a Query Builder

Insert the QueryBuilder MVC extension in one of the following ways:

Use the DevExpress MVC Extension Wizard

Open the View file (Index.cshtml), right-click anywhere on the page, and select Insert DevExpress MVC Extension as shown below.

In the invoked Insert DevExpress Extension dialog, switch to the Data tab and select the QueryBuilder extension. Define the extension name, specify the connection name (this connection is created later in this tutorial), and select Create new for the Query Builder Controller.

When you click the Insert button, the Wizard automatically generates the following View code:
- Razor
```
@Html.DevExpress().QueryBuilder(settings => {
    settings.Name = "QueryBuilder";
    settings.RouteValues = new { Controller = "QueryBuilder", Action = "Invoke" };
}).Bind("NorthwindConnection").GetHtml()
```
Manually Register the Extension

Create a new controller by inheriting it from the QueryBuilderApiControllerBase class and declare an action method to process requests from the Query Builder.
- C#
- VB.NET
```
public class QueryBuilderController : DevExpress.Web.Mvc.Controllers.QueryBuilderApiControllerBase {
    public override ActionResult Invoke() {
        return base.Invoke();
    }
}
```
```
Public Class QueryBuilderController
    Inherits DevExpress.Web.Mvc.Controllers.QueryBuilderApiControllerBase

    Public Overrides Function Invoke() As ActionResult
        Return MyBase.Invoke()
    End Function
End Class
```
Open the View file (for instance, Index.cshtml) and use the ExtensionsFactory.QueryBuilder helper method that returns a QueryBuilderExtension object used to render the Query Builder. Pass the QueryBuilderSettings object as the helper method’s parameter to configure the Query Builder extension. Use the QueryBuilderExtension.Bind method to bind the Query Builder to the required data connection.
- Razor
```
@Html.DevExpress().QueryBuilder(settings => {
    settings.Name = "QueryBuilder";
    settings.RouteValues = new { Controller = "QueryBuilder", Action = "Invoke" };
}).Bind("NorthwindConnection").GetHtml()
```
Note
Use other Query Builder Bind method overloads to connect to data in different ways (for instance, with connection parameters).

Controller and HTTP Handler

In the code above, the QueryBuilderSettings.RouteValues property specifies a route to the controller that the QueryBuilder component uses to perform certain actions, such as fetching the database schema or Select statement execution.

The DevExpress ASP.NET MVC components are developed based on the DevExpress WebForms components and use the same code and logic. This means that the Query Builder component can use the HTTP Handler specified in the Web.config file as the default backend. The built-in handler is an HTTP module whose type is ASPxHttpHandlerModule and name is DXQB.axd.

In most situations, your application can use either the built-in handler or the default controller. A custom controller is necessary when you implement custom routing, authorization, or anti-forgery support.

Database Connection

Declare your database’s connection string in the Web.config file to supply the Query Builder with data. The name of the connection string is the name specified earlier in the Insert DevExpress Extension dialog.

<connectionStrings>
  <add name="NorthwindConnection" connectionString="data source=localhost;integrated security=SSPI;initial catalog=NORTHWND" providerName="System.Data.SqlClient" />
</connectionStrings>

External Libraries

For the Query Builder to work correctly, make sure that the Web.config file contains the following resources section to load all required libraries automatically:

<devExpress>
    <!-- ... -->
    <resources>
        <add type="ThirdParty" />
        <add type="DevExtreme" />
    </resources>
</devExpress>

Alternatively, you can declare an empty resources section and attach all required DevExtreme resources and third-party libraries as described in the following help topic: Embedding Third-Party Libraries.

Save Generated Queries

In the Query Builder extension’s declaration, use the QueryBuilderSettings.SaveCallbackRouteValues property to specify the names of the controller and action that handle callbacks for saving queries.

Razor

@Html.DevExpress().QueryBuilder(settings => {
    settings.Name = "QueryBuilder";
    settings.RouteValues = new { Controller = "QueryBuilder", Action = "Invoke" };
    settings.SaveCallbackRouteValues = new { Controller = "QueryBuilder", Action = "Save" };
}).Bind("NorthwindConnection").GetHtml()

Add a new Save action (specified in the View code above) to the Query Builder controller. Use the QueryBuilderExtension.GetSaveCallbackResult method to obtain the result object with the following properties:

QueryBuilderSaveCallbackResult.ResultQuery - Returns a SelectQuery object that stores a high-level query model.
QueryBuilderSaveCallbackResult.SelectStatement - Returns a string that specifies the final ‘SELECT’ statement.

C#
VB.NET

using DevExpress.Web.Mvc;
using DevExpress.DataAccess.Sql;

public class QueryBuilderController : DevExpress.Web.Mvc.Controllers.QueryBuilderApiController {
    // ...
     public ActionResult Save() {
         var result = QueryBuilderExtension.GetSaveCallbackResult("QueryBuilder");
         SelectQuery query = result.ResultQuery;
         string selectStatement = result.SelectStatement;
         // ...
     }
}

Imports DevExpress.Web.Mvc
Imports DevExpress.DataAccess.Sql

Public Class QueryBuilderController
    Inherits DevExpress.Web.Mvc.Controllers.QueryBuilderApiController
    ' ...
    Public Function Save() As ActionResult
         ' ...
        Dim result = QueryBuilderExtension.GetSaveCallbackResult("QueryBuilder")
        Dim query As SelectQuery = result.ResultQuery
        Dim selectStatement As String = result.SelectStatement
    End Function
End Class

The next lesson describes how to use the generated query to supply data to the Grid View. See the following topic for more information about how to obtain and process queries: Saving Queries.

View the Result

Run the application to view the result in a web browser. The Query Builder displays all the tables and views obtained from a database. You can drop a table from a table list on the design surface.

mvc-query-builder-drop-table