In this article, I woul like to explain how we can retreive the data through ado.net entiry frameowk by using stored procedure.
In this article, we will see that how we can use stored procedure to retrieve the data from Database using ADO.net Entity framework.
ADO.net Entity framework is an ORM tool that performs Data manipulation without writing any code and it provide us simple API to perform CRUD operation. Entity framework generates strongly objects based on selected tables/views/stored procedure while creating entity model.
In the below steps we will see how we can use stored procedure to retrieve the data through entity framework. I this article I have used Northwind Sample database as an example to retrieve the data.
- 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();
Here is the web page which shows the results returned from stored procedure
Hope this article helped!
Thanks for reading and let me know your feedback, comments