Skip to main content

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

public sealed class StoredProcQuery :
    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.

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;
}

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:

Inheritance

Object
SqlQuery
StoredProcQuery
See Also