Hello ! In this article, we will see how to use Store Procedure via Entity Framework.
There are 2 ways of use :
1-) By defining the Store Procedure related to Create, Delete or Update of one of your entity object.
2-) If you want to use the Store Procedure uploaded in your model anytime, by defining it in the Function Imports folder.
Now, let’s think of a situation up where we can see the application of these 2 types of use.
Let’s have a Database hosting a table named Category, and let’s use the Store Procedure while adding a new entry to this table.
First, let’s create our Category table in the Database as below:
After creating our table, now we can start to prepare our Store Procedure which we will be using for the new entry. I chose to name it as sp_add_category.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[sp_add_category]
(
@Slug nvarchar(250),
@Name nvarchar(250),
@Active bit,
@ImageUrl nvarchar(100),
@ParentCatId int
)
AS
BEGIN
SET NOCOUNT ON;
insert into Category (Slug, Name, Active, ImageUrl, ParentCatId)
values
(@Slug, @Name, @Active, @ImageUrl, @ParentCatId)
END
after completing our operations on Database side, now it’s time to go back to Visual Studio platform. I suppose you have already added an Entity Date Model to your project.
Then, let’s add the Category table and Store Procedure to our Entity Data Model from our Database.
Click right in your Model Browser and continue choosing the
Update Model From Database option. At this stage, tick the boxes next to Tables and Store Procedures within the Add tab. We have now in the database a table and a store procedure that are not yet available in the DataModel.
Yes, we will see the window below appear on the screen :
after ticking the related boxes, click on the Finish button. After the window is closed, your Model Browser will be changed as below :
after adding successfully our items in the Entity Model, let’s remember what we wanted to do in the first place : to use Store Procedure while adding a new entry to the Category table instead of using a query sentence at SQL from our Entity Model.
As I told you at the beginning of this article, we have 2 ways of performing it.
1. Defining the Store Procedure related to Create, Delete or Update of one of your entity object
To define the store procedure related to our Category Entity as insert function,
click right on
Category Entity object, then click on the
Stored Procedure Mapping option appearing on the new window.
After clicking on this menu, a Mapping Details panel will be appearing at the bottom of the screen. On the left of this panel, there are two tabs one under the other, we will tick on the option "Map Entity to Functions".
After selecting the Map Entity to Functions option, you will see 3 function types such as below. (Insert, Update, Delete) we can connect the Store Procedure we want to these functions.
What we are interested in is the
Insert Function, as you can see in the animated picture below, when we click on dropdown, the store procedure named
'sp_add_category' that we have uploaded to the Entity Model from DB is listed. We tick it and then first click Ctrl + S to update our Model, then we build our project.
By this means, from now on the new entry will be added through the Store Procedure instead of a the usual SQL query via Entity Model.
IMPORTANT : you must be aware of a particular point. If the names of the parameters you have in your Store Procedure do not correspond with the names in the table, i.e, with the names of the properties in your entity object, then when you come to the third step on the image above, you will need to indicate in the property tab which of the parameters correspond with the properties in the entity object.
2. To use the Store Procedure uploaded in your model anytime, by defining it in the Function Imports folder.
In order to perform this, click right on the
Function Imports folder which is under the
EntityContainer tab which is under the Model Browser on the right of the Entity Model you have updated from the Database, then tick the
Add Function Import option. (EntityModel > Model Browser > EntityContainer > FunctionImports).
On the next window, you will be asked to indicate which store procedure you will add and to enter a function name that you will be using on a C# basis. You can see the related steps on the image below and you can also find a code template to demonstrate how to use it on C# side.
As we can see on the above code template, we can get direct access and use to our function via the instance taken from our Entity model.
Hope it’s been helpful for you guys, and see you soon in my next article.
Abone Ol