QueryParameter Class

A parameter passed to a SqlQuery.

Namespace: DevExpress.DataAccess.Sql

Assembly: DevExpress.DataAccess.v23.2.dll

NuGet Packages: DevExpress.DataAccess, DevExpress.Win.PivotGrid, DevExpress.Win.TreeMap

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;
// ...
SqlDataSource DataSource { get; set; }
// ...
void SqlDataSourceInitialization()
{
    MsSqlConnectionParameters connectionParameters = new MsSqlConnectionParameters(){
        ServerName = "localhost",
        DatabaseName = "NorthWind",
        UserName = null,
        Password = null,
        AuthorizationType = MsSqlAuthorizationType.Windows
    };
    DataSource = new SqlDataSource(connectionParameters);
}
// ...
void SelectQueryCreation()
{
    SelectQuery query = SelectQueryFluentBuilder
                        .AddTable("Categories")
                        .SelectColumn("CategoryName")
                        .GroupBy("CategoryName")
                        .Join("Products", "CategoryID")
                        .SelectColumn("ProductName", AggregationType.Count, "ProductCount")
                        .SortBy("ProductName", AggregationType.Count, 
                            System.ComponentModel.ListSortDirection.Descending)
                        .GroupFilter("[ProductCount] > 7")
                        .Build("Categories");
    DataSource.Queries.Add(query);

    QueryParameterInitialization(query);
}
// ...
void QueryParameterInitialization(SelectQuery query)
{
    QueryParameter parameter = new QueryParameter()
    {
        Name = "catID",
        Type = typeof(DevExpress.DataAccess.Expression),
        Value = new DevExpress.DataAccess.Expression("?catID", typeof(System.Int32))
    };
    query.Parameters.Add(parameter);
    query.FilterString = "CategoryID = ?catID";
}

VB.NET

Imports DevExpress.DataAccess.ConnectionParameters
Imports DevExpress.DataAccess.Sql
' ...
Private Property DataSource() As SqlDataSource
' ...
Private Sub SqlDataSourceInitialization()
    Dim connectionParameters As New MsSqlConnectionParameters() With
        {
        .ServerName = "localhost",
        .DatabaseName = "NorthWind",
        .UserName = Nothing,
        .Password = Nothing,
        .AuthorizationType = MsSqlAuthorizationType.Windows
    }
    DataSource = New SqlDataSource(connectionParameters)
End Sub
' ...
Private Sub SelectQueryCreation()
    Dim query As SelectQuery = SelectQueryFluentBuilder.
        AddTable("Categories").
        SelectColumn("CategoryName").
        GroupBy("CategoryName").
        Join("Products", "CategoryID").
        SelectColumn("ProductName", AggregationType.Count, "ProductCount").
        SortBy("ProductName", AggregationType.Count, System.ComponentModel.ListSortDirection.Descending).
        GroupFilter("[ProductCount] > 7").
        Build("Categories")
    DataSource.Queries.Add(query)

    QueryParameterInitialization(query)
End Sub
' ...
Private Sub QueryParameterInitialization(ByVal query As SelectQuery)
    Dim parameter As New QueryParameter() With {
        .Name = "catID",
        .Type = GetType(DevExpress.DataAccess.Expression),
        .Value = New DevExpress.DataAccess.Expression("?catID", GetType(System.Int32))}
    query.Parameters.Add(parameter)
    query.FilterString = "CategoryID = ?catID"
End Sub

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 Language. 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.

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)
• BI Dashboard - Pass a Dashboard Parameter Value to a Custom SQL Query

Implements

IParameter

IFilterParameter

Inheritance

Object

DataSourceParameterBase

QueryParameter