Bind a Report to an Entity Framework Stored Procedure
- 4 minutes to read
This tutorial binds a report to a stored procedure provided by an Entity Framework data model. Additionally, it maps 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 that defines a conceptual model. The Code-First approach to calling stored procedures is not supported by EFData
#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…In the invoked Data Source Wizard, select a new data connection. On the next page, select Entity Framework and click Next.
Select the data context and click Next.
- For .NET apps, data context selection from a custom assembly in the Visual Studio Report Designer is not available.
DBContext
can only be a part of your solution. - For .NET Framework apps, you can select a data context from a custom assembly. For this, use the Browse button on the Choose a data context Data Source Wizard’s page.
The image shows how the page looks like for .NET apps:
- For .NET apps, data context selection from a custom assembly in the Visual Studio Report Designer is not available.
On the next wizard page, specify the data connection type and 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 specified parameters, click the Preview button.
The following image demonstrates the Data Preview that displays the resulting data sample. Click Close to exit this 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 from the list of stored procedures 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 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 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 required report parameters in the Parameters panel, and click Submit.