Use Query Parameters in WinForms Designer

• Feb 10, 2023
• 11 minutes to read

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

The query parameter value is inserted into the resulting SQL query string in the @QueryParameterName placeholder’s position.

Note

Various SQL extensions can require a special character as the variable’s first character. For instance, this can be the “at” sign (@) for Transact-SQL or the colon (:) for PL/SQL. Insert the required character before the parameter name within the custom SQL query.

In dashboards, query parameters are used in the following scenarios:

• To filter data on the data source level.

• To bind a dashboard to a stored procedure an SQL data source provides.

Use the Parameters property of the dashboard’s SqlDataSource query to access query parameters created within the Query Builder or generated for the data source’s stored procedure.

Configure Query Parameters

The Query Editor‘s second page contains the added query parameters and its settings.

The following properties are available for each query parameter:

Name

Specifies the query parameter’s name.

Type

Specifies the parameter value’s data type.

Expression

Specifies whether an expression is used to specify a parameter value.

Value

Specifies the parameter value.

You can specify a static value according to the selected value’s data type. Alternatively, construct an expression to generate parameter values dynamically.

If the Expression check box is enabled, you can select a dashboard parameter and map it to the query parameter or specify an expression in the Expression Editor dialog.

Use the Add/Remove buttons to add or remove query parameters.

Click the Preview… button to preview the query result. Click Finish to complete query modification.

You can also access query parameters and their settings through the Query Builder by clicking the Edit Parameters… button.

Specify the Query Parameter Value

Below, you can see how to specify a value for a query parameter.

Specify a Static Value

Choose a query parameter’s value type and set a static value in the Value column according to the selected type.

Specify a Dynamic Value

To change the query parameter value dynamically, bind it to the corresponding dashboard parameter or create an expression. To do this, in the query parameter’s settings, select the Expression check box. Then, expand the Value property’s drop-down list and select the option.

Use the following options to calculate the query parameter value dynamically:

Existing Dashboard Parameter

In the drop-down list, select an existing dashboard parameter (created earlier) to map the dashboard parameter to the query parameter.

New Dashboard Parameter

Select the Add Dashboard Parameter option from the drop-down list.

In the invoked Dashboard Parameter dialog, specify the dashboard parameter’s settings. Specify the dashboard parameter type according to the type of the corresponding query parameter. Click OK to save the dashboard parameter.

Expression

Select the Expression Editor option from the drop-down list. In the invoked Expression Editor, specify the expression.

Bind a Dashboard Parameter to a Query Parameter

Pass a Dashboard Parameter Value to a Custom SQL Query

You can create a custom SQL query with a query parameter and bind it to a dashboard parameter to change the value dynamically.

To do this, follow the steps below:

1. In the Query Editor, specify a custom SQL query in the SQL String editor.

   The following custom SQL query contains the WHERE clause that compares the OrderID field to the OrderIdParam query parameter:

   

   Click Next.

2. On the next page of the Query Editor, click Add to create a query parameter.

3. Specify the query parameter’s settings.

   In the Name column, specify the name used in the query. Select the Expression check box and bind the query parameter to a dashboard parameter:

   

4. Click Finish to add the query.

For information on how to bind a query parameter to a multi-value dashboard parameter in a custom SQL query, refer to the following section: Pass a Multi-Value Dashboard Parameter Value to a Custom SQL Query.

Example

The following example shows how to create a new dashboard parameter and bind it to a custom SQL query parameter in code.

View Example

Form1.cs

using DevExpress.DashboardCommon;
using DevExpress.XtraEditors;
using DevExpress.DataAccess;
using DevExpress.DataAccess.Sql;

namespace Dashboard_ParametersAndCustomSQL {
    public partial class Form1 : XtraForm {
        public Form1() {
            InitializeComponent();

            Dashboard dashboard = new Dashboard();
            dashboard.LoadFromXml(@"..\..\Data\Dashboard.xml");

            // Creates a new dashboard parameter.
            StaticListLookUpSettings staticSettings = new StaticListLookUpSettings();
            staticSettings.Values = new string[] { "2014", "2015", "2016" };
            DashboardParameter yearParameter = new DashboardParameter("yearParameter", 
                typeof(string), "2015", "Select year:", true, staticSettings);
            dashboard.Parameters.Add(yearParameter);

            DashboardSqlDataSource dataSource = (DashboardSqlDataSource)dashboard.DataSources[0];
            CustomSqlQuery salesPersonQuery = (CustomSqlQuery)dataSource.Queries[0];
            salesPersonQuery.Parameters.Add(new QueryParameter("startDate", typeof(Expression), 
                new Expression("[Parameters.yearParameter] + '/01/01'")));
            salesPersonQuery.Parameters.Add(new QueryParameter("endDate", typeof(Expression), 
                new Expression("[Parameters.yearParameter] + '/12/31'")));
            salesPersonQuery.Sql = 
                "select * from SalesPerson where OrderDate between @startDate and @endDate";

            dashboardViewer1.Dashboard = dashboard;
        }
    }
}

Form1.vb

Imports DevExpress.DashboardCommon
Imports DevExpress.XtraEditors
Imports DevExpress.DataAccess
Imports DevExpress.DataAccess.Sql

Namespace Dashboard_ParametersAndCustomSQL
    Partial Public Class Form1
        Inherits XtraForm

        Public Sub New()
            InitializeComponent()

            Dim dashboard As New Dashboard()
            dashboard.LoadFromXml("..\..\Data\Dashboard.xml")

            ' Creates a new dashboard parameter.
            Dim staticSettings As New StaticListLookUpSettings()
            staticSettings.Values = New String() { "2014", "2015", "2016" }
            Dim yearParameter As New DashboardParameter("yearParameter", GetType(String), "2015", "Select year:", True, staticSettings)
            dashboard.Parameters.Add(yearParameter)

            Dim dataSource As DashboardSqlDataSource = DirectCast(dashboard.DataSources(0), DashboardSqlDataSource)
            Dim salesPersonQuery As CustomSqlQuery = CType(dataSource.Queries(0), CustomSqlQuery)
            salesPersonQuery.Parameters.Add(New QueryParameter("startDate", GetType(Expression), New Expression("[Parameters.yearParameter] + '/01/01'")))
            salesPersonQuery.Parameters.Add(New QueryParameter("endDate", GetType(Expression), New Expression("[Parameters.yearParameter] + '/12/31'")))
            salesPersonQuery.Sql = "select * from SalesPerson where OrderDate between @startDate and @endDate"

            dashboardViewer1.Dashboard = dashboard
        End Sub
    End Class
End Namespace

Pass a Dashboard Parameter Value to a Stored Procedure

To bind a stored procedure parameter to a dashboard parameter, follow the steps below:

1. Select a stored procedure in the Query Editor:

   

   Click Next.

2. On the next page of the Query Editor, select a query parameter and specify its settings.

   Select the Expression checkbox and bind the query parameter to a dashboard parameter:

   

3. Click Finish to apply the specified settings.

For information on how to bind a query parameter to a multi-value dashboard parameter in a stored procedure, refer to the following section: Pass a Multi-Value Dashboard Parameter Value to a Stored Procedure.

Bind a Multi-Value Dashboard Parameter to a Query Parameter

The following sections show how to pass a multi-value dashboard parameter to a SELECT SQL query, custom SQL query, and stored procedure.

Pass a Multi-Value Dashboard Parameter Value to a Query

To map a multi-value dashboard parameter to a query parameter, follow the steps below:

1. Create a dashboard parameter. In the settings, enable the Allow Multiselect option and specify the Look-Up settings.

   

   Click OK to save the parameter.

   Note

   The query that supplies a list of values to the dashboard parameter (the dynamic look-up list) should be different from the query that supplies data to the dashboard.

2. In the Query Editor, run the Query Builder and specify the query. To specify a filter criteria, click the Filter… button.

   You can also specify the filter criteria by clicking the Filter button in the Query group on the Data Source ribbon page.

3. In the invoked Filter Editor, specify the condition with the Is any of or Is none of operator. To compare a field value to a parameter value, click the button of the second operand, and then click the button. After that, click Select a parameter.

   Select the Bind to… option and then select the dashboard parameter created earlier:

   

   In this case, a query parameter with the same name as the dashboard parameter (OrderIdDashParam) is created and bound to the selected dashboard parameter.

4. Click OK to apply the specified settings.

The following query selects the orders for which IDs can be found within the @OrderIdDashParam query parameter values:

For more information on how to filter SQL queries, refer to the following article: Filter Queries.

Pass a Multi-Value Dashboard Parameter Value to a Custom SQL Query

You cannot pass a multi-value parameter to a custom SQL query directly. To pass a multi-value parameter to a custom SQL query, convert the array of parameter values to a string with the JOIN() function and then split the resulting string in the query.

To map a multi-value dashboard parameter to a custom SQL query parameter, follow the steps below:

1. Create a dashboard parameter. In the settings, enable the Allow Multiselect option and specify the Look-Up settings.

   

   Click OK to save the parameter.

   Note

   The query that supplies a list of values to the dashboard parameter (the dynamic look-up list) should be different from the query that supplies data to the dashboard.

2. In the Query Editor, specify a custom SQL query in the SQL String editor. The image below displays a custom query for a Microsoft SQL Server database.

   

   The STRING_SPLIT function is used to split a string into rows of substrings. For information about techniques for other databases, refer to the following sections:

   • Oracle
   • MySQL
   • IBM DB2

3. On the next page of the Query Editor, click Add to create a query parameter.

4. Specify the query parameter’s settings.

   In the Name column, specify the name used in the query. Select the Expression check box and select the Expression Editor option from the drop-down list.

5. In the Expression Editor, pass the name of the dashboard parameter to the JOIN() function.

   

   The resulting query parameter’s settings may appear as follows:

   

6. Click Finish to add the query.

For more information about custom SQL queries, refer to the following article: Custom SQL Queries.

Pass a Multi-Value Dashboard Parameter Value to a Stored Procedure

You cannot pass a multi-value parameter value to a stored procedure directly. To pass a multi-value parameter to a stored procedure, convert the array of parameter values to a string with the JOIN() function if you use the Microsoft SQL Server, MySQL, or Oracle database. The stored procedure code should parse the resulting string to get the dashboard parameter values.

Note

Do not pass dynamic look-up dashboard parameters to a custom query or a stored procedure with the Select All option enabled.

1. Create a dashboard parameter. In the settings, enable the Allow Multiselect option and specify the Look-Up settings.

   

   Click OK to save the parameter.

   Note

   The query that supplies a list of values to the dashboard parameter (the dynamic look-up list) should be different from the query that supplies data to the dashboard.

2. In the Query Editor, select a stored procedure.

   

3. On the next page, select the Expression check box for the query parameter of the selected stored procedure. From the drop-down list, select the Expression Editor option.

4. In the invoked Expression Editor, pass the name of the dashboard parameter to the JOIN() function.

   

   The resulting query parameter’s settings may appear as follows:

   

5. Click Finish to add the query.

Below are examples of how to get parameter values from the passed string within a stored procedure.

MS SQL Server

Split the string the Join() function returns into an array within the stored procedure.

create procedure [dbo].[GetOrdersByID]
    @data varchar(100)
as 
 select * from Orders
 where OrderID in (select value from STRING_SPLIT(@data,','));

Note that you can use the STRING_SPLIT function in Microsoft SQL Server 2016 and later.

You can also implement a stored procedure with a User Defined Table Type parameter. In this instance, use the CreateTable() expression function to prepare a table from values of several multi-value parameters.

The following code demonstrates how to implement a stored procedure that gets values from the table the CreateTable() function produces:

create type OrderParameters as table (value int, dt datetime);

create procedure [dbo].[get_orders]
   @args OrderParameters readonly 
as 
 select * from Orders
 where OrderId in (select value from @args)
 or OrderDate in (select dt from @args)

Oracle

The following code demonstrates how to implement an Oracle-specific stored procedure that takes a string of parameters:

create or replace procedure get_movies(titles in varchar, rc out sys_refcursor) as 
begin
 open rc for 
   select * from movies
   where title in (
     select regexp_substr(titles, '[^,]+', 1, level) from dual
     connect by regexp_substr(titles, '[^,]+', 1, level) is not null 
   );
end;

MySQL

The following code demonstrates how to implement a MySQL-specific stored procedure that takes a string of parameters:

create procedure `get_movies`(in titles varchar(100))
begin
   select *
   from movies
   where find_in_set(title, titles);
end;

IBM DB2

The following code is the stored procedure for an IBM DB2 database that takes a string of parameters:

CREATE OR REPLACE PROCEDURE "GetOrdersByID" (data varchar(100)) READS SQL DATA DYNAMIC RESULT SETS 1  
    BEGIN
        DECLARE cursor_1 CURSOR WITH RETURN FOR
        SELECT *
        FROM "Orders"
        WHERE "OrderID" in (SELECT * from XMLTABLE('$d/r/i' PASSING XMLPARSE(DOCUMENT CAST('<r><i>' || replace(data , ',' , '</i><i>') || '</i></r>' as CLOB) ) as "d" COLUMNS ITEM VARCHAR(255) PATH '.'));
        OPEN cursor_1;
    END