Execute action queries using SqlCommand in ASP.NET Core

In the previous article we used SqlDataReader to execute SELECT queries and to read data from the database. You often need to execute action queries - INSERT, UPDATE, and DELETE - on the database to perform the respective operations. As far as as SQL Server data provider is concerned you can do that with the help of SqlCommand object. That's what we are going to discuss in this article.

In the previous article we have started building a simple repository - CustomerRepository - that performs the CRUD operations on the Customers table of Northwind database. We have added SelectAll() and SelectByID() methods to the repository. In this article we will add the remaining three methods namely Insert(), Update, and Delete() to the CustomerRepository.

So, let's get going.

Load the same project in Visual Studio and open CustomerRepository class in the Visual Studio IDE.

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

    var query = "Insert Into Customers(CustomerID, CompanyName, 
ContactName, Country) Values(@CustomerID, 
@CompanyName, @ContactName, @Country)";

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

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

    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;
    }
}

The Insert() method accepts a new Customer to be added to the database and returns an integer back to the caller. Inside, we create a SqlConnection and SqlCommand objects as before. This time our SQL query is an INSERT statement that inserts a new row in the Customers table. The INSERT statement has four parameters namely @CustomerID, @Companyname, @ContacName, @Country. These parameters are added to the Parameters collection using AddWithValue() method.

The INSERT action query is executed using the ExecuteNonQueryAsync() method of the SqlCommand object. The ExecuteNonQueryAsync() method executes the action queries and returns an integer indicating the number of rows affected by that query. In this case we are inserting one record. So, it will return 1. In case of any error we return -1 to the caller (ExecuteNonQueryAsync() also returns -1 for queries other than INSERT, UPDATE, and DELETE. So, you may return some other number to flag an error but in our example -1 works for us).

Next, add Update() method to the CustomerRepository as shown below

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

    var query = "Update Customers Set CompanyName = @CompanyName, 
ContactName = @ContactName, Country = @Country 
Where CustomerID = @CustomerID";

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

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

    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;
    }

}

This code is similar to the Insert() method but with a few changes. Firstly, we use UPDATE query that updates a particular CustomerID. Secondly, we create an array of SqlParameter objects and then add that array to the Parameters collection using AddRange() method. I have done that just to illustrate a variation of adding command parameters. You can stick with whatever way you find easy (AddWithValue() or what is shown above).

The UPDATE statement is executed using ExecuteNonQueryAsync() method.

To trap the error, this time we use SqlException. Although we don't do anything meaningful with SqlException inside the catch block, you can use it for error logging or similar purposes. You can also add multiple catch blocks to trap additional exceptions such as InvalidCastException and InvalidOperationException. See a list of possible exceptions ExecuteNonQuery() can throw here.

Finally, add the Delete() method to CustomerRepository as shown below:

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

    var query = "Delete From Customers Where 
CustomerID = @CustomerID";

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

    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;
    }
}

The Delete() method is quite similar to the earlier two method we wrote in that it uses ExecuteNonQueryAsync() method to execute the query. This time we have DELETE statement that deletes a particular CustomerID from the database.

This completes our CustomerRepository with five methods - SelectAll(), SelectByID(), Insert(), Update(), and Delete(). In the next articke we will use these methods to perform the CRUD operations.

That's it for now! Keep coding!!