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

Stored procedures are essential tools in the arsenal of a software developer dealing directly with relational databases, maximizing the performance of operations in these databases with an efficiency that no other approach can match.

They represent blocks of precompiled SQL code that are stored in the database, allowing repetitive or complex tasks to be executed much faster than if they were executed by any external tool, such as software developed in another language and environment. Essentially, a stored procedure is like a function that groups a series of SQL statements and can be called with different parameters to perform standardized operations, such as inserts, updates, and queries, as well as other activities, such as calculations or data validation.

Since it is executed within the server, the data traffic over the network is drastically reduced because the only things that will be passed over the network are the input parameter values and the name of the procedure, optimizing execution time, decreasing CPU usage, and reducing memory requirements. Moreover, this approach creates security mechanisms between the manipulation of the database data.

T-SQL: the programming language for stored procedures by Microsoft

Transact-SQL (T-SQL) is the programming language initially developed for Microsoft SQL Server and is now also used in Azure SQL Database. It is an extension of SQL (Structured Query Language) that, in addition to allowing basic data query and manipulation operations, also incorporates advanced functionalities that are fundamental for creating and executing stored procedures. These stored routines, written in T-SQL, allow for encapsulating complex logic directly in the database, providing greater efficiency and performance in operations.

T-SQL also offers programming constructs such as variables, loops, and conditional statements, making it a powerful tool for database management and programming. Additionally, it includes a variety of built-in functions that facilitate tasks like string manipulation, mathematical operations, and date and time calculations, further enriching the ability to process data directly on the server.

Another significant advantage of using T-SQL is the seamless integration with other Microsoft technologies, such as C#, Visual Studio, and SQL Server. Since all these tools are developed and maintained by Microsoft, the compatibility between them is optimized, resulting in a more cohesive and efficient development environment. This allows, for example, developers to utilize advanced debugging resources in Visual Studio when working with T-SQL, in addition to facilitating the implementation of solutions that leverage specific SQL Server features, such as real-time data access and efficient manipulation of large volumes of information.

Executing stored procedures in C#

Now, let's see how we can call a stored procedure in C#. For this, we will use as an example a stored procedure that we will call RunningProcedure, which is designed to simulate a process that would take about 5 seconds. This intentionally added time has an important function in this learning experience that will be revealed at the end of the article.

The stored procedure in question:

CREATE OR ALTER PROCEDURE RunningProcedure
AS
BEGIN
    -- Send partial message (progress)
    RAISERROR('Process beginning...', 0, 1) WITH NOWAIT;

    -- Simulate a long-running operation
    WAITFOR DELAY '00:00:01';

    -- Send another partial message
    RAISERROR('25%% of process...', 0, 1) WITH NOWAIT;

    -- Simulate another long-running operation
    WAITFOR DELAY '00:00:01';

    -- Send another partial message
    RAISERROR('50%% of process...', 0, 1) WITH NOWAIT;

    -- Simulate another long-running operation
    WAITFOR DELAY '00:00:01';

    -- Send another partial message
    RAISERROR('75%%... of process', 0, 1) WITH NOWAIT;

    -- Simulate another long-running operation
    WAITFOR DELAY '00:00:01';

    -- Send final message
    RAISERROR('Process finished!', 0, 1) WITH NOWAIT;
END;        

Execute the commands above in Microsoft SQL Server to create the stored procedure. To execute it, simply enter the following command in a database terminal:

EXEC RunningProcedure        

If all goes well, the routine will execute in 5 seconds. If you are observing the log generated during execution, you will follow the process through informative messages about the progress, as shown below:

Now, let’s see the magic happen by generating a small program in C# that executes this procedure. Let's start with a console application, which you can create in Visual Studio or Visual Studio Code. Note the importance of including the System.Data and System.Data.SqlClient libraries to use the necessary objects for our task.

using System;
using System.Data; // Necessary to use the Data class
using System.Data.SqlClient; // Necessary to use SqlConnection and SqlCommand

class Program
{
    static void Main()
    {

    }
}        

Next, we need to establish the connection inside the Main() method. Let's create a variable connectionString that should be filled with the connection string to your database.

We can also already store the name of our stored procedure in a variable.

// Define the connection string with the SQL Server database
string connectionString = "Put your Connection String here";

// Name of the stored procedure
string storedProcedureName = "RunningProcedure";        

Next, we use the SqlConnection class to establish the connection. The Open() method is called to open the connection. Let’s place our operations within a try..catch block to ensure error handling.

// Create a connection to the database
using (SqlConnection connection = new SqlConnection(connectionString))
{
    try
    {
        // Open the connection
        connection.Open();

    }
    catch (Exception ex)
    {
        // Handle the exception
        Console.WriteLine("Error: " + ex.Message);
    }
}        

Now, the most important part begins: inside the using block, after the connection, we create a SqlCommand object associated with the stored procedure whose name we already stored in a variable.

using (SqlCommand command = new SqlCommand(storedProcedureName, connection))
{

}        

Next, we need to set the CommandType property to CommandType.StoredProcedure to indicate that we are calling a stored procedure. Naturally, this is essential and cannot be forgotten.

We could also optionally pass some parameters if the stored procedure depended on them, but that’s not the case here. That said, here’s how that would look:

command.CommandType = CommandType.StoredProcedure;

// Add parameters if necessary
// command.Parameters.AddWithValue("@ParamName", paramValue);        

And now, finally, we execute the stored procedure using the ExecuteNonQuery() function. Since this is a console application, it’s also interesting to display a message before and after execution for monitoring purposes.

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

// Execute the stored procedure
command.ExecuteNonQuery();

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

Done! Our code is ready and, complete, it should look like this:

using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        // Define the connection string with the SQL Server database
        string connectionString = "Put your Connection String here";

        // Name of the stored procedure
        string storedProcedureName = "RunningProcedure";

        // Create a connection to the database
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                // Open the connection
                connection.Open();

                // Create a command to execute the stored procedure
                using (SqlCommand command = new SqlCommand(storedProcedureName, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;

                    // Add parameters if necessary
                    // command.Parameters.AddWithValue("@ParamName", paramValue);

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

                    // Execute the stored procedure
                    command.ExecuteNonQuery();

                    Console.WriteLine("Stored procedure finished.");
                }
            }
            catch (Exception ex)
            {
                // Handle the exception
                Console.WriteLine("Error: " + ex.Message);
            }
        }
    }
}        

If we execute it now, we should see the following result:

There we go! We managed to execute the stored procedure from C#! ??

Note: This program can be accessed on my GitHub through this link here.

But what about the messages generated by the stored procedure?

Eventually, you might need to capture the messages executed by the stored procedure during its execution. Our routine, RunningProcedure, does indeed generate these messages. So how should we do this?

Here, we encounter a problem. The execution as implemented is very straightforward, but it runs synchronously. This means that while the stored procedure is being executed, the application is blocked and cannot be operated or display any messages.

If the issue of synchrony doesn't matter and it is only necessary to display all messages at the end, it is easy to solve. Just add the following command before executing the procedure:

// Register the event to capture messages
connection.InfoMessage += new SqlInfoMessageEventHandler(OnInfoMessage);        

This command will register an event handler to capture all informational messages and errors generated by SQL Server during the execution of commands. In other words, each time a message occurs, the OnInfoMessage function will be called. We will need to implement this event after the Main function.

// Event handler to capture messages sent with RAISERROR or PRINT
static void OnInfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    // Display the progress messages from the stored procedure
    Console.WriteLine("Message from SQL Server: " + e.Message);
}        

Now, if we run our program again, the following result will be displayed:

Eureka! We did it! ??

Note: This altered program can be accessed on my GitHub through this link here.

But wait! The messages sent by SQL Server only appeared after the routine finished running, but during the execution, the program was stuck and displayed nothing. Why?

The Problem with Synchronous Execution

Although we succeeded in running our stored procedure, we faced a significant limitation: synchronous execution. While the stored procedure is executed, the application is blocked, which can cause delays, especially if the operation is time-consuming, as in our example that simulates a 5-second execution process. And what if it were 20 seconds? A minute? Wouldn't it be interesting to show that the program is not frozen and that the routine is running in the background?

To do this, we will need to use another approach: asynchronous execution. But that will be for my next article, which will explain how to avoid this problem and how to use this strategy to allow the system to continue responding (and displaying messages) while the database operation is processed.

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

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