Lesson 2 - Use the Resulting Query

• Mar 06, 2024
• 3 minutes to read

This lesson demonstrates how to use queries that the Query Builder generates to supply data to the DevExpress ASP.NET MVC GridView control.

View Example

Pass the Query Data to the GridView

1. Create a new Partial View file (for instance, GridViewPartial.cshtml), since the GridView updating mechanism requires declaring its extension in the Partial View. Use the ExtensionsFactory.GridView helper method and pass the GridViewSettings object as a parameter to configure the extension as shown below:

   Razor

   @Html.DevExpress().GridView(settings => {
       settings.Name = "grid";
       settings.Width = Unit.Percentage(100);
       settings.CallbackRouteValues = new { Controller = "Home", Action = "GridViewPartial" };
       settings.SettingsBehavior.AllowEllipsisInText = true;
   }).Bind(Model).GetHtml()
   

2. Move the Query Builder extension’s declaration from the Index.cshtml file to a new separate View file (QueryBuilder.cshtml in this example).

3. In the Index.cshtml file, render the GridView and provide the capability to run the Query Builder using the following code:

   C#

   @model SqlDataSource
   
   <a href="@Url.Action("QueryBuilder")">Run Query Builder</a>
   <p /><p />
   
   @Html.Partial("GridViewPartial", Model)
   

4. In the Query Builder controller, modify the Save action to save the QueryBuilderSaveCallbackResult.SelectStatement property value to the session variable and redirect a browser to the page with the GridView.

   C#

   using DevExpress.Web.Mvc;
   
   public class QueryBuilderController : DevExpress.Web.Mvc.Controllers.QueryBuilderApiController {
       // ...
       public ActionResult Save() {
           var result = QueryBuilderExtension.GetSaveCallbackResult("QueryBuilder");
           Session["SelectCommand"] = result.SelectStatement;
           return RedirectToAction("Index", "Home");
       }
   }
   

   VB.NET

   Imports DevExpress.Web.Mvc
   
   Public Class QueryBuilderController
       Inherits DevExpress.Web.Mvc.Controllers.QueryBuilderApiController
       ' ...
       Public Function Save() As ActionResult
           Dim result = QueryBuilderExtension.GetSaveCallbackResult("QueryBuilder")
           Session("SelectCommand") = result.SelectStatement
           Return RedirectToAction("Index", "Home")
       End Function
   End Class
   

5. Open the HomeController file and implement the action methods as demonstrated below:

   C#

   using System.Web.Mvc;
   using System.Configuration;
   
   public class HomeController : Controller {
       string NorthwindConnectionString {
           get {
               return ConfigurationManager.ConnectionStrings["NorthwindConnection"].ConnectionString;
           }
       }
       System.Web.UI.WebControls.SqlDataSource DataSource {
           get {
               var select = @"select [Suppliers].[CompanyName] from [dbo].[Suppliers]"; // The default SELECT statement.
               if (Session["SelectCommand"] != null) {
                   select = Session["SelectCommand"] as string;
               }
               return new System.Web.UI.WebControls.SqlDataSource(NorthwindConnectionString, select);
           }
       }
   
       public ActionResult Index() {
           return View(DataSource);
       }
   
       public ActionResult QueryBuilder() {
           return View("QueryBuilder");
       }
   
       public ActionResult GridViewPartial() {
           return PartialView(DataSource);
       }
   }
   

   VB.NET

   Imports System.Web.Mvc
   Imports System.Configuration
   
   Public Class HomeController
       Inherits Controller
   
       Private Property NorthwindConnectionString As String
           Get
               Return ConfigurationManager.ConnectionStrings("NorthwindConnection").ConnectionString
           End Get
       End Property
   
       Private Property DataSource As System.Web.UI.WebControls.SqlDataSource
           Get
               ' The default SELECT statement.
               Dim [select] = "select [Suppliers].[CompanyName] from [dbo].[Suppliers]" 
               If Session("SelectCommand") IsNot Nothing Then
                   [select] = TryCast(Session("SelectCommand"), String)
               End If
   
               Return New System.Web.UI.WebControls.SqlDataSource(NorthwindConnectionString, [select])
           End Get
       End Property
   
       Public Function Index() As ActionResult
           Return View(DataSource)
       End Function
   
       Public Function QueryBuilder() As ActionResult
           Return View("QueryBuilder")
       End Function
   
       Public Function GridViewPartial() As ActionResult
           Return PartialView(DataSource)
       End Function
   End Class
   

View the Result

Run the application to see the GridView displaying the default data and click the Run Query Builder link.

In the invoked Query Builder, construct a query and click the Save toolbar button.

This opens the page with the GridView that shows the obtained data.