Executing Stored Procedures in C# - Part 3: Using Entity Framework and MVC in Best Practices

Previous article: Executing Stored Procedures in C# - Part 2: Monitoring execution using async operations

The field of software engineering is constantly evolving. Technologies, frameworks, and methodologies rapidly change to meet new demands and technological advancements. In this dynamic landscape, C# and .NET continue to stand out, keeping pace with this evolution and providing developers with increasingly powerful tools to create robust and efficient solutions.

In the past, application development often relied on manual and fragmented approaches, such as direct database access via embedded SQL commands. Today, frameworks like Entity Framework and MVC have revolutionized how we develop software, allowing greater abstraction, organization, and scalability. With these technologies, developers can create cleaner, modular, and easier-to-maintain applications, following best practices in software design and architecture.

In the first two articles of this series, we explored how to execute stored procedures directly using ADO.NET, a still valid approach but limited compared to modern possibilities. Now, it's time to take the next step! Let’s integrate stored procedures into Entity Framework, using the MVC pattern to organize our application and follow modern development best practices. Ready to transform how you execute your procedures? Then let's go!

Introduction to Entity Framework and MVC

Entity Framework (EF) is the leading ORM (Object-Relational Mapping) in the .NET ecosystem. It allows developers to work with data as objects, eliminating the need to write SQL manually in most cases. However, there are situations where stored procedures are preferable, whether for performance reasons, security, or to reuse existing logic in the database.

On the other hand, MVC (Model-View-Controller) organizes code into three layers: Models, which represent data; Views, which are the user interfaces; and Controllers, which mediate interaction between the View and the Model. This design pattern separates responsibilities, making the application easier to maintain and evolve.

This article assumes you have a basic understanding of Entity Framework and the MVC pattern. Therefore, we won’t delve too deeply into points that a developer familiar with these technologies would already know how to implement.

Why Use Stored Procedures with Entity Framework?

While Entity Framework offers robust abstraction over the database and allows developers to work intuitively with objects and LINQ queries, there are still scenarios where using stored procedures stands out. They are especially useful in complex operations requiring specific optimizations, ensuring performance and efficiency, especially with large data volumes. Additionally, stored procedures encapsulate business logic and security rules, enabling database administrators (DBAs) to adjust these routines without changing application code.

Entity Framework natively supports executing stored procedures, allowing developers to take advantage of both worlds: the organization and clarity of object-oriented code, combined with the optimization power directly in the database. This way, it's possible to integrate these routines simply, keeping the code clean and aligned with modern development best practices, without sacrificing the performance needed for critical operations.

The Stored Procedure

In the last two articles, we produced two stored procedures for our implementation examples. You could use either to test the implementation shown below, but I suggest creating a new, simpler and more straightforward one. A note: since our goal in this article is only to show how to integrate the procedure call into the framework, we’ll simplify things by executing the procedure synchronously. Here it is:

CREATE OR ALTER PROCEDURE RunningProcedureMVC
    @TaskName NVARCHAR(50) -- Receives the task name
AS
BEGIN
    DECLARE @progress INT = 0;

    -- Insert the process
    INSERT INTO Processes (TaskName, ProgressPercent, LastUpdated)
    VALUES (@TaskName, 100, GETDATE());

END;        

As you can see, it simply inserts a record into a table called Processes.

I recommend using the Database First approach to create the database from the model we’ll set up below. We won’t cover this here, as it's beyond our objective, but if you have trouble with it, you can manually create the table using the command below:

CREATE TABLE [dbo].[Processes] (
    [ProcessId]       INT            IDENTITY (1, 1) NOT NULL,
    [TaskName]        NVARCHAR (MAX) NULL,
    [ProgressPercent] INT            NOT NULL,
    [LastUpdated]     DATETIME2 (7)  NULL,
    CONSTRAINT [PK_Processes] PRIMARY KEY CLUSTERED ([ProcessId] ASC)
);        

Configuring Entity Framework for Stored Procedures

For our example, we’ll need to add the following dependencies to our project:

• Microsoft.Data.SqlClient
• Microsoft.EntityFrameworkCore
• Microsoft.EntityFrameworkCore.SqlServer
• Microsoft.EntityFrameworkCore.Design (if you use the Database First approach)

I’ll assume that the connection string has already been set up in the appsettings.json file and that the connection has been prepared in Program.cs or Startup.cs. We will focus here only on the three MVC layers in our example. But before, let’s create our connection class.

The Model Layer

Let's start by creating our model layer. The idea here is this: since we’ll execute a procedure that inserts into a table that will record processes, we’ll only model a Process class, and the entire execution will involve manipulating this class (and naturally, the related table).

The class will have the following structure:

public class Process
{
    public int ProcessId { get; set; }
    public string? TaskName { get; set; }
    public int ProgressPercent { get; set; }
    public DateTime? LastUpdated { get; set; }
    public Process()
    {
        TaskName = null;
        ProgressPercent = 0;
        LastUpdated = null;
    }
}        

Configuring the Context

To execute the stored procedure in Entity Framework, we first need to ensure our DbContext is correctly configured to access the database. The DbContext is the main class responsible for the interaction between the application and the database.

Here’s a basic example of how we can configure it:

public class MyDbContext : DbContext
{
    public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

    public DbSet<Process> Processes { get; set; }

    // Method to execute a stored procedure
    public async Task<int> ExecuteStoredProcedureAsync(string taskName)
    {
        return await Database.ExecuteSqlRawAsync($"EXEC RunningProcedureMVC @taskName",
            new SqlParameter("@taskName", taskName));
    }

}        

Note two things in this code: first, we are creating a collection of Process objects in our data context. This will allow us to access its data, respecting the layer separation pattern.

Second, we created a function called ExecuteStoredProcedureAsync. It uses the ExecuteSqlRawAsync() method to execute the stored procedure in the database. This is necessary since this operation falls outside our usual CRUD model. Note that we pass a SqlParameter to protect against SQL Injection attacks.

The Controller Layer

Now that we have the context configured, let’s create the controller that will be responsible for calling the stored procedure. In the MVC pattern, controllers manage the application’s logic and connect the model with the user interface.

Here’s a simple example of a controller that calls the stored procedure:

public class ProcessController : Controller
{

    private readonly AppDbContext _context;

    public ProcessController(AppDbContext context)
    {
        _context = context;
    }

    [HttpPost]
    public async Task<IActionResult> Execute()
    {
        string taskName = "Task_" + DateTime.Now.ToString("yyyyMMdd_HHmmss");

        var execute = await _context.ExecuteStoredProcedureAsync(taskName);

        if( execute == 0)
        {
            return StatusCode(500, "Error creating task.");
        }

        var process = await _context.Processes.FirstOrDefaultAsync(p => p.TaskName == taskName);

        return View(process);
    }

}        

In this example, the controller uses the DbContext to call the stored procedure and returns the result to the View, which can be used to display feedback to the user.

The View Layer

Finally, the presentation layer (View) will be responsible for displaying the execution results. Let’s create a basic View, named Execute.cshtml, which receives the result from the controller and displays it to the user:

@model RunningSyncStoredProceduresMVC.Models.Process

<p>Stored procedure executed successfully. Results:</p>

<p><b>Task Name:</b> @Model.TaskName</p>
<p><b>Progress Percent:</b> @Model.ProgressPercent</p>
<p><b>Last Updated:</b> @Model.LastUpdated</p>
@model int

<h2>Stored Procedure Result</h2>
<p>Stored procedure executed successfully. Result: @Model</p>        

Here, the view simply displays the result returned by the stored procedure in a simple and direct way.

Running the MVC Application

If the steps were followed and the application was correctly configured, when running the Execute routine, we will see a page like this:

Wow! We did it! We executed the procedure using Entity Framework and the MVC pattern, and even displayed the data of the task we just ran in a view!

But if something went wrong and you were unable to run the application, take a look at the version I put on my GitHub for guidance by clicking here.

In Conclusion…

Integrating stored procedures with Entity Framework within an MVC application allows us to maintain the modern structure and organization of the application without sacrificing the efficiency and power of stored procedures. The use of the MVC pattern ensures a scalable and easily maintainable application, while Entity Framework facilitates interaction with the database, adhering to best practices in development.

Throughout these three articles, we explored the fascinating universe of executing stored procedures in C#, from the basic implementation, through monitoring execution asynchronously, to integrating with modern frameworks like Entity Framework and MVC. With these tools, you are now prepared to take your applications to a new level of robustness and efficiency, utilizing good and secure stored procedures in modern and agile applications!

Until next time, and happy coding! ??