dapper

官网地址：https://github.com/DapperLib/Dapper
使用文档：https://www.learndapper.com

动态更新字段

使用字符串拼接动态构建 SQL

public void UpdateUserDynamic(User user, List<string> propertiesToUpdate)
{
    var parameters = new DynamicParameters();
    var setClauses = new List<string>();
    
    parameters.Add("Id", user.Id);
    
    foreach (var prop in propertiesToUpdate)
    {
        var value = typeof(User).GetProperty(prop)?.GetValue(user);
        parameters.Add(prop, value);
        setClauses.Add($"{prop} = @{prop}");
    }
    
    string sql = $"UPDATE Users SET {string.Join(", ", setClauses)} WHERE Id = @Id";
    
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Execute(sql, parameters);
    }
}

使用匿名对象

public void UpdateUserPartial(int id, object updateFields)
{
    var properties = updateFields.GetType().GetProperties();
    var setClauses = properties.Select(p => $"{p.Name} = @{p.Name}").ToList();
    
    string sql = $"UPDATE Users SET {string.Join(", ", setClauses)} WHERE Id = @Id";
    
    var parameters = new DynamicParameters(updateFields);
    parameters.Add("Id", id);
    
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Execute(sql, parameters);
    }
}

// 调用示例
UpdateUserPartial(1, new { Name = "New Name", Email = "new@email.com" });

查询返回的 List 类型

直接返回匿名类型的 List

using (var connection = new SqlConnection(connectionString))
{
    var result = connection.Query("SELECT Id, Name FROM Users WHERE Age > @Age", 
                                new { Age = 18 }).ToList();
    
    // result 是 List<dynamic> 类型
    foreach (var item in result)
    {
        Console.WriteLine($"Id: {item.Id}, Name: {item.Name}");
    }
}

将匿名类型结果映射到指定类型

// 定义目标类型
public class UserDto
{
    public int Id { get; set; }
    public string Name { get; set; }
}

// 查询并映射
using (var connection = new SqlConnection(connectionString))
{
    var result = connection.Query<UserDto>(
        "SELECT Id, Name FROM Users WHERE Age > @Age", 
        new { Age = 18 }).ToList();
    
    // result 是 List<UserDto> 类型
}

使用匿名类型作为参数并返回强类型

using (var connection = new SqlConnection(connectionString))
{
    var queryParams = new { MinAge = 18, MaxAge = 30 }; // 匿名参数
    
    var result = connection.Query<UserDto>(
        "SELECT Id, Name FROM Users WHERE Age BETWEEN @MinAge AND @MaxAge", 
        queryParams).ToList();
}

多映射到匿名类型

using (var connection = new SqlConnection(connectionString))
{
    var sql = @"SELECT u.*, p.* 
                FROM Users u 
                JOIN Posts p ON u.Id = p.UserId 
                WHERE u.Age > @Age";
    
    var result = connection.Query(sql, new { Age = 18 }, 
        (User user, Post post) => new { User = user, Post = post })
        .ToList();
    
    // result 是 List<匿名类型{User, Post}> 类型
}

动态返回匿名类型并转换为字典

using (var connection = new SqlConnection(connectionString))
{
    var result = connection.Query("SELECT Id, Name FROM Users")
                .Select(x => (IDictionary<string, object>)x)
                .ToList();
    
    // 现在可以像字典一样访问
    foreach (var row in result)
    {
        Console.WriteLine($"Id: {row["Id"]}, Name: {row["Name"]}");
    }
}

对查询结果进行linq操作

// 正确用法示例
var users = connection.Query<User>("SELECT * FROM Users WHERE Age >= @MinAge", 
                 new { MinAge = minAge }).ToList();
        
return users
    .Where(u => u.IsActive)
    .Select(u => new UserSummary {
        Id = u.Id,
        DisplayName = $"{u.FirstName} {u.LastName}",
        AccountAge = DateTime.Now.Year - u.JoinDate.Year
    })
    .ToList();
//⚠️：注意事项
//立即执行查询：当需要确保数据已完全加载时使用
//延迟执行：直接返回 IEnumerable 让调用方决定是否物化
// 避免不必要的内存化（ToList）
return connection.Query<User>(/*...*/)
       .AsEnumerable()  // 保持延迟执行
       .Select(u => new UserSummary { /*...*/ });

目录

dapper