Bind a Report to a Stored Procedure (Runtime Sample)
- 3 minutes to read
- Create an SqlDataSource instance and set up its connection parameters.
- Create and set up a stored procedure query.
- Create a StoredProcedureQuery object and specify its name.
- Assign the stored procedure name to the query’s StoredProcName property.
- Add the query to the SQL data source’s Queries collection.
- Create and set up a stored procedure query parameter.
- Create a QueryParameter instance and set its Name property to the stored procedure parameter name.
- Set up the query parameter’s Type and Value properties.
- Add the query parameter to the stored procedure query’s Parameters collection.
- 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;