QueryParameter Class

A parameter passed to a SqlQuery.

Namespace: DevExpress.DataAccess.Sql

Assembly: DevExpress.DataAccess.v18.1.dll

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 illustrates how to bind to a Microsoft SQL Server database and filter the result set of a query by 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 Products data table.
    // End the chain by calling the Build method with a specified query name.
    SelectQuery query = SelectQueryFluentBuilder
        .AddTable("Products")
        .SelectColumns("CategoryID", "ProductName")
        .Build("Products");

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

    // Filter the query result set using the created parameter.
    query.FilterString = "CategoryID = ?catID";

    // 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(parameterValue As Integer) As SqlDataSource
    ' Specify parameters required to connect to a specific data provider.
    Dim connectionParameters As _ 
        New MsSqlConnectionParameters("ServerName", "DataBaseName", Nothing, Nothing, MsSqlAuthorizationType.Windows)

    ' Create a data source instance with the specified connection parameters.
    Dim ds As 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("Products").
        SelectColumns("CategoryID", "ProductName").
        Build("Products")

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

    ' Filter the query result set using the created parameter.
    query.FilterString = "CategoryID = ?catID"

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

    Return ds
End Function

To learn how to link query parameters with report or dashboard parameters using an Expression, refer to the following tutorials.

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

Related GitHub Examples

The following code snippets (auto-collected from DevExpress Examples) contain references to the QueryParameter class.

Note

The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.

C#

how-to-create-data-access-library-data-sources-at-runtime-t424210/CS/SqlDataSourceSnippets/Code.cs#L41

StoredProcQuery spQuery = new StoredProcQuery("StoredProcedure", "stored_procedure_name");
spQuery.Parameters.Add(new QueryParameter("@First", typeof(int), 0));
spQuery.Parameters.Add(new QueryParameter("@Second", typeof(string), "Value"));

how-to-pass-a-dashboard-parameter-to-a-custom-sql-query-in-code-e5120/CS/Dashboard_ParametersAndCustomSQL/Form1.cs#L23

CustomSqlQuery salesPersonQuery = (CustomSqlQuery)dataSource.Queries[0];
salesPersonQuery.Parameters.Add(new QueryParameter("startDate", typeof(Expression),
    new Expression("[Parameters.yearParameter] + '/01/01'")));

Reporting_how-to-dynamically-pass-xtrareport-parameters-into-the-underlying-sqldatasource-to-t158360/CS/WindowsFormsApplication1/Form1.cs#L23

DevExpress.DataAccess.Sql.QueryParameter queryParameter1 = new DevExpress.DataAccess.Sql.QueryParameter();
queryParameter1.Name = "p1";

Reporting_how-to-programmatically-filter-report-data-at-the-data-source-level-using-query-t203059/CS/QueryParametersRuntime/XtraReport1.cs#L33

// a simple expression that references a value of a report parameter named "catID".
QueryParameter parameter = new QueryParameter() {
    Name = "catID",

VB.NET

how-to-create-data-access-library-data-sources-at-runtime-t424210/VB/SqlDataSourceSnippets/Code.vb#L28

Dim spQuery As New StoredProcQuery("StoredProcedure", "stored_procedure_name")
spQuery.Parameters.Add(New QueryParameter("@First", GetType(Integer), 0))
spQuery.Parameters.Add(New QueryParameter("@Second", GetType(String), "Value"))

how-to-pass-a-dashboard-parameter-to-a-custom-sql-query-in-code-e5120/VB/Dashboard_ParametersAndCustomSQL/Form1.vb#L27

    CType(dataSource.Queries(0), CustomSqlQuery)
salesPersonQuery.Parameters.Add(New QueryParameter("startDate", GetType(Expression), _
                                New Expression("[Parameters.yearParameter] + '/01/01'")))

Reporting_how-to-dynamically-pass-xtrareport-parameters-into-the-underlying-sqldatasource-to-t158360/VB/WindowsFormsApplication1/Form1.vb#L21

Dim queryParameter1 As New DevExpress.DataAccess.Sql.QueryParameter()
queryParameter1.Name = "p1"

Reporting_how-to-programmatically-filter-report-data-at-the-data-source-level-using-query-t203059/VB/QueryParametersRuntime/XtraReport1.vb#L31

' a simple expression that references a value of a report parameter named "catID".
Dim parameter As New QueryParameter() With { _
    .Name = "catID", _

Implements

IParameter

Inheritance

Object

DataSourceParameterBase

QueryParameter