Execute queries and stored procedures using Dapper in ASP.NET Core

In the last few articles we have discussed how ADO.NET data provider classes such as connection, command, and data reader can be used to execute queries. This approach gives total control over the queries we execute and it's also good in terms of performance. However, it requires you to write good amount of code. Won't it be nice if there some way to reduce this mapping code? That' where Dapper can come handy. In this article we will learn to use Dapper in ASP.NET Core apps.

Dapper is a Micro ORM. Entity Framework Core is a full-fledged ORM and provides several features such as change tracking and migrations. Dapper is a lightweight ORM that wraps ADO.NET code and allows you to quickly execute queries and stored procedures without compromising the control and performance. Dapper achieves this by adding a set of extension methods to the ADO.NET connection object. These extension methods can be used to execute queries and stored procedures. So, most of the times you just create a connection and then call one of the extension methods to get your job done. You can skip writing the command, data reader, and mapping related code. Dapper does all that for you.

In order to use Dapper in your ASP.NET Core application you need to add it's NuGet package. Firstly open the same project that we have been working with during our last article.

Then open the NuGet package manager dialog and search for Dapper. Install the latest stable version in your project.

Note that you also need Microsoft.Data.SqlClient package because Dapper simply adds extension methods to the connection object. We have already added SQL Server data provider while working with the previous articles of this series. So, you should be already having Microsoft.Data.SqlClient installed in your project.

So far we have created three repositories based ICustomerRepository interface - CustomerRepository, CustomerStProcRepository, and CustomerEFRepository. Now we will create the fourth repository named CustomerDapperRepository. So, create a copy of CustomerRepository and rename it to CustomerDapperRepository.

Before we write any code in the CustomerDapperRepository class open GlobalUsings.cs and add a global using statement for Dapper namespace.

global using System.Data;
global using Microsoft.Data.SqlClient;
global using AdoNetIDemo.DataAccess;
global using System.ComponentModel.DataAnnotations;
global using Dapper;

Then open CustomerDapperRepository class and modify the SelectAll() method as shown below:

public async Task<List<Customer>> SelectAll()
{
    using var connection = new SqlConnection(connectionString);
    var query = "Select CustomerID, CompanyName, 
    ContactName, Country From Customers 
    Order By CustomerID";
    
    var data = await connection.QueryAsync<Customer>(query);
        
    return data.ToList();
}    

Notice the code shown in bold letters. After creating an SqlConnection we use QueryAsync() extension method added by Dapper. The QueryAsync() method accepts a SELECT query and returns IEnumerable<Customer> to the calling code. We also specify the type for mapping the query results (Customer in this case). Since SelectAll() is returning a List, we call ToList() and return the customer data to from the repository.

As you can see in this code, we haven't created any SqlCommand and SqlDataReader. Even the mapping code is not needed. Dapper takes care of that in the QueryAsync() extension method.

Now let's modify the SelectByID() method to use another Dapper extension method.

public async Task<Customer> SelectByID(string id)
{
    using var connection = new SqlConnection(connectionString);
    var query = "Select CustomerID, CompanyName, 
    ContactName, Country From Customers 
    Where CustomerID = @CustomerID";
    
    var data = await connection.QuerySingleAsync<Customer>
    (query, new { CustomerID = id});
    
    return data;
}    

This time we use QuerySingltAsync() extension method provided by Dapper. This method accepts a query and query parameters. In the above code there is just one parameter (@CustomerID) and hence we pass it by wrapping it in an anonymous object.

The QuerySingleAsync() method returns a single item to the caller. If there are more than one items, it throws an exception. In this case CustomerID is a primary key and hence it is going to return just one matching item.

Now let's modify the Insert() method to use a suitable Dapper extension method.

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)";

    
    var rowsAffected = await 
    connection.ExecuteAsync(query, new
    {
        CustomerID = customer.CustomerID,
        CompanyName = customer.CompanyName,
        ContactName = customer.ContactName,
        Country = customer.Country
    });
    

    return rowsAffected;
}    

To execute the INSERT statement we use ExecuteAsync() extension method provided by Dapper. As before, this method accepts a query and query parameters. And it returns the number of rows affected by the query.

Next, we will modify Update() method. Take a look at the following code.

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

    
    var p = new DynamicParameters();
    p.Add("CustomerID", customer.CustomerID);
    p.Add("CompanyName", customer.CompanyName);
    p.Add("ContactName", customer.ContactName);
    p.Add("Country", customer.Country);

    var rowsAffected = await connection.ExecuteAsync(query, p);
    
    return rowsAffected;

}    

Here also we use ExecuteAsync() method but we use a different way of passing parameters. We create a DynamicParameters bag and add parameters to it. The Add() method takes parameter name and its value. The DynamicParameter is then passed to the second parameter of ExecuteAsync() method.

Finally, the Delete() method takes a similar route as shown below:

public async Task Delete(string id)
{
    using var connection = new SqlConnection(connectionString);
    
    var query = "Customers_Delete";
    
    var p = new DynamicParameters();
    p.Add("CustomerID", id);
    
    var rowsAffected = await connection.ExecuteAsync
    (query, p, commandType: CommandType.StoredProcedure);
    
    return rowsAffected;
}    

Notice that the Delete() method uses Customers_Delete stored procedure instead of DELETE statement. The ExecuteAsync() method now takes three parameters - query, DynamicParameters object, and command type enumeration value.

This completes the CustomerDapperRepository class. Register it in the Program.cs as shown below:

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddRazorPages();
builder.Services.AddDbContext<AppDbContext>
(o=>o.UseSqlServer(builder.Configuration.
GetConnectionString("AppDb")));

builder.Services.AddScoped
<ICustomerRepository, CustomerDapperRepository>();

var app = builder.Build();
app.MapRazorPages();
app.Run();

You can now run the application and check whether the code using Dapper extension methods work as expected.

That's it for now! Keep coding!!