How to use Dapper Plus in .NET Core

Dapper—the open-source and lightweight “micro ORM” (object-relational mapper) I’ve written about often—supports many databases including SQL Server, MySQL, SQLite, SQL CE, and Firebird. You can use Dapper in your applications to simplify data access while ensuring high performance. I’ve discussed Dapper in several articles:

• How to use the Dapper ORM in C#
• How to perform async operations using Dapper
• How to map object relationships using Dapper in ASP.NET Core
• How to work with Dapper and SQLite in ASP.NET Core

While Dapper provides excellent support for performing CRUD (create, read, update, delete) operations efficiently, it does not have built-in support for bulk operations (i.e., bulk insert, bulk update, bulk delete, and bulk merge).

Dapper Plus extends Dapper to provide this support. Essentially, Dapper Plus extends the IDbConnection and IDbTransaction interfaces of the Dapper library with highly efficient helpers that can perform bulk operations. In this article, we’ll examine how we can use the Dapper Plus library to perform bulk data operations in .NET Core applications.

Create a console application project in Visual Studio 2022 Preview

First off, let’s create a .NET Core console application project in Visual Studio 2022 Preview. Assuming Visual Studio 2022 Preview is already installed in your computer, follow the steps outlined below to create a new .NET Core console application project.

1. Launch the Visual Studio 2022 Preview IDE.
2. Click on “Create new project.”
3. In the “Create new project” window, select “Console App” from the list of templates displayed.
4. Click Next.
5. In the “Configure your new project” window, specify the name and location for the new project.
6. Click Next.
7. In the “Additional information” window shown next, choose “.NET 10.0 (Preview)” as the framework version you would like to use.
8. Leave all the check boxes unchecked since we’ll not need any of those features.
9. Click Create.

This will create a new .NET 10 console application project in Visual Studio 2022 Preview. We’ll use this .NET 10 console application project to work with bulk data operations using Dapper Plus in the subsequent sections of this article.

Install the Dapper and Dapper Plus NuGet packages

In our examples, we’ll use Dapper Plus to connect to and work with a SQL Server database. We’ll need to install both Dapper and Dapper Plus NuGet packages in the project because certain extension methods, such as Query, require the Dapper namespace in your program.

To install these packages, select the project in the Solution Explorer window, then right-click and select “Manage NuGet Packages.” In the NuGet Package Manager window, search for the Dapper.Plus and Microsoft.Data.Sqlite packages and install them.

Alternatively, you can install the Dapper Plus and Dapper packages using the NuGet Package Manager console by entering the commands below.

PM> Install-Package Z.Dapper.Plus
PM> Install-Package Dapper

Insert data in bulk using BulkInsert

To insert bulk data (i.e., multiple entity records) in your database, you can take advantage of the BulkInsert method. This method will insert multiple rows of data in the underlying database table all at once. Let us examine how to use the BulkInsert method to insert bulk data in a database.

Consider the following C# class.

class Author
{
    public int AuthorId { get; set; }
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
    public string Address { get; set; } = string.Empty;
    public string Phone { get; set; } = string.Empty;
}

The code given below illustrates how you can create a list of authors, populate data, and return the list.

static List GetAuthors()
{
    var authors = new List()
    {
        new Author() { AuthorId = 1, FirstName = "Joydip", LastName = "Kanjilal", Address = "Hyderabad, India", Phone = "1234567890" },
        new Author() { AuthorId = 2, FirstName = "Steve", LastName = "Smith", Address = "Texas, USA", Phone = "0987654321" }
    };
    return authors;
}

You map your entity using the DapperPlusManager.Entity method as shown below. Note how the Identity extension method has been called to configure the identity column, i.e., AuthorId in this example.

DapperPlusManager.Entity().Identity(x => x.AuthorId, true);

Finally, to insert bulk records in the underlying database, you would use the code given below.

string connectionString = "Specify your database connection string here.";
using (var connection = new SqlConnection(connectionString)
{
    connection.BulkInsert(authors);
}

In the preceding code, we have created an instance of the SqlConnection class and passed the database connection string as a parameter in its constructor. Then the BulkInsert method is called and the list of authors named authors is passed to it as an argument. Naturally, you must also specify the database string needed to connect to the database.

Update bulk data using BulkUpdate

You can take advantage of the BulkUpdate method of the Dapper Plus library to update multiple entity records in the database at once. The following piece of code shows how you can set the Phone fields of the Author database table to blanks.

using (var connection = new SqlConnection(connectionString))
{
    var data = connection.Query("Select * from Author").ToList();
    data.ForEach(x => x.Phone = string.Empty);
    connection.BulkUpdate(data);
}

Delete data in bulk using BulkDelete

To delete data stored in the database in bulk, you can use the BulkDelete method as shown in the following code.

using (var connection = new SqlConnection(connectionString))
{
    var data = connection.Query("Select * from Author where AuthorId > 1").ToList();
    connection.BulkDelete(data);
}

In the code snippet above, all Author records where the value of the AuthorId column is greater than 1 will be deleted from the underlying database table. In this example, this code when executed will delete only one record from the database table because we have only a couple of records in there.

Merge data in bulk using BulkMerge

In addition to inserting, updating, and deleting data in bulk, you can merge data in bulk as well. To achieve this, you can use the BulkMerge method of the Dapper Plus library. The following code shows how you can use the BulkMerge method to merge author records in the database table.

using (var connection = new SqlConnection(connectionString))
{
    var initialData = connection.Query("Select * From Author").ToList();
    var currentData = new List()
    {
        new Author() { AuthorId = 3, FirstName = "Anand", LastName = "Narayanaswamy", Address = "Cochin, India", Phone = "1111111111" },
        new Author() { AuthorId = 4, FirstName = "Rex", LastName = "Wills", Address = "London, UK", Phone = "9999999999" }
    };
    connection.BulkMerge(initialData, currentData);
}

In the preceding code, the initial author records stored in the database table are retrieved and assigned to an object named initialData. Then a new list of author records is created with new data values and assigned to an object named currentData. Finally, these two objects are merged using the BulkMerge method.

Considerations and takeaways

This being a minimalist implementation, I’ve omitted the scripts required to create the database tables and the source code required to connect to the database. To demonstrate how the bulk operations work, we’ve created only a few records. In real-life applications, you may have massive amounts of data to manipulate using bulk operations. And when you do, you will find that Dapper Plus comes in very handy.

Dapper Plus extends Dapper by providing support for fast bulk data operations using methods such as BulkInsert, BulkUpdate, BulkDelete, and BulkMerge. You can take advantage of Dapper Plus to perform complex and advanced operations that include cascading operations, transactions, table-per-type inheritance, and data annotations, making it suitable for enterprise applications. You can also use these methods asynchronously. I’ll discuss each of these advanced operations in a future post here.