Bulk Operations in Dapper for .NET Core

Bulk Operations in Dapper for .NET Core

Dapper is a high-performance micro-ORM that provides efficient ways to interact with databases. While it excels at executing simple queries, it also supports bulk operations, allowing developers to perform batch inserts, updates, and deletions efficiently. In this article, we'll explore how to implement bulk operations in Dapper and why they are crucial for optimizing database interactions.

 

Why Use Bulk Operations?

When dealing with large datasets, executing multiple single-row operations can lead to performance bottlenecks due to frequent database round trips. Bulk operations help to:

  • Improve Performance: Reduce the number of database calls by processing multiple rows in a single query.
  • Reduce Overhead: Minimize the overhead associated with multiple INSERT, UPDATE, or DELETE statements.
  • Enhance Scalability: Allow efficient handling of large volumes of data.

 

Bulk Insert Using Dapper

Dapper does not provide native bulk insert support like Entity Framework’s BulkInsert(), but you can achieve similar functionality using SqlBulkCopy or optimized multi-row inserts.

Using SqlBulkCopy for High-Performance Bulk Insert

using System;

using System.Data;

using System.Data.SqlClient;

using System.Collections.Generic;

using Dapper;

 

public class EmployeeRepository

{

    private readonly string _connectionString;

   

    public EmployeeRepository(string connectionString)

    {

        _connectionString = connectionString;

    }

 

    public void BulkInsertEmployees(IEnumerable<Employee> employees)

    {

        using (var connection = new SqlConnection(_connectionString))

        {

            connection.Open();

            using (var bulkCopy = new SqlBulkCopy(connection))

            {

                bulkCopy.DestinationTableName = "Employees";

                var table = new DataTable();

                table.Columns.Add("Name", typeof(string));

                table.Columns.Add("Email", typeof(string));

                table.Columns.Add("Salary", typeof(decimal));

                table.Columns.Add("Department", typeof(string));

 

                foreach (var emp in employees)

                {

                    table.Rows.Add(emp.Name, emp.Email, emp.Salary, emp.Department);

                }

 

                bulkCopy.WriteToServer(table);

            }

        }

    }

}

Benefits of Using SqlBulkCopy

  • Significantly faster than executing multiple INSERT statements.
  • Efficient memory usage when handling large data sets.
  • Direct mapping of data to a database table.

 

Bulk Update and Delete Using Dapper

Unlike bulk insert, Dapper does not provide built-in bulk update/delete methods, but we can achieve this using efficient SQL queries with Execute().

Bulk Update with Dapper

public void BulkUpdateEmployees(IEnumerable<Employee> employees)

{

    using (var connection = new SqlConnection(_connectionString))

    {

        connection.Open();

       

        var query = "UPDATE Employees SET Salary = @Salary, Department = @Department WHERE Email = @Email";

        connection.Execute(query, employees);

    }

}

Bulk Delete with Dapper

public void BulkDeleteEmployees(IEnumerable<int> employeeIds)

{

    using (var connection = new SqlConnection(_connectionString))

    {

        connection.Open();

       

        var query = "DELETE FROM Employees WHERE Id IN @Ids";

        connection.Execute(query, new { Ids = employeeIds });

    }

}

Benefits of Using Bulk Update/Delete

  • Batches updates and deletions into a single query execution.
  • Reduces database round trips, leading to faster execution.
  • Maintains database consistency with parameterized queries.

 

Conclusion

Bulk operations in Dapper can drastically improve database performance by minimizing the number of database calls and processing large datasets efficiently. Using SqlBulkCopy for inserts and batch updates/deletes with Execute() ensures high performance and scalability.

If you need even more advanced bulk operations, consider third-party libraries like Dapper.Bulk that extend Dapper’s functionality.

 

Comments

Post a Comment

Popular posts from this blog

Debouncing & Throttling in RxJS: Optimizing API Calls and User Interactions

RxJS Debounce vs Throttle in Angular – Best Practices & Examples What is RxJS — quick primer RxJS (Reactive Extensions for JavaScript) provides Observables and operators to model and manipulate streams of asynchronous events — from keystrokes and HTTP requests to timers and WebSocket messages. 💡 Why this matters: Operators like debounceTime and throttleTime help you control event frequency, reducing wasted CPU and network work while improving UX. 1. Debouncing — emit after silence Debouncing delays emitting a value until a specified period of inactivity. It's best for typeahead inputs where only the final value after the user stops typing is relevant. Live search example (production-ready) // search.component.ts import { Component, OnDestroy } from '@angular/core'; import { FormControl } from '@angular/forms'; import { Subject } from 'rxjs'; import { debounc...
Read more

Promises in Angular

🚀 Promises in Angular – The Complete Beginner’s Guide (with Examples) 📘 Introduction In modern Angular development, handling asynchronous operations is essential. Whether you're fetching data from an API, loading files, or wrapping browser-native APIs, Promises offer a clean and readable way to deal with one-time asynchronous operations. This guide explains: What Promises are How they compare with Observables Real-world examples using .then() and async/await When to use Promises vs Observables in Angular 📚 Table of Contents 🔍 What is a Promise? 🔁 Promise Lifecycle ✨ Key Features 📌 Syntax 💻 Example 1: Fetch 💻 Example 2: setTimeout 📊 Promises vs Observables 🎯 When to Use Promises 📦 Angular Service Example 🔄 Observable vs Promise ❓ FAQ ✅ Summary 🔍 What is a Promise? A Promise is a JavaScript object that represents the eventual success (fulfilled) or failure (rejected) of an asynchronous operation. 📌 Com...
Read more

Comprehensive Guide to C# and .NET Core OOP Concepts and Language Features

Comprehensive Guide to C# and .NET Core OOP Concepts Class A class is a blueprint for creating objects that encapsulate both data and behavior. Access Modifiers : public, internal, abstract, sealed control visibility and inheritance. Static Classes : Cannot be instantiated, contain only static members. Partial Classes : Split across files using partial keyword for better organization. public class Car { public string Model; public void Drive() => Console.WriteLine($"{Model} is driving."); } Analogy: A car blueprint used to build many actual cars. Benefits: Reusability, Encapsulation, Abstraction, Inheritance Object An object is an instance of a class. Managed by Garbage Collector for automatic memory cleanup. Boxing/Unboxing allows value types to be treated a...
Read more