Dapper

Welcome To Learn Dapper ORM - A Dapper Tutorial for C# and .NET Core

Dapper is a simple and efficient .NET library for data access and object-relational mapping (ORM) that supports .NET Core and C# language.

https://www.learndapper.com/

Allows you to execute SQL queries and map the results to objects.

It exists purely to be faster and a less painful version of EF.

It is best at doing many many reads.

Install

dotnet add package Dapper

ALL QUERYS MUST GO INTO an SQL Connection object

SqlConnection objects do NOT get disposed when they exist scope, so be sure to use using

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    // Do work here; connection closed on following line.
}

Command Examples

Note: that all come with a Async counter part

Query Single

Returns a single row. WILL THROW IF MORE THAN ONE ROW IS RETURNED

var sql = "SELECT * FROM Product WHERE ProductID = @productID";
var product = connection.QuerySingle<Product>(sql, new { productID = 1 });

Query First

Gets the first row from a query

var sql = "SELECT * FROM Product WHERE ProductID = @productID";
var product = connection.QueryFirst(sql, new { productID = 1 });

Query

Gets a list of rows

var sql = "SELECT * FROM Product WHERE CategoryID = @categoryID";
var products = connection.Query<Product>(sql, new { categoryID = 1 }).ToList();
foreach(var product in products)
{
	Console.WriteLine($"ProductID: {product.ProductID}; Name: {product.Name}");
}

Non-Query

Execute delete or update operations

var sql1 = "UPDATE Product SET Name = Name + @suffix WHERE CategoryID = @categoryID";		
connection.Execute(sql1, new { categoryID = 1, suffix = " (Updated)" })

Insert

// 1. We will create a connection
using (var connection = new SqlConnection(connectionString))
{
    // 2. We will create an `INSERT` SQL statement
	var sql = "INSERT INTO Customers (Name, Email) VALUES (@Name, @Email)";
	// 3. Call the `Execute` method
	{
		// 3a. The first time, we will pass parameter values with an anonymous type
		var anonymousCustomer = new { Name = "ZZZ Projects", Email = "zzzprojects@example.com" };
		var rowsAffected = connection.Execute(sql, anonymousCustomer);
		Console.WriteLine($"{rowsAffected} row(s) inserted.");
	}
	{
		// 3b. The second time, we will pass parameter values by providing the customer entity
		var customer = new Customer() { Name = "Learn Dapper", Email = "learndapper@example.com" };
		var rowsAffected = connection.Execute(sql, customer);
		Console.WriteLine($"{rowsAffected} row(s) inserted.");
	}
}