Ways to call Stored procedure using Entity Framework in ASP.NET MVC3 Razor
- In this article we are concentrating on two ways by which we can call stored procedure using entity framework.
- Entity Framework performs below expectation when fetching large data, so often we use stored procedure to fetch data.
- In the application where one has used entity framework, one can use entity framework itself to call stored procedure.
Demo
Lets see what are these two ways:
Way 1:
This is probably the simplest method to call a stored procedure with minimal effort.
We have created a simple stored procedure which fetches the registered user to the application. The stored procedure looks like below:
In the above image above is the stored procedure and below is the result that stored procedure returns.
using (SampleAPPEntities dataContext = new SampleAPPEntities())
{
List<Register> userList = dataContext.ExecuteStoreQuery<Register>("RegisteredUsers").ToList();
}
In the above code, we have created a data context object. We have used ExecuteStoreQuery method of data context to fetch the data. The method accepts a parameter which is the name of the stored procedure to call. We are retuning list of type Register class which maps to the Register table in database whose data we are retreiving using stored procedure.
Way 2:
In this method, we first add stored procedure to the .edmx file. The function inport for the stored procedure is created, function import creates a function which uses the stored procedure.
Step 1: Adding stored procedure to EF
Open the edmx file, right click on it, a dialog box will appear as shown below:
Click on Update Model from Database option which will open another dialog box as shown below:
Select the stored procedure and click the Finish button. This will add the stored procedure to the solution or to the edmx file.
Step 2:
In this step we will see how to add funtion import for the stored procedure.
Right click on the stored procedure added, this will open a window as shown below:
The function import name field is pre populated, you can give the function name you want, the stored procedure dropdown has the stored procedure for which you want to add function import.
Select the Entities radio button and select the entity to which you want to map the stored procedure. The stored procedure will return the result of the selected entity type. On clicking ok the funtion import will be created for stored procedure.
using (SampleAPPEntities dataContext = new SampleAPPEntities())
{
List<Register> userList1 = dataContext.GetRegisteredUsers().ToList();
}
We can use the funtion import created to call the stored procedure to get the data from Database.So, these are the two ways by which the stored procedure can be called using entity framework.
can I download the code somewehre?
ReplyDeleteI'm puzzled as to where this code goes? is it in the view?
using (SampleAPPEntities dataContext = new SampleAPPEntities())
{
List userList = dataContext.ExecuteStoreQuery("RegisteredUsers").ToList();
}
hi mark,
ReplyDeletecan you please follow the demo link in the article and watch the video.
You will get the place where to have above lines.
Thanks