StoredProcQuery Class

A stored procedure call.

Namespace: DevExpress.DataAccess.Sql

Assembly: DevExpress.DataAccess.v24.1.dll

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

Declaration

C#

public sealed class StoredProcQuery :
    SqlQuery

VB.NET

Public NotInheritable Class StoredProcQuery
    Inherits SqlQuery

Remarks

The following code creates the SqlDataSource that uses a stored procedure to retrieve data. A stored procedure accepts two parameters. The type of the @CategoryName parameter is Expression. This parameter is bound to the pCategoryName report parameter. The @OrdYear parameter is a static string.

Add the QueryParameter objects to the StoredProcQuery.Parameters collection to pass parameters to a stored procedure. To bind the QueryParameter to a report parameter, set the QueryParameter.Type to Expression and assign an Expression instance to the QueryParameter.Value property.

C#

using DevExpress.DataAccess.ConnectionParameters;
using DevExpress.DataAccess.Sql;
// ...
private SqlDataSource BindToStoredProcedure() {
            // Instantiate a SqlDataSource.
            SqlDataSource ds = new SqlDataSource();

            // Specify the name of the connection and parameters required to connect to a specific data provider.            
            ds.ConnectionName = "DataBaseConnectionName";
            ds.ConnectionParameters = new MsSqlConnectionParameters("ServerName", "DataBaseName", "userName", "password", MsSqlAuthorizationType.SqlServer);            

            // Create a stored procedure query to access fields of the 'SalesByCategory' stored procedure.            
            DevExpress.DataAccess.Sql.StoredProcQuery spQuery = new StoredProcQuery("spQuerySalesByCategory", "SalesByCategory");

            // Initialize stored procedure parameters 
            // The @CategoryName parameter is initialized as an expression and bound to the report's 'pCategoryName' parameter.
            spQuery.Parameters.Add(new QueryParameter(
                 name: "@CategoryName",
                 type: typeof(DevExpress.DataAccess.Expression),
                 value: new DevExpress.DataAccess.Expression("?pCategoryName", typeof(string))));

            //The @OrdYear parameter is initialized as a static string and has the default value.
            spQuery.Parameters.Add(new QueryParameter(
                name: "@OrdYear",
                type: typeof(string),
                value: "2000"));

            ds.Queries.Add(spQuery);
            ds.Fill();
            return ds;
}

VB.NET

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

Private Function BindToStoredProcedure() As SqlDataSource
        ' Instantiate a SqlDataSource
        Dim ds As SqlDataSource = New SqlDataSource()

        'Specify the name of the connection and parameters required to connect to a specific data provider.
        ds.ConnectionName = "DataBaseConnectionName"
        ds.ConnectionParameters = New MsSqlConnectionParameters(".", "NORTHWND", "sa", "dx", MsSqlAuthorizationType.SqlServer)

        ' Create a stored procedure query to access fields of the 'SalesByCategory' stored procedure. 
        Dim spQuery As DevExpress.DataAccess.Sql.StoredProcQuery = New StoredProcQuery("spQuerySalesByCategory", "SalesByCategory")

        ' Initialize stored procedure parameters 
        ' The @CategoryName parameter Is initialized as an expression and bound to the report's 'pCategoryName' parameter.
        spQuery.Parameters.Add(New QueryParameter(
                               name:="@CategoryName",
                               type:=GetType(DevExpress.DataAccess.Expression),
                               value:=New DevExpress.DataAccess.Expression("?pCategoryName", GetType(String))))

        ' The @OrdYear parameter is initialized as a static string and has the default value.
        spQuery.Parameters.Add(New QueryParameter(
                               name:="@OrdYear", 
                               type:=GetType(String), 
                               value:="2000"))
        ds.Queries.Add(spQuery)
        ds.Fill()
        Return ds
End Function

Note

Do not add OUT parameters to the StoredProcQuery.Parameters collection; otherwise, an error occurs when the stored procedure is called.

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

Inheritance

Object

SqlQuery

StoredProcQuery