StoredProcQuery Class

A stored procedure call.

Namespace: DevExpress.DataAccess.Sql

Assembly: DevExpress.DataAccess.v21.2.dll

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:

The following code snippets (auto-collected from DevExpress Examples) contain references to the StoredProcQuery 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.

Inheritance

Object
SqlQuery
StoredProcQuery

Extension Methods

Show 20 items
See Also