Bind a Report to a Stored Procedure (Runtime Sample)

• Aug 01, 2024
• 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.

C#

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;

VB.NET

Imports DevExpress.DataAccess.Sql
Imports DevExpress.DataAccess.ConnectionParameters
' ...
' Create an SQL data source and set up its connection parameters.
Dim sqlDataSource = New SqlDataSource With {
    .Name = "sqlDataSource1",
    .ConnectionName = "SQL_Northwind"
}

Dim connectionParameters = New MsSqlConnectionParameters() With {
    .ServerName = "localhost",
    .DatabaseName = "Northwind",
    .AuthorizationType = MsSqlAuthorizationType.Windows
}

sqlDataSource.ConnectionParameters = connectionParameters

' Create a stored procedure query.
Dim queryName = "CustomersOrdersDetail"

Dim storedProcedureQuery = New StoredProcQuery() With {
    .Name = queryName,
    .StoredProcName = "CustOrdersDetail"
}

sqlDataSource.Queries.Add(storedProcedureQuery)

' Pass the report's orderID parameter to the stored procedure.
Dim queryParameter = New QueryParameter() With {
    .Name = "@OrderID",
    .Type = GetType(DevExpress.DataAccess.Expression),
    .Value = New DevExpress.DataAccess.Expression("?orderID", GetType(Integer))
}

storedProcedureQuery.Parameters.Add(queryParameter)

' Create a report instance and specify its DataSource
' and DataMember properties.
Dim 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