Dapper - The Pocket Rocket Mapper

• Download source code - 71.4 KB

Introduction

Dapper is a popular SQL database mapper. Typically, it maps the column fields in a table row to and from properties inside a C# class instance. In the three tier model of data management, Dapper sits in the middle tier, it takes data from the upper view or presentational tier and presents it to the lower data tier in a form that can be acted on by the database. In a similar way, it takes data from the database and presents it to the upper tier in a form that can be consumed by the view.

Why Use Dapper?

It’s easy to use, lightweight (47K) and fast - about twice the speed of Entity Framework. The authors are a team at StackOverflow, they maintain it and use it to access their huge database. It’s not intended to be a replacement for Entity Framework, that framework is a superb object–relational mapper (ORM) with every facility that you could possibly wish for, but many users have difficulty in controlling it, with the result that routes are taken that don’t need to be travelled and luggage is carried that will never be unpacked. With Dapper, you are in the driving seat. You steer where it goes and determine what it does when it gets there.

Implementation

Dapper is available as open source on GitHub and as a Nuget Package. It doesn’t exist as a distinct entity, it is implemented as a collection of extension methods that, in effect, extend the IDbConnection interface. This is similar to the way system.Linq extends IEnumerable<T>. The usual format for a Dapper query is to pass in a SQL statement as a string coupled with any parameters that the statement requires expressed as members of an anonymous type. Something along these lines.

int id = 7;
string sql = @"Select *from dbo.Employees where EmployeeID=@Id;";
using IDbConnection connection = _connectionCreator.CreateConnection();
var employee = await connection.QueryFirstOrDefaultAsync<Employee>(sql,new{Id=id});

Here, the SQL statement is selecting all column fields from rows in the Employees table where the EmployeeID column is equal to the input parameter Id. The ‘@’ symbol identifies the input parameter that the statement requires. The QueryFirstOrDefaultAsync<T> generic method is used because the EmployeeID is a primary key so there is only one record to search for. The anonymous type has a member name that matches the name of the input parameter that the SQL statement references and it’s set to the value of the local variable id. Dapper maps the output from the query to an instance of the type T. In this example, type T this is defined as the Employee class. That’s the basic set up, there are other Query methods that request data from the database and Execute methods that pass commands to the database such as Insert, Delete and Update. There are usually asynchronous and synchronous versions of the same method. The Learn Dapper website and the Dapper Readme.md file on GitHub have many excellent examples of Dapper in action.

Configuring Dapper for Use in ASP.NET Applications

Managing Database Connections

Database connections need to be opened before they can be used then closed and disposed of as soon as the query completes. This means that there is a certain amount of connection management work associated with every Dapper extension method. There also needs to be a reference to a, Database-Server specific, instance of IDbConnection. So there is the potential here for a lot of code duplication and a dependency upon the chosen IDbConnection type for every method that makes a database call. A solution to this problem is to encapsulate Dapper methods within a IDatabaseContext class so that anything using this class needs to know nothing about Dapper or the database connection. Here’s an example of an IDatabaseContext method that calls Dapper’s ExecuteAsync method.

public async Task<T> QueryFirstOrDefaultAsync<T>(
    string sql,
    object? parameters = null,
    CommandType? commandType = null)
 {
     using IDbConnection connection = _connectionCreator.CreateConnection();
     var result = await connection.QueryFirstOrDefaultAsync<T>(sql, parameters,
      commandType: commandType);
     return result;
 }

The ConnectionCreator.CreateConnection method is a factory method that returns a new instance of the Connection.

 public class MsSqlConnectionCreator : IConnectionCreator
{
    protected ServerOptions _serverOptions;
    public MsSqlConnectionCreator(IOptions<ServerOptions> serverOptionsSnapshot)
    {
        _serverOptions = serverOptionsSnapshot.Value;
    }

    public  IDbConnection CreateConnection()
    {
        var connectionString = _serverOptions.MsSql;
        return new SqlConnection(connectionString);
    }
}

The DatabaseContext gets the database connection string from an instance of the IOptions<T> generic type that’s injected into its constructor.

public SqlServerContext(IOptions<ServerOptions> serverOptionsSnapshot)
    {
        _serverOptions = serverOptionsSnapshot.Value;
        _connectionString = _serverOptions.MsSql;
    }    

This technique is considered preferable to the alternative choice of passing in the ConfigurationManager and using the manager to read the connections. It has the advantage of limiting the scope of the ConfigurationManager to that of other application builders within Program.cs and Startup.cs. The member values of the ConnectionStrings section in appsettings.json are bound to the ServerOptions singleton class at run time.

"ConnectionStrings": {
  "MsSql": "Data Source=(localdb)\\ProjectModels;
   Initial Catalog=Northwind;Integrated Security=True",
  "MySql": "Server=127.0.0.1;user ID=root; Database=northwind; Password=Pa$$w0rd"
    
  },
....

The ServerOptions class has a couple of properties that match the names in the ConnnectionStrings section.

public class ServerOptions
{
    //ConnectionStrings is not mapped, 
    //it's used to avoid a magic string when referencing
    // the appsettings "ConnectionStrings" section
    public const string ConnectionStrings = "ConnectionStrings";

    public string MsSql { get; set; } = string.Empty;
    public string MySql { get; set; } = string.Empty;
}

The bindings are set in Program.cs.

var section = builder.Configuration.GetSection("ConnectionStrings");
builder.Services.Configure<ServerOptions>(section);

Managing SQL Statements

Having a class that implements an IDataAccess interface solves a lot of issues but there is still the need for SQL statements to be issued by services running in the presentation layer. SQL statements are basically data management instructions for the database server and should, ideally, be situated inside the database. The way to achieve this is to use stored procedures. Stored procedures are precompiled SQL statements that reside in the database. In SQL Server Object Explorer, they are in the database’s Programmability folder. The following shows how to call the CustOrderHist stored procedure from the Northwind example database.

string customerID = "ANTON";//input param
var results = await _databaseContext.QueryAsync<ProductSummary>
              (_storedProcedureId.CustomerOrderHistory,
               new { CustomerID = customerID }, 
               commandType: CommandType.StoredProcedure);

The commandType parameter is initialized by the use of a named parameter. A named parameter avoids having to enter a lot of null values for the parameters that are sequenced before the one that's required. The procedure name is passed in as a property of a custom StoredProcedureId class.

public  string CustomerOrderHistory { get; } = "dbo.CustOrderHist";

There are over 800 records in Northwind’s Orders table yet Dapper can complete this procedure in the blink of an eye.

The Example Application

The example application is an ASP.NET 7 console app that uses the Northwind database. It needs to have the connection string in the appsettings.json file updated to point to an instance of the database server. The examples will run with TSql or MySql statements depending on the SQL Server selected. They are not meant to be definitive, most are self explanatory, but there are a couple that may need some clarification.

Example 1

Two classes are involved in this query.The Order class models the Orders table and the Employee class models the Employees table. The Order class also has a foreign key that references the Employees' table primary key and, in addition, it has a property that references an instance of the Employee class.

public class Order
{
    public int OrderID { get; set; }
    public string? CustomerID { get; set; }
    public int EmployeeID { get; set; }
    public DateTime OrderDate { get; set; }
    public int ShipperID { get; set; }
    public Employee? Employee { get; set; }
}

The SQL statement joins the two tables on matching EmployeeIDs and orders the query by the Employee's LastName and then by the FirstName.

string sql = @"select o.EmployeeID,o.OrderId,e.EmployeeID,e.FirstName,e.LastName
              from dbo.Orders o
              inner join dbo.Employees e
              on o.EmployeeID  = e.EmployeeID
              order by e.LastName, e.FirstName";

Dapper takes the result of this query and maps it to a collection of Order items.

var employeeOrders= await _databaseContext.QueryAsync<Order, Employee>(sql,
     (order, employee) =>
     {
         order.Employee = employee;
         return order;
     }, splitOn: "EmployeeID");

In order for Dapper to map efficiently, it needs to be provided with a function that sets the Order class Employee property to the Employee instance and returns the Order class instance. It’s also necessary to tell Dapper where, in all the columns that the query returns, do the columns relating to the Orders table end and the ones in the Employees table begin. That is assumed to be when a column name is encountered with a ToUpper() value of ”ID”. But, in this case, the relevant identity column’s name is EmployeeID so the splitOn parameter needs to be set to that name.

Example 2

This example contains a SQL statement that would normally cause an exception as it has a collection as an input parameter and collections of parameters are not normally allowed. Dapper circumvents that restriction by deconstructing the collection into a series of single parameters in a format that the SQL parser will accept. The statement references the collection as @countries. The local variable countryArray is passed into the QueryAsync method using an anonymous type with a member that must be named the same as the referenced parameter and be set to the local array instance. The query gets the total number of products supplied from each of the four countries named in the array.

string[] countryArray = new[] { "France", "Germany", "UK", "USA" };
string sql = @"Select Suppliers.Country As SuppliersCountry,COUNT(*) as ProductCount
             From Suppliers join Products on Suppliers.SupplierID=Products.SupplierID
             where Suppliers.Country in @countries
             Group by Suppliers.Country
             Order by ProductCount Desc;";
var results = await _dba.QueryAsync<(string SuppliersCountry, int ProductCount)>
              (sql, new { countries = countryArray });

If you are new to SQL, the statement above may look a bit intimidating. A useful tip is to try to read it from back to front. So, starting from the last clause, the output is ordered by ProductCount in descending order. It is grouped by Suppliers.Country only where the Suppliers.Country exists in the input parameter collection. The data is obtained from joining each row in the Suppliers table to a matching row in the Products table on Suppliers.SupplierID being equal to Products.SupplierID. The selected output of rows and columns has Suppliers.Country as a member of the SuppliersCountry column and the count of all the items in the group as a member of the ProductCount column. In SQL, groupings are expressed as a key /value pairs where the value is the result from an aggregation function that uses the group’s collection of member items as an input parameter. That's a lot of functionality compressed into a few lines of code. SQL is the Columbo of programming languages; it’s smarter than it looks.

Conclusion

Using Dapper extension methods as database mappers is a useful and efficient alternative to employing the types provided by Entity Framework. This is especially true in situations where the advanced features of Entity Framework are not required.

Lessons Learned

It’s important that all of Dapper’s async methods are awaited. It is not a good idea to simply call the method and return the Task so that another method can await it. Although that technique avoids the overhead associated with the use of the async keyword, it also results in a null reference exception. The methods are called from within a using statement and, as soon as the uncompleted Task is returned, the using statement ensures that the connection is closed with the result that Dapper is left high and dry. I found that out the hard way.

History

• 3^rd January, 2023: Initial version