Binding Stored Procedure Result from Entity Framework to ObjectDataSource Control

Not too long ago someone asked me how can they bind data returned from stored procedure to a gridview control. There are several ways to achieve that. You can either use EntityDataSource or ObjectDataSource Control. I find it easier to use ObjectDataSource control because it allows me to encapsulate logic inside my classes. The basic steps are as follows

1. Bring the Stored Procedure into the storage model.

2. Import the stored procedure into conceptual model. This will result in a method getting exposed on your derived object context.

3. Create a class that you can bind to ObjectDataSource control. On the class expose a method that returns data returned from the method created on your derived Object Context.

Below is a detail walk through of this.

Suppose we are dealing with Customer entity and we have stored procedure that takes city parameter and returns all the customers that are in that city. Figure below shows our customer entity along with the stored procedure.

Stored procedure returning customers for a given city or all customers if the city passed in is null.

1. Import Customer table and the above stored procedure into Entity Data Model.

2.  Open up the model browser and expand the storage section. Find the GetCustomersByCity stored procedure we just imported. Right click on the procedure and select Add Function Import. For the return type choose Customers entity. Figure below shows our Add Function Import dialog.

3. Create a class that you will be used by your ObjectDataSource control to fetch the results from the stored procedure. I am calling the class CustomerRepository. You can call it what you want. Below is a sample implementation.

4. Next we need to create a page that declares a gridview control bound to objectdatasource control. The Type property of the ObjectDataSource wold be CustomerRepository and SelectMethod would be GetCustomersByCity. Code below shows the complete class.

In EF Recipes book we cover how to consume EF using EntityDataSource control and ObjectDataSource Control. you can check out the following recipes.

4-2 Building CRUD Operations in an ASP.NET Web Page

4-8 Building CRUD Operations with an ObjectDataSource Control