Passing Parameter Values to a Data Source Query

• Sep 28, 2018
• 7 minutes to read

This document provides general information on query parameters and describes common ways of utilizing parametrized SQL queries to perform simple and straightforward data source level data shaping.

The document consists of the following sections.

• Query Parameters Overview
• Create and Configure Query Parameters
• Provide the Query Parameter Value
• Pass a Multi-Value Parameter to a Query

Query Parameters Overview

A query parameter holds an external value that is inserted into a SQL statement before query execution. This value can be either static or dynamically generated by an associated expression.

The query parameter value is inserted into the resulting SQL query string in the position of the corresponding placeholder, which has the “@QueryParameterName“ form.

In XtraReports, query parameters are typically used in the following scenarios.

• Passed as criteria to the WHERE part of a SQL statement to perform data source level filtering. To learn more, see Filtering Data at the Data Source Level.
• Passed as actual parameters to a stored procedure as described in the Bind a Report to a Stored Procedure topic.

The image below demonstrates a SQL string with parameter placeholders produced by the Query Builder.

Create and Configure Query Parameters

To create and configure query parameters, do the following.

1. When creating a new data-bound report using the Report Wizard or binding an existing report to a database, go to the query customization page. On this page, click the button for the Queries category to create a new query using the Query Builder.

   

   You can also open the query customization page to modify an existing data source as described in the Query Builder document.

2. In the Query Builder, construct the required query and click the Edit Parameters button.

   

3. The invoked Query Parameters dialog allows you to add and remove query parameters as well as specify parameter settings.

   

   For each query parameter, the following properties are available.

   • Name - specifies the name used to refer to a parameter.
   • Type - specifies the data type of the parameter value.
   • Expression - determines whether the actual parameter value is static or generated dynamically.
   • Value - determines the actual value of the query parameter. If the Expression option is enabled, the actual parameter value is produced dynamically by calculating an associated expression. This is particularly useful when you need to map the query parameter value to the value of a report parameter. For more information, refer to the Provide the Query Parameter Value document section.

4. To filter data at the data source level based on query parameter values, click the Filter button, and in the invoked Filter Editor, construct a filtering expression. To access parameters, continue clicking the icon on the right until it turns into a question mark. Then, click the parameter placeholder and select one of the available parameters.

   

   You can also create a new parameter by selecting Add Query Parameter. Then, in the invoked Create Query Parameter dialog, specify the parameter name and appropriate value type, and click OK.

   

   As a result, the query parameter will be passed to the WHERE part of the SQL string and will have the “@QueryParameterName“ form.

The created query parameters will then be available on the Configure Query Parameters wizard page.

After creating a data source, you can access and manage its parameters. To do this, select the data source in the Report Explorer and go to the Properties window. Then, expand the SqlDataSource.Queries collection property and click the ellipsis for the SqlQuery.Parameters property of the required query.

This invokes the Query Parameters dialog described above.

Provide the Query Parameter Value

You can provide actual values (either static or dynamic) for query parameters right away when creating the parameters in the dedicated dialogs or do it afterwards on the Configure Query Parameters wizard page.

• Specifying a static value

  To specify a static value for a query parameter, choose its value type and specify the Value property according to the selected type.

  

• Providing a dynamic value

  To use a dynamically generated value, activate the Expression check box for the required parameter.

  Then, the following three options will be available to you, which help to dynamically calculate the parameter’s actual value.

  • To create a complex expression, expand the drop-down list for the Value property and select Expression Editor.

    

    Construct a required expression in the invoked Expression Editor.

    

  • To map the query parameter to a new report parameter, expand the drop-down list for the Value property and select New Report Parameter.

    

    In the invoked Report Parameter dialog, specify required report parameter settings. Be sure to specify the report parameter type according to the type of the corresponding query parameter. Click OK to exit the dialog.

    

  • You can map a query parameter to a report parameter that already exists in a report by referring to its value from a simple expression. To do this, expand the drop-down list for the Value property in the Query Parameters and select the parameter that you want to use. An appropriate expression string will be generated automatically.

    

    Note

    At present, mapping query parameters to multi-value report parameters is not supported. For a possible workaround, see the next section of this document.

Pass a Multi-Value Parameter to a Query

This example illustrates a workaround enabling you to use multi-value parameters in a query string.

At present, query parameters cannot be directly mapped to multi-value report parameters, and this task is solved by adding a WHERE clause to the SQL string in the following way.

SQL

SELECT [Categories].[CategoryID], [Categories].[CategoryName] FROM [Categories] [Categories]
 WHERE [Categories].[CategoryID] IN (1,2,3)

To dynamically update the query string using the values assigned to a report parameter (whose Parameter.MultiValue property is set to true), use the following code in the XtraReportBase.DataSourceDemanded event handler.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/how-to-pass-a-multi-value-parameter-to-a-query-t352379.

XtraReport1.vb

Imports DevExpress.DataAccess.Sql
Imports System
Imports System.Collections
Imports System.Text
' ...

Namespace WindowsFormsApplication3
    Partial Public Class XtraReport1
        Inherits DevExpress.XtraReports.UI.XtraReport

        Public Sub New()
            InitializeComponent()

            ' Assign a set of values to the report parameter.
            Me.parameter1.Value = New Integer() { 1, 2, 3 }

            ' Handle the DataSourceDemanded event of a report.
            AddHandler Me.DataSourceDemanded, AddressOf XtraReport1_DataSourceDemanded
        End Sub

        Private Sub XtraReport1_DataSourceDemanded(ByVal sender As Object, ByVal e As EventArgs)
            Dim query As CustomSqlQuery = TryCast(Me.sqlDataSource1.Queries(0), CustomSqlQuery)
            Dim count As Integer = (TryCast(Me.parameter1.Value, IList)).Count
            If count = 0 Then
                Return
            End If
            Dim builder As New StringBuilder()
            builder.Append("("c)
            For i As Integer = 0 To count - 1
                'builder.Append('\''); // Uncomment this line when parsing a string parameter value.
                builder.Append((TryCast(Me.parameter1.Value, IList))(i))
                'builder.Append('\''); // Uncomment this line when parsing a string parameter value.
                If i <> count - 1 Then
                    builder.Append(","c)
                End If
            Next i

            builder.Append(")"c)
            query.Sql &= " WHERE [Categories].[CategoryID] IN " & builder.ToString()
            sqlDataSource1.RebuildResultSchema()
        End Sub
    End Class
End Namespace

XtraReport1.cs

using DevExpress.DataAccess.Sql;
using System;
using System.Collections;
using System.Text;
// ...

namespace WindowsFormsApplication3 {
    public partial class XtraReport1 : DevExpress.XtraReports.UI.XtraReport {
        public XtraReport1() {
            InitializeComponent();

            // Assign a set of values to the report parameter.
            this.parameter1.Value = new int[] { 1, 2, 3 };

            // Handle the DataSourceDemanded event of a report.
            this.DataSourceDemanded += XtraReport1_DataSourceDemanded;
        }

        void XtraReport1_DataSourceDemanded(object sender, EventArgs e) {
            CustomSqlQuery query = this.sqlDataSource1.Queries[0] as CustomSqlQuery;
            int count = (this.parameter1.Value as IList).Count;
            if (count == 0)
                return;
            StringBuilder builder = new StringBuilder();
            builder.Append('(');
            for (int i = 0; i < count; i++) {
                //builder.Append('\''); // Uncomment this line when parsing a string parameter value.
                builder.Append((this.parameter1.Value as IList)[i]);
                //builder.Append('\''); // Uncomment this line when parsing a string parameter value.
                if (i != count - 1)
                    builder.Append(',');
            }

            builder.Append(')');
            query.Sql += " WHERE [Categories].[CategoryID] IN " + builder.ToString();
            sqlDataSource1.RebuildResultSchema();
        }
    }
}