Tuesday, February 23, 2010

Call Stored Procedure from Entity Framework


Microsoft have introduced entity framework to connect to the database. When you work with the database, you have to call your stored procedure. This blog explain you how to execute your stored procedure from Entity Framework.

Assume that your stored procedure insert/update/delete record into database. It returns nothing just do as per the parameter value.

Follow the below steps:
1. Open Entity Framework Model page in Visual Studio IDE
2. Right click on blank area and choose “Add” -> “Function Import” option
3. In Add Function Import dialog, select your stored procedure, give function name and choose return type
4. Click on Ok button to add the function in Entity Framework
5. In your page, Create instance of entities class and call your function with proper parameter value

If your stored procedure is return multiple record then you have to create one custom entity, which have the same schema as your stored procedure return the data. To create the custom entity, right click on Entity Framework Model page and choose “Add” -> “Entity…” It will open “Add Entity” dialog box where you have to give Entity Information.

You can add/edit/delete properties into custom activity. To add, Select “Scalar Properties” section under custom activity, right click and choose “Add” -> “Scalar Property”.

Just create custom entity. After creating custom entity, Add Function Import to your entity framework model.

Follow the below steps:
1. Open Entity Framework Model page in Visual Studio IDE
2. Right click on blank area and choose “Add” -> “Function Import” option
3. In Add Function Import dialog, select your stored procedure, give function name and choose return type as Entities and select your custom entity from dropdown list.
4. Click on Ok button to add the function in Entity Framework
5. In your page, Create instance of entities class and call your function with proper parameter value


TestDBEntities1 testdb = new TestDBEntities1();
var p = from d in testdb.GetOrderInfo() select d;

2 comments:

  1. Thank you very much. I donot get result. when i created complex type as return type i got result

    sebastian
    My mail : iamseban@gmail.com

    ReplyDelete
  2. Hi sebastian,

    In which format you wanted to get back the result from the SP, if its a scalar type you don't need to create complex type, but if its a recordset you are returing then you need to create a complex type to get the result in your app.

    ReplyDelete

DotNet Code Guru