Managing data with Dapper

Dapper uses ADO.NET underneath when working with SQL Server. Because it is a higher-level technology, it is not as efficient as using ADO.NET directly, but it can be easier. Dapper is an alternative ORM to EF Core. It is more efficient because it extends the low-level ADO.NET IDbConnection interface with very basic functionality without trying to be all things to all people.

Dapper connection extension methods

Dapper adds three extension methods to any class that implements IDbConnection (like SqlConnection). They are Query<T>, Query, and Execute. Dapper will automatically open and close the associated connection as needed.

The Query<T> extension method is the most used because it runs any specified SQL command and then returns the results as an IEnumerable<T> (a sequence of objects). It is designed to run commands that retrieve data like SELECT. It has several parameters, as shown in Table 2.8:

Parameter	Description
string sql	This is the only mandatory parameter. It is either the text of a SQL command or the name of a stored procedure.
object param = null	A complex object for passing parameters used in the query. This can be an anonymous type.
IDbTransaction transaction = null	To manage distributed transactions.
bool buffered = true	By default, it will buffer the entire reader on return. With large datasets, you can minimize memory and only load objects as needed by setting buffered to false.
int? commandTimeout = null	To change the default command timeout.
CommandType? commandType = null)	To switch to a stored procedure instead of the default of text.

Table 2.8: Dapper’s Query<T> extension method parameters

The Query extension method is a loosely-typed equivalent so it is less frequently used.

The Execute extension method runs any specified SQL command and then returns the number of rows affected as an int. It is designed to run commands like INSERT, UPDATE, and DELETE. It has the same parameters as the Query<T> extension method.

Querying using Dapper

Let’s see a simple example that queries the Suppliers table instead of the Products table:

1. In the Northwind.Console.SqlClient project, add a package reference for Dapper, as shown highlighted in the following markup:

   <ItemGroup>
     <PackageReference Include="Microsoft.Data.SqlClient" Version="5.1.2" />
     <PackageReference Include="Dapper" Version="2.1.21" />
   </ItemGroup>
   

   At the time of writing, the latest version of Dapper is 2.1.21, released on November 11, 2023. You can check if it has been updated since then at the following link: https://www.nuget.org/packages/Dapper.

2. Build the project to restore packages.
3. Add a new class file named Supplier.cs, and modify its contents to define a class to represent four columns from each row in the Suppliers table, as shown in the following code:

   namespace Northwind.Models;
   public class Supplier
   {
     public int SupplierId { get; set; }
     public string? CompanyName { get; set; }
     public string? City { get; set; }
     public string? Country { get; set; }
   }
   

4. At the bottom of Program.cs, add statements to retrieve Supplier entities in Germany, enumerate the collection outputting basic information about each one, and then serialize the collection as JSON to the console, as shown in the following code:

   WriteLineInColor("Using Dapper", ConsoleColor.DarkGreen);
   connection.ResetStatistics(); // So we can compare using Dapper.
   IEnumerable<Supplier> suppliers = connection.Query<Supplier>(
     sql: "SELECT * FROM Suppliers WHERE Country=@Country",
     param: new { Country = "Germany" });
   foreach (Supplier s in suppliers)
   {
     WriteLine("{0}: {1}, {2}, {3}",
       s.SupplierId, s.CompanyName, s.City, s.Country);
   }
   WriteLineInColor(JsonSerializer.Serialize(suppliers),
     ConsoleColor.Green);
   OutputStatistics(connection);
   

5. Run the console app, and in the section where we used Dapper, note the same connection was used, so its events were raised while the Dapper query was executed, the enumerated collection output, and then JSON generated from the list of suppliers, as shown in the following output:

   Using Dapper
   11: Heli Süßwaren GmbH & Co. KG, Berlin, Germany
   12: Plutzer Lebensmittelgroßmärkte AG, Frankfurt, Germany
   13: Nord-Ost-Fisch Handelsgesellschaft mbH, Cuxhaven, Germany
   [{"SupplierId":11,  "CompanyName":"Heli S\u00FC\u00DFwaren GmbH \u0026 Co. KG",
     "City":"Berlin","Country":"Germany"},
    {"SupplierId":12,
     "CompanyName":"Plutzer Lebensmittelgro\u00DFm\u00E4rkte AG",
     "City":"Frankfurt","Country":"Germany"},
    {"SupplierId":13,
     "CompanyName":"Nord-Ost-Fisch Handelsgesellschaft mbH",
     "City":"Cuxhaven","Country":"Germany"}]
   BytesReceived: 1,430
   BytesSent: 240
   SelectRows: 3
   ExecutionTime: 5
   

6. At the bottom of Program.cs, add statements to run the GetExpensiveProducts stored procedure, passing a price parameter value of 100, enumerate the collection outputting basic information about each one, and then serialize the collection as JSON to the console, as shown in the following code:

   IEnumerable<Product> productsFromDapper = 
     connection.Query<Product>(sql: "GetExpensiveProducts",
     param: new { price = 100M, count = 0 }, 
     commandType: CommandType.StoredProcedure);
   foreach (Product p in productsFromDapper)
   {
     WriteLine("{0}: {1}, {2}",
       p.ProductId, p.ProductName, p.UnitPrice);
   }
   WriteLineInColor(JsonSerializer.Serialize(productsFromDapper),
     ConsoleColor.Green);
   

Warning! With Dapper, you must pass a param object with all parameters, even if they are only used as output parameters. For example, we must define count, or an exception will be thrown. You must also remember to explicitly set the command type to stored procedure!

Run the console app, and in the section where we used Dapper to run the stored procedure to get the products that cost more than 100, note the same connection was used so its events were raised while the Dapper query was executed, the enumerated collection output, and then JSON generated from the list of products, as shown in the following output:

Info: Getting expensive products: 100.00.
29: Thüringer Rostbratwurst, 123.7900
38: Côte de Blaye, 263.5000
[{"ProductId":29,"ProductName":"Th\u00FCringer Rostbratwurst","UnitPrice":123.7900},{"ProductId":38,"ProductName":"C\u00F4te de Blaye","UnitPrice":263.5000}]

More Information: You can learn more about Dapper at the following link: https://github.com/DapperLib/Dapper/blob/main/Readme.md.