Lesson 2 - Use the Resulting Query

• Dec 17, 2020
• 3 minutes to read

This lesson will teach you how to use a SQL query string generated by the ASP.NET Query Builder control to supply DevExpress data-aware web controls with data.

1. Open the web application created in the Lesson 1 of this tutorial and add a new Web Form to the project.
2. Select the newly added Web Form in the Solution Explorer and click the Design button to open the visual designer for this web form.

3. Expand the DX.20.2: Data and Analytics Toolbox tab and drop the ASPxGridView control onto the page.

   

   The added Grid View will be used to display data obtained from a database by executing a query generated by the Query Builder control.

4. Switch to the Web Form containing the Query Builder added in Lesson 1 and handle the ASPxQueryBuilder.SaveQuery event. This event occurs on the server side every time the Query Builder’s Save () toolbar command is clicked and provides access to the resulting query string. Add the following code to the handler to save the query string and redirect a browser to the page containing a Grid View.

   C#

   using DevExpress.Web;
   // ...
   protected void ASPxQueryBuilder1_SaveQuery(object sender, DevExpress.XtraReports.Web.SaveQueryEventArgs e) {
       Session["selectCommand"] = e.SelectStatement;
       ASPxWebControl.RedirectOnCallback("WebForm2.aspx");
   }
   

   VB.NET

   Imports DevExpress.Web
   ' ...
   Protected Sub ASPxQueryBuilder1_SaveQuery(ByVal sender As Object, ByVal e As DevExpress.XtraReports.Web.SaveQueryEventArgs)
       Session("selectCommand") = e.SelectStatement
       ASPxWebControl.RedirectOnCallback("WebForm2.aspx")
   End Sub
   

5. Add a provider-specific connection string that will be used by the Grid View to the application’s Web.config file.

   XML

   <connectionStrings>
     <add name="NorthwindXpo" connectionString= 
       "data source=localhost;integrated security=SSPI;
        initial catalog=nwind.mdf;XpoProvider=MSSqlServer;"/>
     <add name="Northwind" connectionString=
       "data source=localhost;integrated security=SSPI;
        initial catalog=nwind.mdf" providerName="System.Data.SqlClient" />
   </connectionStrings>
   

6. Switch to the Web Form containing the Grid View. On the Page_Load event, call the ASPxWebControl.DataBind method of the Grid View. Next, handle the System.Web.UI.Control.DataBinding event of the Grid View to bind it to data using the SQL query generated by the Query Builder as show below.

   C#

   using System.Configuration;
   using System.Web.UI.WebControls;
   // ...
   protected void Page_Load(object sender, EventArgs e) {
       ASPxGridView1.DataBind();
   }
   
   protected void ASPxGridView1_DataBinding(object sender, EventArgs e) {
       var selectCommand = Session["selectCommand"] as string;
       if (string.IsNullOrEmpty(selectCommand)) return;
       var grid = sender as ASPxGridView;
       // Bind a Grid View to a new SqlDataSource populated using the generated SQL query.
       grid.DataSource = new SqlDataSource(ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString, selectCommand);
   }
   

   VB.NET

   Imports System.Configuration
   Imports System.Web.UI.WebControls
   ' ...
   Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
       ASPxGridView1.DataBind()
   End Sub
   
   Protected Sub ASPxGridView1_DataBinding(ByVal sender As Object, ByVal e As EventArgs)
       Dim selectCommand = TryCast(Session("selectCommand"), String)
       If String.IsNullOrEmpty(selectCommand) Then
           Return
       End If
       Dim grid = TryCast(sender, ASPxGridView)
       ' Bind a Grid View to a new SqlDataSource populated using the generated SQL query.
       grid.DataSource = New SqlDataSource(ConfigurationManager.ConnectionStrings("Northwind").ConnectionString, selectCommand)
   End Sub
   

7. Run your web application, use the Query Builder to construct a query and click the Query Builder’s Save toolbar command to view the result.