QueryParameter Class

A parameter passed to a SqlQuery.

Namespace: DevExpress.DataAccess.Sql

Assembly: DevExpress.DataAccess.v20.2.dll

NuGet Packages: DevExpress.DataAccess, DevExpress.WindowsDesktop.DataAccess

Declaration

C#

public sealed class QueryParameter :
    DataSourceParameterBase

VB.NET

Public NotInheritable Class QueryParameter
    Inherits DataSourceParameterBase

Remarks

A collection of QueryParameter objects is returned by the SqlQuery.Parameters property. This property is also available for the StoredProcQuery class.

The following code retrieves data from the Microsoft SQL Server database and filters the resulting data set with a specified parameter value.

C#

using DevExpress.DataAccess.ConnectionParameters;
using DevExpress.DataAccess.Sql;
// ...

private SqlDataSource BindToData(int parameterValue) {
            // Specify parameters required to connect to a specific data provider.
            MsSqlConnectionParameters connectionParameters =
                new MsSqlConnectionParameters("ServerName", "DataBaseName", null, null, MsSqlAuthorizationType.Windows);

            // Create a data source instance with the specified connection parameters.
            SqlDataSource ds = new SqlDataSource(connectionParameters);

            // Create a query to access fields of the Orders data table.
            // End the chain by calling the Build method with a specified query name.
            SelectQuery query = SelectQueryFluentBuilder
                .AddTable("Orders")
                .SelectColumns("OrderID", "OrderDate", "ShipName", "ShipCountry")
                .Build("Orders");

            // Create a query parameter of a required type and assign a value to it.
            QueryParameter parameter = new QueryParameter() {
                Name = "orderID",
                Type = typeof(System.Int32),
                Value = parameterValue
            };
            query.Parameters.Add(parameter);

            // Filter the query result set using the created parameter.
            query.FilterString = "OrderID = ?orderID";

            // Add the query and fill the data source.
            ds.Queries.Add(query);
            ds.Fill();

            return ds;
}

VB.NET

Imports DevExpress.DataAccess.ConnectionParameters
Imports DevExpress.DataAccess.Sql
' ...

Private Function BindToData(ByVal parameterValue As Integer) As SqlDataSource
        ' Specify parameters required to connect to a specific data provider.
        Dim connectionParameters As MsSqlConnectionParameters = New MsSqlConnectionParameters("ServerName", "DataBaseName", Nothing, Nothing, MsSqlAuthorizationType.Windows)

        ' Create a data source instance with the specified connection parameters.
        Dim ds As SqlDataSource = New SqlDataSource(connectionParameters)

        ' Create a query to access fields of the Products data table.
        ' End the chain by calling the Build method with a specified query name.
        Dim query As SelectQuery = SelectQueryFluentBuilder.AddTable("Orders").SelectColumns("OrderID", "OrderDate", "ShipName", "ShipCountry").Build("Orders")

        ' Create a query parameter of a required type and assign a value to it.
        Dim parameter As QueryParameter = New QueryParameter() With {
            .Name = "orderID",
            .Type = GetType(System.Int32),
            .Value = parameterValue
        }
        query.Parameters.Add(parameter)

        ' Filter the query result set using the created parameter.
        query.FilterString = "OrderID = ?orderID"

        ' Add the query and fill the data source.
        ds.Queries.Add(query)
        ds.Fill()
        Return ds
End Function

You can use expressions to specify the query parameter value. Set the parameter’s Type property to the DevExpress.DataAccess.Expression type and assign an Expression instance to the parameter’s Value property. For more information on operators, functions, and constants, refer to the following help topic: Expression Constants, Operators, and Functions.

C#

using DevExpress.DataAccess;

// Add a 'date' query parameter and set the current date as the parameter value. Format the date as 2020-01-31.
query.Parameters.Add(new QueryParameter(
            name: "date",
            type: typeof(Expression),
            value: new Expression("FormatString('{0:yyyy-MM-dd}',Today())")));

VB.NET

Imports DevExpress.DataAccess

'  Add a 'date' query parameter and set the current date as a parameter value. Format the date as 2020-01-31.
query.Parameters.Add(New QueryParameter(
            name:= "date", 
            type:= GetType(Expression), 
            value: = New Expression("FormatString('{0:yyyy-MM-dd}',Today())")))

In DevExpress reports, expressions can include report parameters. Prepend a report parameter’s name with the ? character in an expression to bind a query parameter to a report parameter.

C#

using DevExpress.DataAccess;

// The "ReportDateParameter" is a DevExpress report parameter whose value is copied to the new query parameter.
query.Parameters.Add(new QueryParameter(
            name: "date",
            type: typeof(Expression),
            value: new Expression("?ReportDateParameter")));

VB.NET

Imports DevExpress.DataAccess

' The "ReportDateParameter" is a DevExpress report parameter whose value is copied to the new query parameter.
query.Parameters.Add(New QueryParameter(
            name:= "date", 
            type:= GetType(Expression), 
            value: = New Expression("?ReportDateParameter")))

Review the following examples on how to use the Expression to link query parameters with the report or dashboard parameters:

• Reporting - Filter Data at the Data Source Level (Runtime Sample)
• Dashboards - How to: Pass a Dashboard Parameter to a Custom SQL Query in Code

Implements

IParameter

IFilterParameter

Inheritance

Object

DataSourceParameterBase

QueryParameter