Get data from a stored procedure using Entity Framework and display it in Grid View.

In this article we are going to see how we can get data from a stored procedure using Entity Framework and display it in grid view in .NET Framework 4.0.
1.Let us consider following Emp table.

2.Below is the stored procedure which we are going to use for this example which will fetch data from the Emp table.
CREATE PROCEDURE dbo.GetAllEmp
AS
SET NOCOUNT ON
SELECT EmpId,EmpName,Salary,IsActive
FROM Emp;

3.Create a New Website and add New Item "ADO.NET Entity Data Model" and name it as "EmpEntityModel" as shown below:

4.If you get below message: click on Yes which places the Entity Data Model in the App_Code folder of the website.


5.Now a dialog box will appear which will prompt for Data Model Contents. Now select "Generate from database" option as below, It will create a Entity model from the database and click Next.

6.After click on the next it will prompt for database connection string as shown. We need to create one via clicking on new connection. If we check the "Save entity connection settings in Web.Config as" checkbox, it will save the connection string name in web.config file.

7.Once you click next it will fetch all database objects information like Tables,Views and Stored Procedure as shown below. Here I have selected the Emp table and the "GetAllEmp" Stored Procedure. Give the Model Namespace as "EmpDBModel".

8.After this, it will create a Entity Model class in solution explorer. Now build the solution. Now we would like to bind the stored procedure with the result class. so first let us create function which will call 'GetAllEmp' stored procedure. Go to EntityDataModel Browser from the View tab as shown below.

9.In order to create function we need to select Our Entity Model class and then go to Model Browser from the View tab and then select the Stored Procedure and right click->Add Function Import as shown below:

10.It will open the "Add Function Import" window. The stored procecure return type has four options 1. None 2. Scalars 3. Complex 4. Entities and there will be a button Get Column information. once you click this button. It will gather all the column information of stored procedure result set. Then click 'Create New Complex Type' It will create a new complex type from the gathered column information. I have renamed the complex type as 'GetAllEmpResult'.

11.Build the solution. Now click ok this will create function which calls the stored procedure and will return Object Result set of Type 'GetAllEmpResult which we have just created. Now let us bind this result set to a simple grid view to see how it works.
Add a GridView to the Default.aspx page as shown below:
<asp:GridView ID="gvEmpList" runat="server"></asp:GridView>
12.In the Default.aspx.cs file, in the Page_load event handler, write the below code. In the below code We are creating an instance of the "EmpEntities" class and calling the GetAllEmp method of this class and binding it to datasource of the GridView. This will fetch the data from the database using the stored procedure and bind it to the GridView.

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            using (EmpEntities empEntity = new EmpEntities())
            {
                gvEmpList.DataSource = empEntity.GetAllEmp();
                gvEmpList.DataBind();
            }
        }
    }