Wednesday, 13 April 2011

Calling an stored procedure using ADO.NET Entity Model (Entity Framework LINQ to SQL)


Hi Guys,
I have seen a lot of question coming for how to Call stored procedure in Entity Framework Model. So i came write this blog. Lets start with it.

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 ok.
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 wizard.


After configuration your model will be added and opened and will show all your tables you have select from the wizard.

1.Now create an storedProcedure in your database.
2. Come back to you solution and open the sampleModel.edmx from the DAL project and right click.
3. 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.
4. Expand the stored procedure item and select your stored procedure from the list.

Now click finish and save the Model.
5. Go to the visual studio menu and  select View->Other Windows -> Entity Data Model Browser

6. Now open the Model Browser and expand EntityContainer and right click on “Fucntion Imports” and click “Add Function Import…”.
7. Name the function “GetAllEmployees” and select the available storedprocedure in the model from the dropdown list GetAllEmployees. Now select the return type so you are going to show the emplyee details so the return type would be the Entity: Employee

8. Click on and save the model.
Now write the code in your business layer to get the returned data from the Stored Prodcedure.
using DAL;
namespace BusinessLayer
{
    public class Employees
    {
        public static List<Employee> GetAllEmployeesInfo()
        {
            btnetEntities data = new btnetEntities();
            var EmpCol = data.GetAllEmployees();
            var EmpItems = from e in EmpCol
                           select new Employee
                           {
                               Eid = e.employee_id,
                               Name = e.employee_name,
                               EMail = e.email

                           };
            List<Employee> ls = new List<Employee>(EmpItems.ToList());
            return ls;
        }
    }
}

In your aspx page bind the grid:
using BusinessLayer;

namespace EFLinqToSqlDemo
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            GridView1.DataSource = Employees.GetAllEmployeesInfo();
            GridView1.DataBind();
        }
    }
}

And you are done.:))



Here’s the output.


11 comments:

  1. Thanks man u really saved my life in this attendance Manager project

    ReplyDelete
  2. 100% verified google adsense account for sale in USA, Europe, Asia, Africa, etc. for info: itsmehacker@hotmail.com
    http://muchmoretips.blogspot.com

    ReplyDelete
  3. Thank you Very much
    It's really help me ....

    ReplyDelete
  4. Thanks a lot. Its really awesome .......

    ReplyDelete
  5. how to insert record using linq to stored procedure

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. Something like this

    --SQL
    create procedure create_emp
    @name nvarchar(50)
    as
    insert into dbo.emp
    (name) VALUES(@name)
    select scope_identity()

    //Entity
    Import the store/function create_emp to the entity

    //C#
    int id;
    btnetEntities data = new btnetEntities();
    id=data.create_emp("Luís Costa");
    var newEmp = data.employees.first(i=>i.eid=id);

    ReplyDelete
  9. Oh God! Finally I found the Answer for my query in a simple and clear way thanks to you man, I love your working style, your work is mindblowing , Thanks man.
    from Abhinavsingh993@gmail.com

    ReplyDelete
  10. Thanks man, but i have a problem. My store procedure, it's include two table, so i don't know a method to take data in table! please help me!

    This is my store procedure
    CREATE PROCEDURE sp_GET_ROOM
    AS
    BEGIN
    SELECT R.ID, R.RoomName, TR.TypeOfRoomName, R.Status, R.Note
    FROM ROOM R, TYPE_OF_ROOM TR
    WHERE R.IDTypeOfRoom = TR.IDTypeOfRoom
    END

    ReplyDelete