What Is Dapper ORM?

SELECT id, first_name, last_name, email, country, city, phone_number FROM customers WHERE id = "115";

customers.GetById(115)

Core features of Dapper ORM

As we said, every ORM tool works differently. Dapper is distinguished by its core features, rooted in simplicity and performance. It may be not as feature-rich as other ORMs, yet it still has a lot to offer.

• Dapper has a simple API and requires minimum configuration to be integrated into your applications. In addition to that, it is lightweight and fast, which makes it a good fit for applications that require low latency and high performance.
• Dapper is simple yet quite powerful. It works with any .NET language, allows executing raw SQL queries, and quickly maps query results from ADO.NET data readers to instances of business objects.
• Dapper supports both asynchronous and synchronous database queries as well as batching of multiple queries into a single call. It also supports parameterized queries to help protect against SQL injection attacks.
• With Dapper, you get support for a variety of database systems, including SQL Server, MySQL, PostgreSQL, SQLite, and Oracle. It's versatile and can be used in different environments with minimum changes to your codebase.
• Finally, Dapper is compatible with any ADO.NET data provider, including those from our dotConnect product line.

Why Dapper is perfect for .NET applications

Well, not in all cases—but performance-sensitive applications might benefit quite a lot. Legacy applications transitioning to modern .NET frameworks are covered just as well.

If we compare Dapper with EF Core, we will see the following advantages of the former.

• It is simpler and more lightweight than EF Core, and delivers better performance.
• It allows working with raw SQL and ADO.NET.
• It gives direct access to existing database schemas.
• It supports multi-mapping (i.e., mapping of query results to multiple objects).
• It offers quite a few handy extension methods—and that's what we're going to discuss next.

Dapper extension methods

Dapper extends ADO.NET's IDbConnection interface with multiple useful methods that enhance CRUD operations.

Execute executes a command and returns the number of affected rows.

• Always use parameters to prevent SQL injection attacks.
• Check the number of affected rows when updating or deleting data to make sure the operation has been successful.
• If you need to perform multiple Execute calls that depend on each other, wrap them in a transaction to ensure atomicity.

using (var connection = new SqlConnection("your_connection_string"))
{
    connection.Execute("UPDATE Users SET Name = @Name WHERE Id = @Id", new { Name = "John", Id = 1 });
}

Query executes a query and maps the result. It is the most commonly used method in Dapper.

• Unlike EF Core, where you need to apply .AsNoTracking() to speed up read-only operations, Dapper inherently avoids change tracking, making it efficient for read-only operations.
• Avoid returning excessive data for better performance. Always try to limit the number of rows or columns you fetch, especially for large datasets.

using (var connection = new SqlConnection("your_connection_string"))
{
    var users = connection.Query<User>("SELECT Id, Name FROM Users").ToList();
}

QueryFirst executes a query and maps the first result, or throws an exception if no result is found.

• QueryFirst is ideal when you are certain there should be exactly one result, such as retrieving a user by a unique ID.
• Since QueryFirst throws an exception if no rows are found, make sure your query is designed to return a row.

using (var connection = new SqlConnection("your_connection_string"))
{
    var user = connection.QueryFirst<User>("SELECT Id, Name FROM Users WHERE Id = @Id", new { Id = 1 });
}

QueryFirstOrDefault executes a query and maps the first result, or returns a default value (e.g., null) if no result is found.

• Use QueryFirstOrDefault when there might not be a matching record, but you want to return null instead of throwing an exception.

using (var connection = new SqlConnection("your_connection_string"))
{
    var user = connection.QueryFirstOrDefault<User>("SELECT Id, Name FROM Users WHERE Id = @Id", new { Id = 99 });
}

QuerySingle executes a query and maps exactly one result, or throws an exception if more than one result is returned.

• Use QuerySingle when you expect exactly one result. If there is a chance of multiple results, you may want to consider QuerySingleOrDefault instead.

using (var connection = new SqlConnection("your_connection_string"))
{
    var user = connection.QuerySingle<User>("SELECT Id, Name FROM Users WHERE Id = @Id", new { Id = 1 });
}

QuerySingleOrDefault executes a query and maps exactly one result, or returns a default value (e.g., null) if no result is found.

• Use QuerySingleOrDefault when you expect one or zero results, such as finding a user by their email address.

using (var connection = new SqlConnection("your_connection_string"))
{
    var user = connection.QuerySingleOrDefault<User>("SELECT Id, Name FROM Users WHERE Id = @Id", new { Id = 99 });
}

QueryMultiple executes multiple queries within the same command and maps the results.

• Use QueryMultiple when you need to execute a query that returns multiple result sets, and you need to read each one sequentially.
• Make sure you correctly map each result set to the appropriate type.
• After calling QueryMultiple, use Read() to retrieve each result set in order.

using (var connection = new SqlConnection("your_connection_string"))
{
    using (var multi = connection.QueryMultiple("SELECT * FROM Users; SELECT * FROM Orders"))
    {
        var users = multi.Read<User>().ToList();
        var orders = multi.Read<Order>().ToList();
    }
}

What data sources does Dapper support?

Dapper can work with any relational database system that supports ADO.NET data providers. These include MySQL, Oracle, PostgreSQL, SQLite, DB2, and Microsoft SQL Server.

By the way, you can use Dapper together with any of our our dotConnect data providers, which you may check in the following table.

Supported data sources

• All sources
• Databases
• Clouds

Adobe Commerce

BigCommerce

DB2

Dynamics CRM

Dynamics 365

FreshBooks

Magento

Mailchimp

MySQL

Oracle

PostgreSQL

QuickBooks

Salesforce

Salesforce MC

SQLite

SQL Server

SugarCRM

Zoho Books

Zoho CRM

Zoho Desk

DB2

MySQL

Oracle

PostgreSQL

SQLite

SQL Server

Adobe Commerce

BigCommerce

Dynamics CRM

Dynamics 365

FreshBooks

Magento

Mailchimp

QuickBooks

Salesforce

Salesforce MC

SugarCRM

Zoho Books

Zoho CRM

Zoho Desk

Examples of Dapper ORM queries

Now let's get down to several practical examples. But first, we need to install Dapper.

Install the Dapper package

There are several ways to install Dapper. The first one is to use NuGet Package Manager in Visual Studio.

• 1. In Visual Studio, right-click your project in the Solution Explorer and select Manage NuGet Packages.
• 2. In the NuGet Package Manager, find Dapper and click Install. The latest version of Dapper will be downloaded and installed into your project.

The second way is to use the Package Manager Console in the same Visual Studio. Go to Tools > NuGet Package Manager > Package Manager Console and run the following command.

Install-Package Dapper

The third way is to install Dapper using the .NET CLI. Run the following command in the root folder of your project.

dotnet add package Dapper

The fourth way is to download Dapper directly from GitHub and manually install it in your project. However, the use of NuGest is the recommended easiest approach.

With the installation out of the way, let's take a look at further examples of Dapper queries for various operations, based on the extension methods we have described previously.

Read

Here's an example of a query that applies the QueryFirstOrDefault method to select the user whose Id equals 1.

var user = connection.QueryFirstOrDefault<User>("SELECT * FROM Users WHERE Id = @Id", new { Id = 1 });

What if you need to fetch multiple records? Well, then simply use a method that allows it, for instance, Query.

var users = connection.Query<User>("SELECT * FROM Users").ToList();

Insert

Here is an example that involves the Execute method to perform an insert operation.

var sql = "INSERT INTO Users (Name, Age) VALUES (@Name, @Age)";
connection.Execute(sql, new { Name = "Alice", Age = 25 });

Here we would like to reiterate that you should use parameterized queries to prevent SQL injection attacks.

Bulk insert

Dapper does not support bulk insert by default. However, you can wrap Execute calls in transactions as follows.

using (var transaction = connection.BeginTransaction())
{
	var sql = "INSERT INTO Users (Name, Age) VALUES (@Name, @Age)";
	connection.Execute(sql, new[]
	{
    	new { Name = "Bob", Age = 30 },
    	new { Name = "Eve", Age = 28 }
	}, transaction);
	transaction.Commit();
}

Update

You can write and run update queries in Dapper ORM using the Execute method. For instance, this query updates the name of the user whose Id equals 1.

var rowsAffected = connection.Execute("UPDATE Users SET Name = @Name WHERE Id = @Id", new { Name = "John Doe", Id = 1 });

Delete

Finally, here's a query that deletes a record with the Id of 1 and returns the number of rows affected by the deletion.

var rowsAffected = connection.Execute("DELETE FROM Users WHERE Id = @Id", new { Id = 1 });

Make sure you include a WHERE clause in your query to avoid unwanted data loss.

EF Core vs Dapper

Dapper is quite often compared to EF Core. Both are well-reputed ORM solutions, both are good for what they are, and both have their peculiarities and use cases that can even be combined in projects. Here's a brief comparison that will help you see the key differences.

Feature	Dapper	EF Core
Type	Micro-ORM	Full-fledged ORM
Performance	Faster	Slightly slower due to features and abstractions
Queries	Raw SQL queries	LINQ-based queries (yet can use raw SQL)
Abstraction level	Low (direct SQL execution)	High (automated change tracking, migrations)
Change tracking	No (manual tracking)	Yes (automated tracking of entities)
Migrations	No	Yes
Relationships	Manual handling	Automatic handling (navigation properties, foreign keys)
Data mapping	Manual mapping between SQL results and objects	Automatic mapping between entities and databases
Learning curve	Low (for simple queries)	Moderate (due to features and conventions)
Ease of use	Requires SQL knowledge	Easier for CRUD operations
Flexibility	Works with any SQL	Works best with EF Core conventions
Use cases	Use for performance-critical queries and full control over SQL	Use to manage complex relationships and transactions

Using Dapper and EF Core together

You can combine Dapper and EF Core in a single project, bringing the strengths of both sides to the table. Dapper is lightweight and focused on raw SQL queries; thus it can be used for performance-critical queries where you need more control over SQL. Meanwhile, EF Core provides higher-level abstraction with LINQ and change tracking for work with entities and databases.

If you use both libraries in the same method or business logic, make sure they are part of the same transaction when necessary (you can use EF Core's IDbContextTransaction and Dapper's Transaction support).

Conclusion

As a lightweight open-source micro-ORM for .NET and .NET Core applications, Dapper fully lives up to its promise, and it one of the most popular options for performance-sensitive applications. You can download and install it at any given moment using one of the ways we have described, and see it in action.

Additionally, we gladly invite you to explore dotConnect data providers, our high-performance ADO.NET connectivity solutions that cover a rich variety of database systems and cloud services. All of them are available for a free 30-day trial, so you will be able to give them a go and see how they perform under a real workload.