Bulk UpdateHow to Quickly Update Multiple Rows in Dapper

The Dapper Plus BulkUpdate extension method enables you to quickly update multiple rows in your database. This method is up to 50x faster than conventional Dapper update techniques and offers extensive customization with hundreds of options.

// Easy to use
connection.BulkUpdate(products);

// Easy to customize
connection.UseBulkOptions(options => options.BatchSize = 1000)
		  .BulkUpdate(products);

Online Example

In this article, we will explore why using the BulkUpdate method can be extremely beneficial for your projects. We will also discuss some common options and scenarios, such as conditional updating (only updating rows if at least one value is different).

Benchmark

The traditional technique for updating multiple rows in Dapper requires you to write your UPDATE statement and pass a list of entities to the execute method:

connection.Execute(@"
UPDATE Product SET Name = @Name, Description = @Description, Column1 = @Column1, Column2 = @Column2, Column3 = @Column3, Column4 = @Column4, Column5 = @Column5, Column6 = @Column6, Column7 = @Column7, Column8 = @Column8, Column9 = @Column9
WHERE ProductID = @ProductID", products);

The problem is similar to what we have seen in our Bulk Insert Benchmark; one database round-trip is required for every row that needs to be updated, making the entire update operation significantly slower than if you use the Dapper Plus BulkUpdate method.

Let's compare the performance of both techniques:

You can directly try this online benchmark in your browser.

Not only does BulkUpdate offer significantly better performance, but the code is also clearer and easier to maintain.

Getting Started with Bulk Update

To get started, you should read the Bulk Extensions Methods documentation as the concept is the same for all our bulk methods. Here is a recap:

• You can update asynchronously with the BulkUpdateAsync method.
• You can chain operations with the AlsoBulkUpdate and ThenBulkUpdate methods.
• You can use BulkUpdate from a connection, transaction, or a new Dapper Plus Context.
• You can utilize the BulkUpdate method with multiple different Data Sources.

Then you can learn how to map your entity to save it the way you want. You can even map the same entity multiple times using a mapping key

Common Options / Scenarios

In this section, we will explore some common options and scenarios that developers often use with the BulkUpdate method:

• Custom Key
• Conditional Update
• Coalesce Update

For more options, refer to our list of options documentation.

Custom Key

A common scenario when using BulkUpdate involves updating a large number of entities coming from an external data source. The entities you want to import may not be aware of the internal key of your system, only the external key.

In Dapper Plus, you can map your entities multiple times in different ways using a mapping key.

DapperPlusManager.Entity<Product>("CustomKey_Code")
	.Table("Product")
	.Identity(x => x.ProductID)
	.Key(x => x.Code);
	
var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer());
			
connection.BulkUpdate("CustomKey_Code", products);

Online Example

Conditional Update

Why update a row if no column values have been modified? This is often a requirement reported by our customers.

You can choose to update only if at least one column has a different value using the UpdateMatchedAndOneNotConditionExpression and UpdateMatchedAndOneNotConditionNames options:

DapperPlusManager.Entity<Product>()
	.Table("Product")
	.Identity(x => x.ProductID)
	.UseBulkOptions(options => options.UpdateMatchedAndOneNotConditionExpression = x => new { x.Name });
	
var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer());

connection.BulkUpdate(products);

Online Example

Coalesce Update

Handling null values is another common scenario reported to us. If your database column value is not null but your entity's property value is null, you can opt to keep the database value with the CoalesceOnUpdateExpression and CoalesceOnUpdateNames options:

DapperPlusManager.Entity<Product>()
	.Table("Product")
	.Identity(x => x.ProductID)
	.UseBulkOptions(options => options.CoalesceOnUpdateExpression = x => new { x.Name });
	
var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer());
	
connection.BulkUpdate(products);

Online Example

Conclusion

In this documentation, we introduced the BulkUpdate method. Its ease of use, coupled with hundreds of different options, makes this method particularly valuable. While manually coding multiple options could be challenging and prone to human error, using these options directly through Dapper Plus allows for faster coding and ensures that each feature has been thoroughly tested.