Calling a Stored Procedure from Entity Framework

Dear readers,

In the previous posts on entity framework, we saw some basic tutorials on programming using Entity Framework. This post is about calling a stored procedure using Entity Framework. I assume that you have idea in creating .edmx and write stored procedures in SQL server.

1) Write a stored procedure and execute it in your data base, In my case I have written a simple SP to delete the records with odd Numbered Primary Keys from a table.

0StoredProc

Stored Procedure

2) Then Go to your application and open the .edmx file>> right click and select Update Model from database.

1UpdateModelFromDB

Update Model From Database

3) An update wizard will open. In which you can select the stored procedure that you would like to execute through Entity Framework >> Click finish.

2AddTheStoredProc

Add the SP from Update Model Wizard

4) Now open the model browser by right clicking the .edmx file and selecting Model browser. You will now be able to see the your stored procedure under (Second red box in model browser) yourmodel.store tree view. You will be able to see another parent tree just above that in the name of your model, traverse to Entity Container >> Function imports (first red box in the model browser).

3ModelBrowser

Open the Model Browser

5) Right click Function Imports and select “Add Function import“. The below wizard will open in which you need to give a name to sp and then select the SP that you want to call from your code. Provide the return parameters. if any(In my case I am not using any return type in my SP). Click “ok“.

5AddFuctionWizard

Open the Add Function Import Wizard

6) Go to the fuction from where you want to execute it and call the ExecuteFunction method by passing the params as string array, if any. In the code snippet below, I have called my “DeleteOddAddress” SP.

6ContextExecuteFunction

Call the Execute function from the code

The result for this execution returns 0 records since I do not have any odd numbered address ids in my table.

Result Console

Result Console

Hope you find this useful. Happy Learning Folks!!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s