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

Previous article: Executing Stored Procedures in C# - Part 1: Execution and Feedback

Imagine that your C# program needs to execute a stored procedure that might take several seconds or even minutes to complete. During this time, it simply freezes, without responding or showing any progress. In this situation, the user, unsure whether the program is still running or has crashed, may even interrupt the execution while the stored procedure is still running. Have you ever encountered this? How can we resolve this in a way that the application continues working and, even better, displays progress updates in real-time?

In the previous article, we discussed the synchronous execution of stored procedures and how this approach can be limiting, especially when dealing with long-running operations. In this one, we will take it a step further and show how to overcome this limitation, displaying progress messages on the screen in real-time WHILE THE STORED PROCEDURE IS STILL EXECUTING!

Quick summary of what we've covered

In the first part of this article, we explored the concept of stored procedures and learned that T-SQL is Microsoft's language used in SQL Server and Azure SQL Database for developing these routines. We also explored how to execute stored procedures in C# in a simple and direct way, creating a stored procedure and learning how to access it via C#. We learned how to open a connection to the database, define a procedure, and execute it using the ExecuteNonQuery() method. Additionally, we discussed using SqlCommand to manage the execution of these routines within a try..catch block, ensuring that potential errors were properly captured and handled.

However, when executing the stored procedure synchronously, we encountered a limitation: the program becomes blocked during execution. It calls the T-SQL routine and freezes until it finishes. In the end, it may even display all the messages that occurred during execution, but all at once (which we indeed did). In other words, the user does not receive any visual feedback until the process is complete, giving the impression that the application is frozen.

Let’s solve that right now!

The long-running stored procedure

Now, let's take a step further and implement a stored procedure that simulates a long-running task, monitoring progress in real-time. The idea is to create a routine that updates a progress table during execution, allowing us to track the task's progress directly in the database.

This stored procedure, called LongRunningProcedureAsync, uses a table named ProgressTable that records the percentage of progress during execution. The C# program will be able to query this table during execution and display the progress to the user.

Additionally, since it's an asynchronous process, we will also allow multiple instances of the program to run simultaneously, calling the same routine! To manage this, we'll track which instance of the program called which routine. Each instance generates a task identifier, passed as a parameter to the T-SQL, allowing each task to query only its specific progress. Simple, right?

First, let's create the table:

CREATE TABLE ProgressTable (
    Id INT IDENTITY(1,1) PRIMARY KEY, -- Auto-incrementing primary key
    TaskName NVARCHAR(50) NOT NULL,   -- Name of the task or operation being monitored
    ProgressPercent INT NOT NULL,     -- Progress percentage (0 to 100)
    LastUpdated DATETIME NOT NULL DEFAULT GETDATE() -- Timestamp of the last update
);        

Now, here’s the stored procedure code. On each iteration, the progress increases by 10%, and the routine waits for 2 seconds before continuing, simulating a long execution:

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

-- Check if a record already exists for the task and insert if not
IF NOT EXISTS (SELECT 1 FROM ProgressTable WHERE TaskName = @taskName)
BEGIN
    INSERT INTO ProgressTable (TaskName, ProgressPercent, LastUpdated)
    VALUES (@TaskName, 0, GETDATE());
END

-- Simulate task progress in a loop
WHILE @progress < 100
BEGIN
    -- Increase progress
    SET @progress = @progress + 10;

    -- Update the ProgressTable
    UPDATE ProgressTable
    SET ProgressPercent = @progress, LastUpdated = GETDATE()
    WHERE TaskName = @TaskName;

    -- Simulate a delay between task steps (2 seconds here)
    WAITFOR DELAY '00:00:02'; -- 2-second delay to simulate work being done
END        

To test this routine in the terminal, just execute it with a task identifier. In the example below, we use "Test" as the parameter. The execution should take about 20 seconds:

EXEC LongRunningProcedureAsync "Test"        

Now that we have the stored procedure ready, let’s move on to the implementation of the routine in C#.

C# Code for Asynchronous Execution

The goal of the program here is to connect to the database, execute the stored procedure asynchronously, and simultaneously query the progress table, displaying the completion percentage of the task.

But first, we need to understand the concept of thread. A thread is like a line of execution within a program. It can run at the same time as other threads. Think of a thread as a worker on a team: while one worker is busy with a task, others can be performing different activities. This helps the program do more than one thing at a time.

In a simple program that runs synchronously, only one thread, the main one, is executed. But we will need to change that in this application.

Let's start by creating a console application, just like we did in the previous example, but this time we will make some modifications to run the execution asynchronously. The first change is in the declaration of the Main() function. Observe:

using System;
using System.Data; // Necessário para usar a classe Data
using System.Data.SqlClient; // Necessário para usar SqlConnection e SqlCommand

class Program
{
    static async Task Main()
    {

    }
}        

What changed:

Asynchronous Method (async): The async modifier indicates that the method can execute tasks asynchronously. This allows the program to continue executing other operations while waiting for the completion of a time-consuming task, such as executing a stored procedure. This is a mandatory clause for any function that needs to run an asynchronous task.

Return Type Task: The Main() function now returns a Task instead of having no return value (as it would with void). This return type is essential because both the execution of the stored procedure and the progress monitoring are performed asynchronously. In C#, a Task represents an operation that may be in progress or will be completed in the future. By returning a Task in Main(), we inform the system that there are ongoing operations that need to be awaited.

Now, let’s ensure that our application is set up correctly to support asynchronous execution. Note that, in addition to defining the connection string and the name of the stored procedure, we will also generate a unique identifier for our task (taskName) using the current date and time. This is important for monitoring simultaneous tasks and avoiding confusion between different executions.

string connectionString = "Put your Connection String here";

// Generate the task name using date and time
string taskName = "Task_" + DateTime.Now.ToString("yyyyMMdd_HHmmss");
string storedProcedureName = "LongRunningProcedureAsync";        

Now, let's move on to the actual execution of the stored procedure:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    try
    {
        // Open the connection
        await connection.OpenAsync();

        // Execute the stored procedure in a separate task
        var procedureTask = Task.Run(() => ExecuteStoredProcedureAsync(connection, storedProcedureName, taskName));

        // Monitor the progress in a separate task
        var monitorTask = MonitorProgressAsync(connectionString, taskName);

        // Wait for the completion of both tasks
        await Task.WhenAll(procedureTask, monitorTask);
    }
    catch (Exception ex)
    {
        Console.WriteLine("Error: " + ex.Message);
    }
}        

Note that this snippet, in addition to opening the connection and error handling, has significant changes compared to the synchronous program we developed in the previous article. Let’s take a look:

await

Notice the use of this keyword in the invocation of some methods. It is essential for asynchronous behavior in C#. When you use await before a method call, you are instructing the program to "wait" for the completion of the task in a non-blocking way. In other words, the program will invoke the function preceded by await, and while waiting for its completion, it will allow the application to continue executing other operations (such as rendering the screen, responding to interface events, or performing other background tasks). Once the called function completes its execution, the flow of the current block will resume.

It is important to note that the called function also needs to be asynchronous for the program not to be blocked. For this, the function must return a Task. We will soon see an example of this.

await connection.OpenAsync();

Here we are opening the connection to the database. Note the use of the await clause, indicating that this connection function should also run asynchronously. OpenAsync() is the asynchronous version of Open() from SqlConnection.

var procedureTask = Task.Run(() => ExecuteStoredProcedureAsync(connection, storedProcedureName, taskName));

Here we are calling a function that will be executed asynchronously in a different thread from the main one where the program is running. This is necessary because this task may take time, and we do not want it to freeze the program. Task.Run is the method used to execute a new task on a separate thread. ExecuteStoredProcedureAsync is the function that will be triggered.

var monitorTask = MonitorProgressAsync(connectionString, taskName);

Here, we are calling another asynchronous task, MonitorProgressAsync, to monitor the progress of the stored procedure. However, unlike the previous one that uses Task.Run, we simply call the function without opening another thread for it, since this task will not take as long; it will just be querying the progress of the main function. Thus, we won’t unnecessarily consume resources by creating another thread.

await Task.WhenAll(procedureTask, monitorTask);

Task.WhenAll is used to combine multiple asynchronous tasks into a single operation. It accepts one or more Task objects as arguments and returns a new task that represents the completion of all provided tasks. The interesting thing here is that it, too, is an asynchronous function (hence it is preceded by await), and it will only proceed in the flow of the current execution block when both functions, procedureTask and monitorTask, are completed. Interesting, right?

With that, our Main() function is ready. But we still need to implement the functions that are called asynchronously. Let's start with the one that executes the stored procedure.

// Asynchronous method to execute the stored procedure, passing the task name as a parameter
static async Task ExecuteStoredProcedureAsync(SqlConnection connection, string storedProcedureName, string taskName)
{
    using (SqlCommand command = new SqlCommand(storedProcedureName, connection))
    {
        command.CommandType = CommandType.StoredProcedure;

        // Pass the task name to the stored procedure
        command.Parameters.AddWithValue("@TaskName", taskName);

        // Increase the execution timeout of the stored procedure (for example, 5 minutes)
        command.CommandTimeout = 300; // Timeout of 300 seconds (5 minutes)

        Console.WriteLine("Calling the stored procedure...");

        // Execute the stored procedure asynchronously
        await command.ExecuteNonQueryAsync();

        Console.WriteLine("Stored procedure finished.");
    }
}        

Nothing different from what we did in the previous version of the program when our routine was synchronous. We created the command, set it as a StoredProcedure, passed the name of our task as a parameter, and executed it (printing messages to the console before and after its execution for control). Note that this time, the command call is made with an asynchronous function: ExecuteNonQueryAsync().

Next, let’s create the function to monitor the stored procedure:

// Asynchronous method to monitor progress
static async Task MonitorProgressAsync(string connectionString, string taskName)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        await connection.OpenAsync();

        // Store the last displayed progress
        int? lastProgress = null;

        while (true)
        {
            using (SqlCommand command = new SqlCommand("SELECT ProgressPercent FROM ProgressTable WHERE TaskName = @TaskName", connection))
            {
                command.Parameters.AddWithValue("@TaskName", taskName);

                // Execute the query asynchronously
                var progress = (int?)await command.ExecuteScalarAsync();

                if (progress.HasValue)
                {
                    // Only display progress if it is different from the last displayed value
                    if (progress != lastProgress)
                    {
                        Console.WriteLine($"Progress of task '{taskName}': {progress}%");
                        lastProgress = progress;
                    }

                    // If the progress reached 100%, the task is complete
                    if (progress == 100)
                    {
                        break;
                    }
                }
            }

            // Wait a few seconds before checking again
            await Task.Delay(1000); // 1 second
        }
    }
}        

In this function, we create a separate connection. This was necessary because SQL Server does not allow a stored procedure to run on one connection while another operation is executed on the same connection. Therefore, we needed to ensure that the monitoring connection was different from the execution connection.

We then query the ProgressTable to check the progress through a command called by the ExecuteScalarAsync() function. At this point, you should already understand that this is the asynchronous version of ExecuteScalar(), a method that retrieves information from a database. And, of course, it needs to be preceded by an await.

Finally, we write the progress of the process to the console.

Done! The program is finished.

If the steps have been followed correctly, it should work. In case of any doubts, this program can be retrieved from my GitHub through this link here.

If you run the program now, you will see the magic happen:

Great! We're able to monitor the execution of the stored procedure in real time!

We did it!

Now we can monitor the execution of our T-SQL processes without interrupting the system's operation!

Awesome, right?

But wait!

There's always a "but"...

We made the routine run perfectly, but we implemented a traditional console program, old school, without the modern development techniques that are now fundamental, such as the use of Entity Framework, MVC, and other frameworks that promote a more robust and scalable architecture.

In the third and final article of this series, we will explore how to integrate these technologies into our project, leveraging their features to create a more powerful application aligned with contemporary development practices.

See you next week! Bye and happy coding! ??

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