All docs
V21.1
21.2 (EAP/Beta)
21.1
20.2
20.1
The page you are viewing does not exist in version 20.1. This link will take you to the root page.
19.2
The page you are viewing does not exist in version 19.2. This link will take you to the root page.
19.1
The page you are viewing does not exist in version 19.1. This link will take you to the root page.
18.2
The page you are viewing does not exist in version 18.2. This link will take you to the root page.
18.1
The page you are viewing does not exist in version 18.1. This link will take you to the root page.
17.2
The page you are viewing does not exist in version 17.2. This link will take you to the root page.

Bind a Report to a Stored Procedure (Runtime Sample)

  • 3 minutes to read
  1. Create an SqlDataSource instance and set up its connection parameters.
  2. Create and set up a stored procedure query.
    1. Create a StoredProcedureQuery object and specify its name.
    2. Assign the stored procedure name to the query’s StoredProcName property.
    3. Add the query to the SQL data source’s Queries collection.
  3. Create and set up a stored procedure query parameter.
    1. Create a QueryParameter instance and set its Name property to the stored procedure parameter name.
    2. Set up the query parameter’s Type and Value properties.
    3. Add the query parameter to the stored procedure query’s Parameters collection.
  4. Create an XtraReport instance. Assign the SQL data source to the report’s DataSource property. Set the report’s DataMember property to the stored procedure query name.

Example

The following example demonstrates how to bind a report to an SQL data source’s CustOrdersDetail(@OrderID) stored procedure and pass the report’s orderID parameter to this procedure.

using DevExpress.DataAccess.Sql;
using DevExpress.DataAccess.ConnectionParameters;
// ...
// Create an SQL data source and set up its connection parameters.
var sqlDataSource = new SqlDataSource {
    Name = "sqlDataSource1",
    ConnectionName = "SQL_Northwind"
};

var connectionParameters = new MsSqlConnectionParameters() {
    ServerName = "localhost",
    DatabaseName = "Northwind",
    AuthorizationType = MsSqlAuthorizationType.Windows
};

sqlDataSource.ConnectionParameters = connectionParameters;

// Create a stored procedure query.
var queryName = "CustomersOrdersDetail";

var storedProcedureQuery = new StoredProcQuery() {
    Name = queryName,
    StoredProcName = "CustOrdersDetail"
};

sqlDataSource.Queries.Add(storedProcedureQuery);

// Pass the report's orderID parameter to the stored procedure.
var queryParameter = new QueryParameter() {
    Name = "@OrderID",
    Type = typeof(DevExpress.DataAccess.Expression),
    Value = new DevExpress.DataAccess.Expression("?orderID", typeof(int))
};

storedProcedureQuery.Parameters.Add(queryParameter);

// Create a report instance and specify its DataSource
// and DataMember properties.
var report = new XtraReport1();
report.DataSource = sqlDataSource;
report.DataMember = queryName;

// Optionally, set up the report's parameters and disable
// the RequestParameters property to apply the default
// parameter values to the report when you show its preview.
report.Parameters["orderID"].Value = 10249;
report.RequestParameters = false;