Execute Stored Procedures using SqlCommand in ASP.NET Core

In the previous article we developed a Razor Pages app that provides the UI for performing CRUD operations using the CustomerRepository. Currently our CustomerRepository uses plain SQL queries to get the job done. Many times developers prefer to use Stored Procedures to encapsulate some logic. And the stored procedure is called from the repositories or data access classes. It would be worthwhile to know how stored procedures can be called using SqlCommand object. That's what we are going to cover in this article.

First, we will create a set of stored procedures that encapsulate our CRUD operations. And then we will execute those stored procedures from our modified CustomerRepository.

Take a look at the five stored procedures below - Customers_SelectAll, Customers_SelectByID, Customers_Insert, Customers_Update, and Customers_Delete - that are responsible for the CRUD operations.

CREATE PROCEDURE [dbo].[Customers_SelectAll]
AS
    SELECT CustomerID, CompanyName, ContactName, 
    Country FROM CUSTOMERS ORDER BY CustomerID;
RETURN 0

CREATE PROCEDURE [dbo].[Customers_SelectByID]
	@CustomerID NVARCHAR(5)
AS
	SELECT CustomerID, CompanyName, ContactName, 
    Country FROM CUSTOMERS 
    WHERE CustomerID = @CustomerID;
RETURN 0

CREATE PROCEDURE [dbo].[Customers_Insert]
	@CustomerID NVARCHAR(5),
	@CompanyName NVARCHAR(40),
	@ContactName NVARCHAR(30),
	@Country NVARCHAR(15),
	@RecCount INT OUT
AS
	INSERT INTO Customers(CustomerID, CompanyName, 
    ContactName, Country) 
    VALUES(@CustomerID, @CompanyName, 
    @ContactName, @Country);
	SELECT @RecCount = COUNT(*) FROM Customers;
RETURN 0

CREATE PROCEDURE [dbo].[Customers_Update]
	@CustomerID NVARCHAR(5),
	@CompanyName NVARCHAR(40),
	@ContactName NVARCHAR(30),
	@Country NVARCHAR(15)
AS
	UPDATE CUSTOMERS 
    SET CompanyName = @CompanyName, 
    ContactName=@ContactName, 
    Country=@Country 
    WHERE CustomerID=@CustomerID;
RETURN 0

CREATE PROCEDURE [dbo].[Customers_Delete]
	@CustomerID NVARCHAR(5)
AS
	DELETE FROM CUSTOMERS 
    WHERE CustomerID=@CustomerID;
RETURN 0

I am not going to discuss these stored procedures in detail here since they are quite straightforward. Notice that Customers_Insert has one OUT parameter named @RecCount. The @RecCount parameter contains the number of records in the Customers table upon inserting the new customer. We don't need this value in our app or repository as such. But I have added this parameter just to show how output parameters can be used in ADO.NET.

You can create these stored procedures in Northwind database either using SQL Server Management Studio or using Visual Studio Server Explorer (Tools - Connect to Database option). The following figure shows these stored procedures in Visual Studio's Server Explorer.

Now that we have required stored procedures ready, we will modify our customer repository to utilize them. You can either modify the CustomerRepository class itself or create another repository named CustomerStProcRepository. I am going to follow the later approach to preserve my earlier repository code.

The following code shows the SelectAll() method of CustomerStProcRepository that make use of the Customers_SelectAll stored procedure.

public async Task<List<Customer>> SelectAll()
{
    using var connection = new SqlConnection(connectionString);

    var query = "Customers_SelectAll";

    using var command = new SqlCommand(query, connection);
    command.CommandType = CommandType.StoredProcedure;

    try
    {
        await connection.OpenAsync();
        using var reader = await command.ExecuteReaderAsync();
        List<Customer> items = null;
        if (reader.HasRows)
        {
            items = new List<Customer>();
            while (await reader.ReadAsync())
            {
                var item = new Customer(
                    CustomerID: reader.GetString(0),
                    CompanyName: reader.GetString(1),
                    ContactName: reader.GetString(2),
                    Country: reader.GetString(3));
                items.Add(item);
            }
        }
        await reader.CloseAsync();
        await connection.CloseAsync();
        return items;
    }
    catch
    {
        if (connection.State != ConnectionState.Closed)
        {
            await connection.CloseAsync();
        }
        return null;
    }
}

Notice the code shown in bold letters. The query variable is set to the name of the stored procedure to be executed - Customers_SelectAll. Moreover the CommandType property of SqlCommand is set to CommandType.StoredProcedure enumeration value. This way we tell the data provider that the query mentioned in the SqlCommand is not a plain SQL statement but a stored procedure. The other values of CommandType enumeration include Text (Plain SQL statement. This is the default.) and TableDirect (Just the name of the table).

The other code of SelectAll() remains the same as before and is not discussed here again.

The following code shows the SelectByID() method of CustomerStProcRepository that make use of the Customers_SelectByID stored procedure.

public async Task<Customer> SelectByID(string id)
{
    using var connection = new SqlConnection(connectionString);

    var query = "Customers_SelectByID";

    using var command = new SqlCommand(query, connection);
    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.AddWithValue("@CustomerID", id);

    try
    {
        await connection.OpenAsync();
        using var reader = await command.ExecuteReaderAsync();
        Customer item = null;
        if (reader.HasRows)
        {
            while (await reader.ReadAsync())
            {
                item = new Customer(
                    CustomerID: reader.GetString
(reader.GetOrdinal("CustomerID")),
                    CompanyName: reader.GetString
(reader.GetOrdinal("CompanyName")),
                    ContactName: reader.GetString
(reader.GetOrdinal("ContactName")),
                    Country: reader.GetString
(reader.GetOrdinal("Country")));
            }
        }
        await reader.CloseAsync();
        await connection.CloseAsync();
        return item;
    }
    catch
    {
        if (connection.State != ConnectionState.Closed)
        {
            await connection.CloseAsync();
        }
        return null;
    }
}

Changes to SelectByID() are similar to the changes made to SelectAll() and hence are not discussed again. Notice that this time we use Customers_SelectByID stored procedure that takes @CustomerID parameter.

The Insert() method is also similar to SelectAll() and SelectByID() but there are a few more changes. So, let's discuss that here. Have a look at the modified Insert() method below.

public async Task<int> Insert(Customer customer)
{
    using var connection = new SqlConnection(connectionString);

    var query = "Customers_Insert";

    using var command = new SqlCommand(query, connection);
    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.AddWithValue("@CustomerID", 
    customer.CustomerID);
    command.Parameters.AddWithValue("@CompanyName", 
    customer.CompanyName);
    command.Parameters.AddWithValue("@ContactName", 
    customer.ContactName);
    command.Parameters.AddWithValue("@Country", 
    customer.Country);

    
    var recCount = command.Parameters.Add("@RecCount", 
    SqlDbType.Int);
    recCount.Direction = ParameterDirection.Output;
    

    try
    {
        await connection.OpenAsync();
        var rowsAffected = await command.ExecuteNonQueryAsync();
        var count = recCount.Value;
        await connection.CloseAsync();
        return rowsAffected;
    }
    catch
    {
        if (connection.State != ConnectionState.Closed)
        {
            await connection.CloseAsync();
        }
        return -1;
    }
}

Notice the code shown in bold letters. We set query to Customers_Insert and CommandType to StoredProcedure as before. We then add @CustomerID, @CompanyName, @ContactName, and @Country parameters. Finally, we also add the @RecCount output parameter. Since this parameter is an output parameter we would like to check its value after the stored procedure is executed. So, we set the Direction property of SqlParameter object to ParameterDirection.Output. The other values of ParameterDirection enumeration include Input, InputOutput, and ReturnValue.

Recollect that the @RecCount OUT parameter is assigned a value in the Customers_Insert stored procedure. We retrieve the value after the Customers_Insert is executed by checking the Value property of recCount SqlParameter object. Although we aren't using it for any meaningful purpose in our Insert() method, you can use it depending on your application logic and requirement.

The Update() and Delete() methods require similar modification and are quite straightforward. You can read the earlier articles that discusses the CustomerRepository in detail here and here. For the sake of completeness the modified Update() and Delete() methods are given below.

public async Task<int> Update(Customer customer)
{
    using var connection = new SqlConnection(connectionString);

    var query = "Customers_Update";

    using var command = new SqlCommand(query, connection);
    command.CommandType = CommandType.StoredProcedure;

    SqlParameter[] p = new SqlParameter[4];
    p[0] = new SqlParameter("@CustomerID", customer.CustomerID);
    p[1] = new SqlParameter("@CompanyName", customer.CompanyName);
    p[2] = new SqlParameter("@ContactName", customer.ContactName);
    p[3] = new SqlParameter("@Country", customer.Country);
    command.Parameters.AddRange(p);

    try
    {
        await connection.OpenAsync();
        var rowsAffected = await command.ExecuteNonQueryAsync();
        await connection.CloseAsync();
        return rowsAffected;
    }
    catch(SqlException e)
    {
        // do something with e

        if (connection.State != ConnectionState.Closed)
        {
            await connection.CloseAsync();
        }
        return -1;
    }
}

Here is the code for Delete() method :

public async Task<int> Delete(int id)
{
    using var connection = new SqlConnection(connectionString);

    var query = "Customers_Delete";

    using var command = new SqlCommand(query, connection);
    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.AddWithValue("@CustomerID", id);

    try
    {
        await connection.OpenAsync();
        var rowsAffected = await command.ExecuteNonQueryAsync();
        await connection.CloseAsync();
        return rowsAffected;
    }
    catch
    {
        if (connection.State != ConnectionState.Closed)
        {
            await connection.CloseAsync();
        }
        return -1;
    }
}

To use the modified customer repository register it in Program.cs as follows:

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddRazorPages();
builder.Services.AddScoped
<ICustomerRepository, CustomerStProcRepository>();
var app = builder.Build();
app.MapRazorPages();
app.Run();    

You can now run the application and test the CRUD operations.

In the next article we will discuss some cases where ADO.NET objects and EF Core code can be used hand in hand.

That's it for now! Keep coding!!