Welcome To Learn Dapper
This site is for developers who want to learn how to use Dapper — The micro ORM created by the people behind Stack Overflow.
What is Dapper?
Dapper is an open-source object-relational mapping (ORM) library for .NET and .NET Core applications. The library allows developers quickly and easily access data from databases without the need to write tedious code. Dapper allows you to execute raw SQL queries, map the results to objects, and execute stored procedures, among other things. It is available as a NuGet package.
- Dapper is lightweight and fast, making it an ideal choice for applications that require low latency and high performance.
- It is a simple yet powerful object mapping tool for any .NET language, such as C#, that enables developers to quickly and easily map query results from ADO.NET data readers to instances of business objects.
- It has excellent support for both asynchronous and synchronous database queries and batching multiple queries together into a single call.
- Additionally, dapper supports parameterized queries to help protect against SQL injection attacks.
Does Dapper support .NET Core?
Yes, Dapper has been supporting .NET Core since the release of version 1.50 in July 2016. Dapper is a cross-platform .NET library, which means that it can be used on any platform that supports .NET, including .NET Core.
Does Dapper support C#?
Yes, you can use Dapper with C# as multiple other languages, such as VB.NET and F#. In fact, Dapper was written in C# and is a popular choice for data access in C# applications because of its simplicity and efficiency.
Is Dapper an ORM?
Dapper falls into a family of tools known as micro-ORMs. These tools perform only a subset of the functionality of full-blown Object Relations Mappers, such as Entity Framework Core, but Dapper is known for its speed and simple implementation compared to others. The following table provides a general idea of the capabilities that you can expect to find in a micro ORM compared to an ORM:
| Micro ORM | ORM |
|---|---|
| Map queries to objects | |
| Caching results | |
| Change tracking | 1 |
| SQL generation | 2 |
| Identity management | |
| Association management | |
| Lazy loading | |
| Unit of work support | |
| Database migrations |
Dapper concentrates its efforts on the O and M of ORM — Object Mapping.
[1] Some extensions have been added to Dapper that provide the minimal change-tracking capability
[2] Dapper does generate SQL but in a limited fashion. Third-party libraries such as Dapper Plus can generate the full SQL for insert, update, and delete statements
When Should You Use Dapper?
When deciding whether to use Dapper or not, one should bear in mind the primary reason for its existence — performance. The original developers of Dapper were using Entity Framework Core’s predecessor — the short-lived Linq to SQL. However, they found that query performance wasn’t good enough for the increasing traffic that the site in question (Stack Overflow) was experiencing, so they wrote their own micro ORM.
Dapper is, therefore, a good choice in scenarios where read-only data changes frequently and is requested often. It is particularly good in stateless scenarios (e.g. the web) where there is no need to persist complex object graphs in memory for any duration.
Dapper does not translate queries written in .NET languages to SQL like a full-blown ORM. So you need to be comfortable writing queries in SQL or have someone write them for you.
Dapper has no real expectations about the schema of your database. It is not reliant on conventions in the same way as Entity Framework Core, so Dapper is also a good choice where the database structure isn’t particularly normalized.
Dapper works with an ADO.NET IDbConnection object, which means that it will work with any database system for which there is an ADO.NET provider.
There is no reason why you cannot use both an ORM and a micro ORM in the same project.
What does Dapper do?
Here is a standard ADO.NET C# code for retrieving data from a database and materializing it as a collection of Product objects:
At its most basic level, Dapper replaces the highlighted block of assignment code in the example above with the following:
Dapper also creates the command and opens the connection if needed. If you use Dapper to manage basic assignments like this, it will save you hours. However, Dapper is capable of doing quite a bit more.
Where can you get Dapper?
Dapper is available from NuGet. It is compliant with .NET Standard 2.0, which means you can use it in .NET applications that target the .NET Framework and .NET Core. You can install the latest version using the following command with the .NET CLI:
Or run the following command to install the Dapper NuGet package in the Package Manager Console:
The source code for Dapper is available on GitHub. To clone the Dapper GitHub repository, run the following command in a Git Bash window:
Dapper – Micro ORM With Epic Possibilities
Object-relational mapping (ORM) revolutionized object-oriented programming by streamlining the storage and retrieval of data. However, most ORM solutions presume that data is mostly static and read-only, significantly reducing the performance of any application that primarily uses dynamic, always-changing information. Fortunately, Dapper fixes these issues in an easy-to-use package.
Dapper is a popular open-source, micro-ORM solution that is compatible with the .NET application framework. It lets you work with strongly typed data that constantly changes without spending hours writing code. It is especially useful when dealing with unnormalized databases.
However, Dapper has no concept of schema, nor will it translate your queries into SQL. Therefore, by reading further, you will learn how to get started with the ORM and a few of its benefits.
Table of Contents
What is Dapper ORM?
Waves of digital analysis of data sets with 3d render visualization. Futuristic observation of fluctuations in web research and experimentation. Financial forecasting of exchange flows.
ORMs make programming easier despite the intimidation new users often experience. To achieve this goal, they turn Structured Query Language (SQL) into an extension of your preferred programming language. As a result, you access and manipulate your data through a special object library.
Dapper is a simple, open-source ORM for the .NET framework and its associated languages and architectures. It provides a common interface for ADO.NET data readers that does not require you to map query results. It is very popular for its speed and performance comparable to raw ADO.NET code.
However, Dapper is not a full ORM. Instead, it is a micro-ORM with only the subset of the functionality focused on object mapping. Although, you can add features through extensions. As such, Dapper’s feature set can vary between implementations.
What does Dapper do?
In practice, Dapper converts long chains of data reading statements into a hash table accessible through a connection object in the following format:
var products = connection.Query<Product>(SQL);
The Dapper object auto generates enough SQL to retrieve the results, including the commands required to open a connection if needed. This way, Dapper can save you hours of coding, though it can do much more.
Why should I use Dapper?
.NET developers of all skill levels love using Dapper because it:
- Automatically maps query entries
- Automatically flushes the connection with each query
- Processes query faster than most other ORMs
- Does CRUD operations directly through connection objects
- Provides static and dynamic data queries of the database
- Offers generic results for both simple and complex data types
- Can store bulk data in a single operation
It is also easy to install and implement into your applications, with most installations taking less than 30 minutes to complete.
Using Dapper
One of Dapper’s best features is that you can treat it as an extension of the ADO.NET’s native IDbConnection object. You access its functionality as IDbConnection properties and methods. It is this accessibility that gives Dapper its powerful performance.
Because the extension’s methods reside under the “Dapper” namespace, you must add it to your project to use those methods. After that, you can use the following popular Dapper commands to perform CRUD operations.
- Execute: Create INSERT, UPDATE, and DELETE SQL queries, useful for launching stored procedures
- Query: Create SELECT queries including all variations
- QueryMultiple: batch executes multiple queries through a single command
You normally use these commands within a three-step process. At first, you create and open the connection. You then develop your CRUD query before sending it as a parameter of the Execute method.
Other common Dapper methods include QueryFirst, QueryFirstOrDefault, QuerySingle, and QuerySingleOrDefault. These methods offer the same functionality found in LINQ solutions without requiring you to install an additional LINQ framework.
How do you add Dapper ORM to your project?
However, nothing will happen until you install Dapper into your application. It is not a standard feature of any .NET compatible server architecture or the framework itself. As such, you must download It from NuGet or a related package provider such as GitHub.
From there, you have several ways to install the latest version of the framework:
- Through the .NET CLI: Issue the command dotnet add package Dapper from a .NET command line.
- The SQL Mapper file: Add the Dapper Mapping file found in the package to your project. It should look like DapperMappingFileForC#4.5.cs or SqlMapperAsync.cs depending on your version of the .NET framework.
- Visual Studio NuGet Package Manager: Open the Package Manager Console in Visual Studio and use the command install-package Dapper
Once installed, Dapper is fully compliant with .NET Standard 2.0. So, you can use it with any .NET application that uses the full .NET framework and Core.
What databases does Dapper support?
An ORM is only as good as its underlying database management system (DBMS), and Dapper is no different. You want something that fits your application while supporting the ORM query commands. Fortunately, Dapper offers fantastic database support thanks to its connection.
Because Dapper is a shell and extension for ADO.NET, it is compatible with any compatible DBMS. NET’s IDbConnection interface. You must provide the right connection string and SQL syntax, but the ORM will do the rest.
That means you can use Dapper ORM with the following databases and many others:
- SQL Server
- SQLite
- Postgres
- Access
- MySQL
- MariaDB
- Oracle
The benefits of using Dapper
While every application is different, there are a few things in common with every project you do. One of them is writing lots of code unless you are working on something small. Your project may even get even more tedious if you must frequently interact with SQL queries. This is because SQL statements can get unwieldy and difficult to troubleshoot and maintain.
Writing SQL by hand is great for performance-optimized tasks and prototyping, you need something less prone to error when developing large enterprise applications. That is the moment when Dapper takes center stage. You must appropriately structure your queries for it to work, but it will make your project significantly more manageable.
Along with better code manageability, Dapper offers many other benefits, most of which are shared by all micro-ORM, including:
- Fewer lines of code
- Faster performance in .NET than comparable full ORM solutions
- Static and dynamic object binding options
- Built-in object mapper
- Multiple queries and bulk data support
- Easy SQL query handling
- Uses the IDBConnection class as its interface
- Streamlined support for stored procedures
- Integrates into non-standard data sources such as Wikipedia and other web services
Disadvantages of Dapper
These benefits should suit most developers and applications, especially those that require the absolute best performance. Dapper is also best suited for development teams with strong SQL skills and resources. That is because Dapper has the distinct disadvantage of requiring SQL query strings for most of its functionality. This can make the micro-ORM less useful and more error-prone in some cases.
A real-world example of using Dapper ORM
Most developers use Dapper to process API CRUD operations because it allows them to modularize their applications. The following example will show you how to produce a .NET 6 API with these CRUD features and configure it through Dapper. While this example exists solely as a demonstration, you can use the same Dapper code and CRUD patterns with any type of data or application.
Pre-requisites
To follow this tutorial, you need to have:
Setting up a database
Before creating an API, you must have a database with which you will interact. To do that, open SQL Server Management Studio (SSMS). You will see a dialog box with the server type, server name, and authentication type. Click on Connect.
From the Object Explorer window on the left, right-click on ‘Databases’ and Select ‘New Database.’
In the New Database window, give a name to your database, for instance, OrderDb, and click ‘Ok.’ Your database has been created.
Once you have your database, you need to create a table you will query. To do that, Select ‘New Query’ and copy the following code into your SSMS window. Click on ‘Execute,’ refresh and find the table under the Tables tab in the Object Explorer window.
The Id column is an Identity column that will automatically increment numbers from 1 every time a new row is added.
Creating a new Web API project
Next, you need to create a new project inside Microsoft Visual Studio 2022.
From the taskbar, Select File->New->Project. Select ASP.Net Core Web API that supports C# as shown below:
Name your project, for instance, MethodPoetDapperCRUDTesting, and Click on ‘Next.’ Ensure that the Settings are as follows:
- Framework -> .Net 6.0 (Long-term support)
- Authentication Type -> None
- Configure for HTTPS -> Checked
- Use Controllers (uncheck to use minimal APIs) -> Checked
- Enable OpenAPI support -> Checked
Uncheck every other box and click on ‘Create.’ You now have your Web API project.
The project opens up to the following structure:
There is WeatherForecast API already present as an example. You can run that for understanding. However, we will make our own API for an extremely basic ordering system.
Setting up Database Connection with appsettings.json
To make your Web API talk to your database, you need to set up a connection with it. You will see some JSON commands already injected in there. Add Connection String below the ‘’AllowedHosts”.
The name of the Server is different for every individual. It will be present on that “Connect to Server” box that appears at the startup of the SSMS. As for the ‘Database,’ make sure its name matches the SSMS database. Otherwise, a connection will not be possible. Since the connection is safe, make ‘Trusted_Connection=true’.
Installing NuGet Packages
To make the web APIs work with Dapper, you need to install a couple of NuGet Packages. To install the packages, go to Tools->NuGet Package Manager->Manage NuGet Packages for Solution.
Select Browse and look for System.Data.SqlClient. Install this package for your solution.
You also need the Dapper Package to make the CRUD methods work with .Net 6. Search for “Dapper” in the Browse search bar and install the package.
Creating a Model Class to define the table structure
The model class will define the database table entries and their types. Since our database has an Id, Name, Quantity, and Price column, the Class will take the same.
In the Solution Explorer, select the project name i.e. MethodPoetDapperCRUDTesting, then select Add >> New Folder. Name this folder ‘Models’ and Enter.
Now add a class by right-clicking on the ‘Models’ and selecting Add>>Class. Name your class “Order”, and then press Add to create it.
The new Class will appear under “Model” as follows:
Copy and paste the script below to the Order Class.
Adding the Controller
In the next step, you need a controller to use the Order model you’ve created in the previous step. There will be a ‘WeatherForecastController’ File already present under the Controller section. Ignore it and perform the following steps to create an OrderController:
- Right Click on Controller in the Solution Explorer
- Select Add->Controller
- Choose API and then API Controller-Empty. Add it to move to the next step.
- Name your Controller ‘OrderController’ before selecting Add.
- A new Controller with your desired name will appear along with the example weather forecast controller.
Implementing the CRUD methods with Dapper and SQL Queries
If you don’t need further explanations of the code, copy and paste the following code snippet into the controller:
You can skip these explanations or read them to better understand what you are doing.
Before implementing the actual CRUD methods, you need to create the constructor with the IConfiguration to access the Connection Strings in appsettings.json. The code for doing that is given below:
Read operation
The first CRUD operation is READ, also known as the “Get Method.” In this tutorial, we will create two different get methods. One will get all the rows in the database, and the other will return a single row depending upon the entered ID. For creating these methods in Dapper, we need to begin by opening the connection, which is why you will see a connection variable in the code snippet below:
Since the Get Method would be required in all upcoming methods, we have refactored that method.
As for the READ operations for a single order, you will add a specific route to read only the order with the Id entered by the user.
Create operation
The CREATE operation, also known as “Post Method” in ASP.Net Core, is another asynchronous operation we are performing. Here the SQL query will be Insert Into instead of Select * From. The name and values of all the variables are inserted, and all the orders are returned wrapped in an Ok statement.
Update operation
The “Put Method” updates the records with new values. It takes the Id of the database record you want to update and changes only in the specified records. The entire database would be updated with the duplicate entries if the ID were not set.
Delete operation
To delete the record, you need to add a route with the orderId. The order with the ID specified by the user will get removed from the database.
Now, it is time to test your web APIs.
Testing Web APIs with Postman
Postman helps you test web API online. However, you need to install their desktop agent, which will let you send a request from your browser to your local database.
After you have downloaded the Postman desktop agent, open the Postman app. A window like this will appear:
Open a new request from the “+” button in the top left-hand corner.
Since this is a basic tutorial, we will not play around with the in-built examples. You will find some pre-written .json under properties in your Visual Studio Project.
Find the applicationURL under profiles; it will be of the type https://localhost:7182.
Extend it so that it reads https://localhost:7182\api\yourmodelclassname. The Model class, in our case, was ‘Order.’ Replace it with the above URL and paste this URL into the Postman web client.
Testing HTTP Get method
To test the Get Method, choose GET from the drop-down menu and hit SEND. A ‘200 OK’ status will represent a successful response. You can visualize the data in JSON, HTML, TEXT, or XML. In our case, we will be returning a JSON, and we should be getting an empty table back.
Testing HTTP Post method
To test the post method, choose POST from the drop-down menu, and enter the URL in the designated place.
Choose Body->raw->JSON in the options below the URL box.
Copy and paste the following JSON script in the box:
Hit Send and find the ‘200 OK’ Response for success. You will see all the orders saved in your database in the response window.
Testing HTTP Put method
The put method will update an existing entry by taking its ID. To test this method, choose PUT from the drop-down box, and enter the URL.
Select Body->raw->JSON again and paste the following script into the Postman window:
This will update the entry from ‘Pencils’ to ‘Pens.’ Look for the ‘200 OK’ response to check if the request is successful.
Testing HTTP Delete method
This method will test whether an entry is getting deleted from your database table. For this method, Choose DELETE from the drop-down menu, and place the URL in the box next to it.
Place the ID of the entry you want to delete next to the URL, for instance, https://localhost:7182/api/Order/1.
Note: The entry you are deleting or updating must already exist in the database
Hit the Send button and look for the ‘200 OK’ response to see if the API endpoint is working correctly.
If you had only one entry in the database before, it would be empty again.
Использование Dapper в ASP.NET WEB API 2 приложении
Dapper.NET — это «mini-ORM» на которой работает движок StackExchange и сайт StackOverflow в частности. Dapper это технология маппинга результатов sql-запросов с классами c#. Dapper чем то похож на Entity Framework, но за счет легковесности Dapper обеспечивает большую производительность и работает быстрее, нежели Entity Framework.
Далее рассмотрим как можно построить REST'ful api controller с запросами к базе через dapper.
1. Создаем class library для Dapper'a
2. Подключаем Dapper с nuget'a
3. Создаем WebApi проект в visual studio
Теперь когда у нас есть каркас, мы можем приступить к созданию базы данных.
4. Создание базы
- Переходим в SQL Management Studio
- Нажимаем правой кнопкой на базы данных и выбираем создать новую
- Вводим название новой базы (например Dapper) и нажимаем ОК.
5. Создаем таблицу "Книги"
для этого выполним следующий sql скрипт:
Теперь когда наша база готова, можем приступать к программированию.
5. Создаем класс Book
6. Создаем репозиторий для Book
Для класса книга, теперь можно создать репозиторий который будет выполнять CRUD операции.
Создаем интерфейс IBookRepository
И его реализацию BookRepository
Следует заметить, что мы используем DapperConnection connectionstring.
Добавим ее в webapi проект в web.config.
7. Переходим к созданию API BooksController'y
Следует отметить, что строчка private readonly IBookRepository _bookRepository = new BookRepository(); к использованию не желательна, тут она продемонстрирована ислючительно в качестве примера, я рекомендую использовать какой-то IoC контейнер и инджектить IBookRepository в конструктор.
Наш RESTful API готов к использованию, давайте протестируем его через постмен и посмотрим что получилось.
Добавление книги
Обновление записи книга
Получение списка книг
Получение книги по id
Удаление книги
Dapper это отличный инстумент который подойдет для высоконагруженных и посещаемых enterprise проектов таких как stackexchange. В остальных случаях Entity Framework отлично справляется с задачей основной ORM. Но она требует правильных настроек и правильного подхода к использованию и оптимизации. Если у вас есть достаточно высоконагруженные системы или запросы в них, можно приминять Dapper для оптимизации этих запросов (или же в EF использовать команду SqlQuery и писать чистый sql) так как Dapper работает в разы быстрее остальных ORM.
A Practical Guide to Dapper
Dapper is a lightweight framework for data access. Camilo Reyes explains how to query, call stored procedures, and more with Dapper in C#.
The Data Access Layer (DAL) is often the most critical part of any application. Dapper is a micro-ORM built with simplicity, top-notch performance, and ease of use in mind. In this take, I will walk you through what Dapper has to offer and why it has become the tool of choice for many high-demand solutions.
Dapper is a lightweight shim around ADO.NET for data access via extension methods. To keep this relevant to any real application, there is quite a bit of code, which I won’t be able to show, so I recommend downloading the repo from GitHub. The focus here is to walk through the code API and follow best practices for building a DAL. The set of extension methods can feel overwhelming because there is a lot of functionality to cover.
I recommend general familiarity with LINQ and the List<> generic class in C#. This guide can be used as a reference so you can read one section at a time. At each pause, I encourage you to play with the code and allow the information to sink in.
I opted to use only async methods because these are the ones anyone should be using in a production environment. C# and .NET make this relatively painless via async/await, albeit with caveats.
You will need the .NET 6 SDK and a copy of SQL Server preloaded with the AdventureWorks database to run the code sample. The connection string in the repo points to the MSLocalDB instance, which you can get from SQL Server Express. It is also possible to point this to another SQL Server instance via the DataSource property. Fortunately, Visual Studio is not required unless you prefer to use this tool.
ExecuteAsync
Whenever you see the ExecuteAsync method, it is safe to assume this performs a mutation to the backend database. The method returns an int with the number of rows affected by the query. In SQL, this typically means INSERT , UPDATE , or DELETE statements.