Best Practices for Performing Inserts and Updates with Dapper

Core Design Philosophy of Dapper Framework

Dapper, as a lightweight ORM framework, emphasizes performance and simplicity in its design philosophy. Initially focusing primarily on query and execute operations, this design choice allows Dapper to achieve performance close to native ADO.NET while providing object mapping convenience. However, this approach requires developers to handle the implementation details of insert and update operations themselves.

Basic Insert and Update Operation Implementation

The most direct method for performing insert and update operations with Dapper involves using its Execute method with parameterized SQL statements. While this approach requires manual SQL writing, it offers maximum flexibility and control.

Here is an example implementation of an insert operation:

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    
    string insertSql = "INSERT INTO Users (Name, Email, CreatedDate) VALUES (@Name, @Email, @CreatedDate)";
    
    var result = connection.Execute(insertSql, new 
    {
        Name = "John Doe",
        Email = "john.doe@example.com",
        CreatedDate = DateTime.Now
    });
}

Update operations follow a similar pattern:

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    
    string updateSql = "UPDATE Users SET Name = @Name, Email = @Email WHERE Id = @Id";
    
    var result = connection.Execute(updateSql, new 
    {
        Id = 1,
        Name = "Jane Smith",
        Email = "jane.smith@example.com"
    });
}

Utilizing Dapper.Contrib Extension Library

To simplify CRUD operations, the Dapper community developed the Dapper.Contrib extension library. This library provides a series of convenient extension methods that significantly streamline common database operations.

First, install the Dapper.Contrib NuGet package, then add appropriate attributes to your entity classes:

[Table("Users")]
public class User
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public DateTime CreatedDate { get; set; }
}

Using Dapper.Contrib for insert operations:

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    
    var user = new User 
    { 
        Name = "Michael Brown", 
        Email = "michael.brown@example.com", 
        CreatedDate = DateTime.Now 
    };
    
    var insertedId = connection.Insert(user);
}

Implementation of update operations:

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    
    var user = connection.Get<User>(1);
    user.Email = "newemail@example.com";
    
    var success = connection.Update(user);
}

Performance Optimization and Batch Operations

When dealing with large-scale data insertion, performance becomes a critical consideration. As mentioned in the reference article, using Dapper's batch insertion may encounter performance issues in certain database systems like SQLite.

For performance optimization in batch insertion, consider the following strategies:

Using transactions to wrap batch operations:

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    
    using (var transaction = connection.BeginTransaction())
    {
        try
        {
            string insertSql = "INSERT INTO Users (Name, Email) VALUES (@Name, @Email)";
            
            var users = new List<object>
            {
                new { Name = "User1", Email = "user1@example.com" },
                new { Name = "User2", Email = "user2@example.com" },
                new { Name = "User3", Email = "user3@example.com" }
            };
            
            connection.Execute(insertSql, users, transaction);
            transaction.Commit();
        }
        catch
        {
            transaction.Rollback();
            throw;
        }
    }
}

For extremely large batch data, consider using database-specific bulk insertion functionality:

// SQL Server bulk insertion example
using (var bulkCopy = new SqlBulkCopy(connection))
{
    bulkCopy.DestinationTableName = "Users";
    
    var dataTable = new DataTable();
    dataTable.Columns.Add("Name", typeof(string));
    dataTable.Columns.Add("Email", typeof(string));
    
    // Add data rows
    foreach (var user in userList)
    {
        dataTable.Rows.Add(user.Name, user.Email);
    }
    
    bulkCopy.WriteToServer(dataTable);
}

Practical Application Scenario Analysis

Choosing appropriate insert and update strategies is crucial in different application scenarios. For simple single-record operations, Dapper.Contrib provides the most concise solution. For complex business logic or performance-sensitive scenarios, using the native Execute method with manual SQL statements may be more appropriate.

Consider an order processing scenario in an e-commerce system:

public class OrderService
{
    private readonly string _connectionString;
    
    public OrderService(string connectionString)
    {
        _connectionString = connectionString;
    }
    
    public int CreateOrder(Order order, List<OrderItem> items)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            connection.Open();
            
            using (var transaction = connection.BeginTransaction())
            {
                try
                {
                    // Insert main order record
                    string orderSql = @"
                        INSERT INTO Orders (CustomerId, OrderDate, TotalAmount) 
                        VALUES (@CustomerId, @OrderDate, @TotalAmount);
                        SELECT CAST(SCOPE_IDENTITY() as int)";
                    
                    var orderId = connection.QuerySingle<int>(orderSql, order, transaction);
                    
                    // Insert order details
                    string itemSql = @"
                        INSERT INTO OrderItems (OrderId, ProductId, Quantity, UnitPrice) 
                        VALUES (@OrderId, @ProductId, @Quantity, @UnitPrice)";
                    
                    var itemsWithOrderId = items.Select(item => new 
                    {
                        OrderId = orderId,
                        item.ProductId,
                        item.Quantity,
                        item.UnitPrice
                    });
                    
                    connection.Execute(itemSql, itemsWithOrderId, transaction);
                    
                    transaction.Commit();
                    return orderId;
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }
    }
}

Summary and Best Practice Recommendations

Based on in-depth analysis of Dapper insert and update operations, the following best practice recommendations emerge: For simple CRUD operations, prioritize using Dapper.Contrib to improve development efficiency. In scenarios requiring complex business logic or performance optimization, use the native Execute method with manual SQL statements. Always use transactions for batch operations to ensure data consistency, and select appropriate batch processing strategies based on data volume. Conduct thorough performance testing, especially when handling large datasets, to ensure the chosen solution meets application performance requirements.