Bulk Synchronize: The Best Way in Dapper to Synchronize Multiple Rows

The Dapper Plus BulkSynchronize extension method acts as a MIRROR operation; it makes your table exactly reflect your data source. This means it will UPDATE existing rows, INSERT new rows that do not exist in the database, and DELETE rows that are no longer present in your data source.

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

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

Online Example

The BulkSynchronize method may be less frequently used, but it remains one of the most powerful tools in the Dapper Plus arsenal, allowing you to create an exact mirror of an entire table or just a portion of it, as we will explore later in this article.

Benchmark

There isn't a traditional technique in Dapper for fully synchronizing a table—essentially, a mirror operation is not as straightforward as performing Insert or Merge operations.

However, let's examine the performance of our BulkSynchronize method:

As always, don't just take our word for it. Try our online benchmark on .NET Fiddle to see the performance difference for yourself and verify these results firsthand.

Getting Started with Bulk Synchronize

To effectively begin using the BulkSynchronize method, we recommend first familiarizing yourself with our comprehensive Bulk Extensions Methods articles.

Here is a quick recap:

• Asynchronous Synchronization: You can synchronize data asynchronously with the BulkSynchronizeAsync method.
• Chaining Operations: Enhance workflow efficiency by chaining operations with the AlsoBulkSynchronize and ThenBulkSynchronize methods.
• Flexible Usage: Use BulkSynchronize from a connection, transaction, or leverage a new Dapper Plus Context for greater control.
• Multiple Data Sources: The BulkSynchronize method supports synchronization across various DataSources, enhancing its adaptability.

// Example code will be provided here to demonstrate the use of BulkSynchronize

Online Example

This setup guides you through the initial steps to effectively use BulkSynchronize, emphasizing its flexibility and power in handling complex data synchronization scenarios.

Common Options / Scenarios

In this section, we explore some common options and scenarios that developers frequently use with the BulkSynchronize method:

• Synchronize Only a Subset of Your Data
• SynchronizeSoftDeleteFormula

For a comprehensive list of options, please refer to our options documentation.

Synchronize Only a Subset of Your Data

Suppose your table has a column named StoreID that acts like a tenant identifier. You can synchronize only a specific store by using the ColumnSynchronizeDeleteKeySubsetExpression or ColumnSynchronizeDeleteKeySubsetNames option. This ensures that only data from your data source for a specified store is synchronized, leaving all other store data unmodified.

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

Online Example

SynchronizeSoftDeleteFormula

If you prefer performing a soft delete instead of a hard delete, you can use the SynchronizeSoftDeleteFormula. This option allows you to update non-existing rows based on your formula, effectively enabling soft deletion where data is marked as inactive instead of being removed.

DapperPlusManager.Entity<Product>()
	.Table("Product")
	.Identity(x => x.ProductID)
	.UseBulkOptions(options => options.SynchronizeSoftDeleteFormula = "IsSoftDeleted = 1");
	
var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServer());
	
connection.BulkSynchronize(products);

Online Example

These options enhance the flexibility of the BulkSynchronize method, making it suitable for a variety of data handling scenarios.

Conclusion

In this article, we've explored the BulkSynchronize method, discussed why it is beneficial, and highlighted a few common scenarios where it can be particularly effective.

The BulkSynchronize method is unique and incredibly powerful. While not all projects may require such a feature, for those that do, having access to a pre-coded and thoroughly tested method can be a significant advantage. This saves you the time and effort of having to develop and debug a complex synchronization process on your own.