Bind a Report to an Entity Framework Stored Procedure
- 4 minutes to read
This tutorial demonstrates how to bind a report to a stored procedure provided by an Entity Framework data model. Additionally, it shows how to map report parameters to stored procedure parameters, producing a convenient interface for user-level data shaping.
NOTE
The approach described in this document requires your project to contain an .edmx file defining a conceptual model. The Code-First approach to calling stored procedures is not supported by EFDataSource.
Bind to a Stored Procedure
To bind a report to a stored procedure, do the following.
Click the report's smart tag. In the invoked actions list, expand the drop-down menu for the DataSource property and click Add Report Data Source...
On the first page of the invoked Data Source Wizard, select Entity Framework and click Next.
Select the data context and click Next.
On the next wizard page, specify the connection string to be used to establish a data connection and click Next.
The next wizard page allows you to add stored procedures to the data source and configure their parameters. To add a stored procedure, click Add.
In the invoked dialog, select the required stored procedure from the list of the available stored procedures. You can select multiple stored procedures by clicking list items while pressing CTRL. Click OK to exit the dialog.
Next, specify values for the stored procedure parameters in the list of stored procedure parameters. A stored procedure parameter value can be either static or generated by an expression. In this document section, static parameter values are used.
To preview the result of the stored procedure execution with the specified parameters, click the Preview button.
The following image demonstrates the Data Preview displaying the resulting data sample. Click Close to exit the preview.
Click Finish to exit the wizard.
The Field List will be updated to reflect the structure of the created Entity Framework data source including stored procedures added on the previous step.
Pass Parameters to the Stored Procedure
To map report parameters to parameters of a stored procedure, do the following.
To edit stored procedure parameters, select the Entity Framework data source in the Report Explorer and click the ellipsis for its EFDataSource.StoredProcedures property in the Properties window.
The Manage Stored Procedures dialog will be invoked. Select the required stored procedure in the list of the stored procedure to edit its parameters. For each stored procedure parameter that you want to map to a report parameter, activate the Expression check box, which allows you to use expressions to calculate parameter values. Next, expand the drop-down list for the Value property and select New Report Parameter to create a new report parameter, and use a reference to this parameter as a simple expression to generate a value that will be passed to the stored procedure.
In the invoked Add New Parameter dialog, specify the required report parameter settings. Be sure to specify the report parameter type according to the type of the respective stored procedure parameter. Click OK to exit the dialog.
Alternatively, you can map a stored procedure parameter to a report parameter that already exists in a report. To do this, expand the drop-down list for the Value property and select the parameter that you want to use.
You can also create a complex expression to generate a parameter value. To do this, expand the drop-down list for the Value property in the EFParameter Collection Editor and select Edit Expression. Then, construct the required expression in the invoked Expression Editor.
View the Result
A parameterized report bound to a stored procedure is now ready. To view the result, drop the data fields that you want to display in your report onto appropriate report bands and switch to the Preview Tab of the Visual Studio Report Designer. Enter values for the required report parameters in the Parameters panel, and click Submit.
