Execute Stored Procedures In MVC Core Using Entity Framework Core

Entity Framework Core allows us to execute Stored Procedures from .NET Core applications. Entity Framework Core provides the following two methods to execute Stored Procedures.

1. FromSql() Method
2. ExecuteSqlCommand() Method

The first method, FromSql() is used to execute a Stored Procedure that retrieves records from database tables and the second method, ExecuteSqlCommand() is used to execute a Stored Procedure that inserts, updates, or deletes records from a database. The second method, ExecuteSqlCommand() executes a Stored Procedure and returns an integer value that indicates the number of rows affected. To execute a Stored Procedure from .NET Core web application using database first approach, we need to follow the following steps.

1. Create a Stored Procedure
2. Create MVC Core Web Application
3. Create a Model of Stored Procedure
4. Set Model of Stored Procedure in DbContext Class
5. Execute Stored Procedure
6. Create View

Create a Stored Procedure

To create a Stored Procedure, follow the following steps:

• Open SQL Server Management Studio 2017, and in the Object Explorer pane expand databases folder and select the database name in which you want to create your Stored Procedure.
• Now expand the programmability folder under your selected database name, then right click on the Stored Procedures folder. When you right click on the Stored Procedures folder, it will open a Query editor that contains a prototype for your Stored Procedure.
• Now put your Stored Procedure Query and parameters if necessary and press the execute option from the main menu of SQL Server Management Studio. When you press the Execute option, your Stored Procedure will be created in the underlying database.

Suppose you have the following Stored Procedure,

USE [EmployeesDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[RetrieveEmployeeRecord]

 @EmpCode nvarchar(50)

AS
BEGIN
    SET NOCOUNT ON;

SELECT EmpCode, EmpFullName, EmpGender, EmpBirthDate, EmpJoiningDate, EmpMobileNumber, EmpEmailAddress, EmpPicture, BranchName, DepartmentName
from EmployeeGeneralDetails e inner join Branches b on e.EmpBranchCode = b.BranchCode
inner join Departments d on e.EmpDepttCode = d.DepartmentID
where EmpCode = @EmpCode
END

The name of the above Stored Procedure is RetrieveEmployeeRecord and it retrieves records from three tables that are EmployeeGeneralDetails, Branches and Departments using inner join Query.

Create MVC Core Web Application

To create an MVC Core web application, open Visual Studio.NET 2017 or later version, open New Project and select ASP.NET Core Web Application template, give a name to your MVC Core application project and press the OK button. Suppose the name of your MVC Core web application is ProcedureTest. When you created your MVC Core web application project, all the necessary files and folders are displayed in the Solution Explorer window located on the right side pane of the Visual Studio.

Create a Model of Stored Procedure

Now, create a model class that shows all the projected fields or columns of the Stored Procedure you want to execute. In model class, define properties for each projected column of the Stored Procedure. The projected columns or fields means the number of columns you want to retrieve from database tables.

When you create MVC Core web application, then from the Solution Explorer window, right click on the Models folder of your project, and create a new class. Now give name to your model class. You can give any name to your model class but usually it is same as the name of your Stored Procedure.

Since the name of our created Stored Procedure is RetrieveEmployeeRecord therefore, the model class name is also should be RetrieveEmployeeRecord. Following is the code of the model class for the above Stored Procedure,

using System;
using System.ComponentModel.DataAnnotations;

namespace ProcedureTest.Models
{
    public class RetrieveEmployeeRecord
    {
        [Key]
        public string EmpCode { get; set; }
        public string EmpFullName { get; set; }
        public string EmpGender { get; set; }
        public DateTime? EmpBirthDate { get; set; }
        public DateTime? EmpJoiningDate { get; set; }
        public string EmpMobileNumber { get; set; }
        public string EmpEmailAddress { get; set; }
        public string EmpPicture { get; set; }
        public string BranchName { get; set; }
        public string DepartmentName { get; set; }
    }
}

The model class must be designed according to the query of the Stored Procedure. The projected fields or columns of the Stored Procedure must be the same as the properties of the model class. Each property of the model class is related with each field or column of the Stored Procedure.

Set Model of Stored Procedure in DbContext Class

After creating the model class, now open your data context class and add new DbSet with your created model class. The data context class is a class which is derived from the DbContext class. Following is the DbSet for the above model class:

public virtual DbSet<RetrieveEmployeeRecord> RetrieveEmployeeRecord {get; set;}

Execute Stored Procedure

To execute a Stored Procedure the FromSql() method or ExecuteSqlCommand() method is used. Here we are using the FromSql() method because we are executing a Stored Procedure that retrieves record from database tables. To execute Stored Procedure using FromSql() method, open the HomeController of your project and put the following code in the Index action method,

public IActionResult Index(string id) {
    var empRecord = dbContext.RetrieveEmployeeRecord.FromSql($ "RetrieveEmployeeRecord {id}").ToList();
    return View(empRecord);
}

The name of the Stored Procedure is RetrieveEmployeeRecord and the name of the model class is also RetrieveEmployeeRecord. The dbContext.RetrieveEmployeeRecord indicates the model class. The FromSql() method takes the Stored Procedure name as argument that is FromSql($"RetrieveEmployeeRecord {id}"). The {id} indicates the parameter of the Stored Procedure. If you want to execute a Stored Procedure that does not take any parameter then use the FromSql method as FromSql($"RetrieveEmployeeRecord"). If your Stored Procedure takes more than one parameter then you can use multiple parameters in the FromSql() method separating by comma. Suppose your Stored Procedure takes two parameters then you can use the FromSql() method as bellow,

FromSql($"RetrieveEmployeeRecord {param1}, {param2}")

In the above FromSql() method, the {param1} indicates the first parameter of the Stored Procedure and {param2} indicates the second parameter of the Stored Procedure.

Create View

Now open the view file Index.cshtml and put the following code,

@model IEnumerable< ProcedureTest.Models.RetrieveEmployeeRecord>
<style type="text/css">
@@media only screen and (min-width: 300px) {
table, th, td {
   border: 1px solid black;
   text-align: center;
}
th {
   height: 50px;
   background-color: darkgray;
   color: white;
   font-size: 14px;
   white-space:nowrap;
}
tr {
   height: 30px;
}
tr:nth-child(even) {
   background-color: #f2f4f5;
}
td {
   font-size: 12px;
   white-space: nowrap;
}
}
</style>
<div class="row">
    <div class="col-md-12">
        <table>
            <thead>
                <tr>
                    <th>Code</th>
                    <th>Name</th>
                    <th>Gender</th>
                    <th>Birth Date</th>
                    <th>Joining Date</th>
                    <th>Mobile Number</th>
                    <th>Email</th>
                    <th>Branch</th>
                    <th>Department</th>
                    <th>Picture</th>
                </tr>
            </thead>
            <tbody>
@foreach (var item in Model)
{

                <tr>
                    <td>@item.EmpCode</td>
                    <td>@item.EmpFullName</td>
                    <td>@item.EmpGender</td>
                    <td>@item.EmpBirthDate.Value.ToString("dd/MM/yyyy")</td>
                    <td>@item.EmpJoiningDate.Value.ToString("dd/MM/yyyy")</td>
                    <td>@item.EmpMobileNumber</td>
                    <td>@item.EmpEmailAddress</td>
                    <td>@item.BranchName</td>
                    <td>@item.DepartmentName</td>
                    <td>
                        <input type="image" src="~/Images/@item.EmpPicture" style="width:120px; height:80px;" />
                    </td>
                </tr>
}

            </tbody>
        </table>
    </div>
</div>