ADO.Net Entity Framework: How to Retrieve data using Stored procedure step by step

1. Add ADO.Net entity data Model in your project

 
Select Generate from Database and click on Next button.

 
 
Choose the data connection and database name.

After selecting the data connection and database, you will see the below screen where you can see the tables/view/stored procedure section of that database. Here we will select Product table (Just for example) and a stored procedure “Ten Most Expensive Products”. Leave default setting as it is.

 
Once the wizard finish, you see only the product class/table object is added into the entity framework designer screen. Here, you will realize that the stored procedure is not displayed because there is no strongly type object exists where the entity framework assigns the results.

 

 
In order to use stored procedure in entity framework, we need to do one additional step show below. Right Click on entity designer surface and click on Function Import Menu..
 

 
After clicking on Function Import menu, you see dialogue box which shows the stored procedure Name and Function Import Name.
Put the Function Name as “TenExpensiveProducts”. Note that, entity framework will generate this strongly type function in entity code behind file once you done with below steps and you will be able to access the data through this function.
Select Complex Type radio button.

  

 
Click on “Get Column Information” button to generate the schema of that stored procedure. This schema helps entity framework to generate strongly type complex object.

 
After generating the stored procedure schema, you will see that Create New Complex Type button has enabled. It will allow you to generate strongly type object type which will return by stored procedure. Click on Create new complex type and give appropriate name to the new complex type and click on OK button.

 
Here you are done with adding stored procedure into entity framework. Next we will see how can we get the data from the entity framework stored procedure function and how we bind the results to Gridview in aspx page.
 
Add a web page into you application and add gridview control ( Name it as GridView1)
Use the below code to bind the results returned by the stored procedure function to the GridView1 on page load event

Here is the sample code. 

protected void Page_Load(object sender, EventArgs e) 
     

   { 
      

 using (NorthwindEntities context=new NorthwindEntities()) 
           

 { 
    

            GridView1.DataSource = context.TenExpensiveProducts(); 
                GridView1.DataBind(); 
            

 } 
       

   } 

Here is the web page which shows the results returned from stored procedure