Stored procedures in Entity Framework

Before start reading this i recommend to go through my blog on CRUD operation using entity framework.

Calling a stored procedure in entity framework is different from calling a table or view. Stored procedure can't call directly. In the following topic i will explain step by step how to call stored procedure and how to map returned result of a stored procedure to entity class.

I am continuing by assuming readers are familiar with basic crud operation using entity framework. If not please click here to know the basics of crud operation with entity framework.

I am explaining with an EDMX which is connected to Northwind database. I will call a simple stored procedure which will insert CompanyId, CompanyName, ContactName to Customers table of Northwind database.

In Northwind database i created a stored procedure to insert the above mentioned customer details as shown below,

Create proc [dbo].[InsertCustomers]
@CustomerId varchar(50),
@CompanyName varchar(50),
@ContactName varchar(50)
as
insert into Customers
(
CustomerId,
CompanyName,
ContactName
)
values
(
@CustomerId,
@CompanyName,
@ContactName
)

Before we call this stored procedute we have to model it.

1. Open the EDMX (Double click on EDMX file ).
2. Right click on the empty of the designer surface and select "Update Model From Database" as shown below.


3. Expand stored procedure and select the stored procedure you want to use as shown below.

I selected "InsertCustomers" stored procedure. Click finish button.

4. on designer surface click right button of mouse and from the add context menu select "Function Import" as shown below,

5. On Add Function Import wizard select "InsertCustomers" as stored procedure name and type a name in "Function Import Name" textbox as shown below,




6. Click on "Get Column Information". If your procedure returns any columns it will display the column information. Here i am just inserting a record so "Get column Information" return nothing.

7. Click OK button.

Now go to your button save event and create object of entity class and call the function by passing the parameters(The name you given in "Add Function Import" wizard as shown below.

private void btnSave_Click(object sender, EventArgs e)
{
using (NorthwindEntities1 obj = new NorthwindEntities1())
{
obj.InsertCustomersData("CID", "Name", "Contact");
}
}

That's all. Your save button click event now save the data to the data store (Table).