Calling an stored procedure using ADO.NET Entity Model
Create new asp.net website in your solution. Now add new project as class
library named as Business Layer. Add a new project again and name it DAL.
Add the reference of BusinessLayer to the asp.net website project. Similarly add
the reference of DAL project to BusinessLayer. This all we are doing as we are
trying to create a layered architecture for the sample application.
Now right click on DAL project and add new item -> ADO.NET Entity Model click
Now it'll ask you to configure your Model from the database. Here I'm skipping
this step assuming that you may know how to configure the model using the
After configuration your model will be added and opened and will show all your
tables you have select from the wizard.
- Now create an stored Procedure in your database.
Come back to you solution and open the sampleModel.edmx from the DAL project and right click.
Click on "Update the model from database" it'll open the same wizard that was appeared while you were adding the Model. But this time it'll only show the newly added item in the database.
- Expand the stored procedure item and select your stored procedure from the list.
Now click finish and save the Model.
- Go to the visual studio menu and select View->Other Windows -> Entity Data Model Browser
- Now open the Model Browser and expand Entity Container and right click on "Fucntion Imports" and click "Add Function Importâ€¦".
- Name the function "GetAllEmployees" and select the available stored procedure in the model from the dropdown list GetAllEmployees. Now select the return type so you are going to show the employee details so the return type would be the Entity: Employee
- Click on and save the model.
Now write the code in your business layer to
get the returned data from the Stored Procedure.
public class Employees
public static List<Employee>
btnetEntities data =
var EmpCol =
var EmpItems =
from e in EmpCol
select new Employee
Eid = e.employee_id,
Name = e.employee_name,
EMail = e.email
ls = new List<Employee>(EmpItems.ToList());
In your aspx page bind the grid:
public partial class _Default
GridView1.DataSource = Employees.GetAllEmployeesInfo();
And you are done.:))
Here's the output.