ASP.NET Core Dapper：轻量级ORM使用指南-优快云博客

ASP.NET Core Dapper：轻量级ORM使用指南

【免费下载链接】aspnetcore dotnet/aspnetcore: 是一个 ASP.NET Core 应用程序开发框架的官方 GitHub 仓库，它包含了 ASP.NET Core 的核心源代码和技术文档。适合用于 ASP.NET Core 应用程序开发，特别是对于那些需要深入了解 ASP.NET Core 框架实现和技术的场景。特点是 ASP.NET Core 官方仓库、核心源代码、技术文档。项目地址: https://gitcode.com/GitHub_Trending/as/aspnetcore

概述

在ASP.NET Core应用开发中，数据访问是不可或缺的核心环节。虽然Entity Framework Core提供了强大的ORM（Object-Relational Mapping，对象关系映射）功能，但在某些高性能场景下，开发者可能需要更轻量级、更直接的数据库操作方案。Dapper作为一款轻量级ORM，以其卓越的性能和简洁的API设计，成为众多开发者的首选。

本文将深入探讨如何在ASP.NET Core中集成和使用Dapper，涵盖从基础配置到高级用法的完整实践指南。

Dapper核心优势

性能对比

mermaid

功能特性对比表

特性	Dapper	EF Core	ADO.NET
性能	⭐⭐⭐⭐⭐	⭐⭐	⭐⭐⭐⭐⭐
开发效率	⭐⭐⭐⭐	⭐⭐⭐⭐⭐	⭐⭐
学习曲线	⭐⭐	⭐⭐⭐	⭐⭐⭐⭐⭐
功能丰富度	⭐⭐⭐	⭐⭐⭐⭐⭐	⭐
缓存机制	无	有	无
迁移支持	无	有	无

环境准备与配置

安装必要包

首先通过NuGet安装Dapper和相关依赖：

<PackageReference Include="Dapper" Version="2.1.28" />
<PackageReference Include="System.Data.SqlClient" Version="4.8.5" />
<PackageReference Include="Microsoft.Extensions.Configuration" Version="8.0.0" />
<PackageReference Include="Microsoft.Extensions.DependencyInjection" Version="8.0.0" />

配置数据库连接

在appsettings.json中配置数据库连接字符串：

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=MyDatabase;Trusted_Connection=true;TrustServerCertificate=true;"
  }
}

依赖注入配置

在Program.cs中注册服务：

using System.Data;
using System.Data.SqlClient;
using Dapper;

var builder = WebApplication.CreateBuilder(args);

// 注册IDbConnection
builder.Services.AddScoped<IDbConnection>(sp => 
    new SqlConnection(builder.Configuration.GetConnectionString("DefaultConnection")));

// 注册Repository
builder.Services.AddScoped<IUserRepository, UserRepository>();

var app = builder.Build();

基础数据操作

实体类定义

public class User
{
    public int Id { get; set; }
    public string UserName { get; set; }
    public string Email { get; set; }
    public DateTime CreatedAt { get; set; }
    public bool IsActive { get; set; }
}

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public int CategoryId { get; set; }
}

基础Repository实现

public interface IUserRepository
{
    Task<User> GetByIdAsync(int id);
    Task<IEnumerable<User>> GetAllAsync();
    Task<int> CreateAsync(User user);
    Task<bool> UpdateAsync(User user);
    Task<bool> DeleteAsync(int id);
}

public class UserRepository : IUserRepository
{
    private readonly IDbConnection _connection;

    public UserRepository(IDbConnection connection)
    {
        _connection = connection;
    }

    public async Task<User> GetByIdAsync(int id)
    {
        const string sql = "SELECT * FROM Users WHERE Id = @Id";
        return await _connection.QueryFirstOrDefaultAsync<User>(sql, new { Id = id });
    }

    public async Task<IEnumerable<User>> GetAllAsync()
    {
        const string sql = "SELECT * FROM Users WHERE IsActive = 1";
        return await _connection.QueryAsync<User>(sql);
    }

    public async Task<int> CreateAsync(User user)
    {
        const string sql = @"
            INSERT INTO Users (UserName, Email, CreatedAt, IsActive)
            OUTPUT INSERTED.Id
            VALUES (@UserName, @Email, @CreatedAt, @IsActive)";

        return await _connection.ExecuteScalarAsync<int>(sql, user);
    }

    public async Task<bool> UpdateAsync(User user)
    {
        const string sql = @"
            UPDATE Users 
            SET UserName = @UserName, Email = @Email, IsActive = @IsActive
            WHERE Id = @Id";

        var affectedRows = await _connection.ExecuteAsync(sql, user);
        return affectedRows > 0;
    }

    public async Task<bool> DeleteAsync(int id)
    {
        const string sql = "UPDATE Users SET IsActive = 0 WHERE Id = @Id";
        var affectedRows = await _connection.ExecuteAsync(sql, new { Id = id });
        return affectedRows > 0;
    }
}

高级查询技巧

多表关联查询

public async Task<IEnumerable<UserWithProducts>> GetUsersWithProductsAsync()
{
    const string sql = @"
        SELECT u.*, p.*
        FROM Users u
        LEFT JOIN UserProducts up ON u.Id = up.UserId
        LEFT JOIN Products p ON up.ProductId = p.Id
        WHERE u.IsActive = 1";

    var userDict = new Dictionary<int, UserWithProducts>();

    await _connection.QueryAsync<User, Product, UserWithProducts>(
        sql,
        (user, product) => 
        {
            if (!userDict.TryGetValue(user.Id, out var userWithProducts))
            {
                userWithProducts = new UserWithProducts { User = user, Products = new List<Product>() };
                userDict.Add(user.Id, userWithProducts);
            }
            
            if (product != null)
                userWithProducts.Products.Add(product);
            
            return userWithProducts;
        },
        splitOn: "Id"
    );

    return userDict.Values;
}

public class UserWithProducts
{
    public User User { get; set; }
    public List<Product> Products { get; set; }
}

分页查询实现

public async Task<(IEnumerable<User> Users, int TotalCount)> GetUsersPagedAsync(
    int pageNumber, int pageSize, string searchTerm = null)
{
    const string countSql = @"
        SELECT COUNT(*) FROM Users 
        WHERE IsActive = 1 
        AND (@SearchTerm IS NULL OR UserName LIKE '%' + @SearchTerm + '%')";

    const string dataSql = @"
        SELECT * FROM Users 
        WHERE IsActive = 1 
        AND (@SearchTerm IS NULL OR UserName LIKE '%' + @SearchTerm + '%')
        ORDER BY CreatedAt DESC
        OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

    var parameters = new 
    { 
        SearchTerm = searchTerm,
        Offset = (pageNumber - 1) * pageSize,
        PageSize = pageSize
    };

    using var multi = await _connection.QueryMultipleAsync(
        countSql + ";" + dataSql, parameters);

    var totalCount = await multi.ReadFirstAsync<int>();
    var users = await multi.ReadAsync<User>();

    return (users, totalCount);
}

事务处理

单元OfWork模式实现

public interface IUnitOfWork : IDisposable
{
    IDbTransaction Transaction { get; }
    void BeginTransaction();
    void Commit();
    void Rollback();
}

public class UnitOfWork : IUnitOfWork
{
    private readonly IDbConnection _connection;
    private IDbTransaction _transaction;

    public UnitOfWork(IDbConnection connection)
    {
        _connection = connection;
    }

    public IDbTransaction Transaction => _transaction;

    public void BeginTransaction()
    {
        if (_transaction == null)
        {
            _connection.Open();
            _transaction = _connection.BeginTransaction();
        }
    }

    public void Commit()
    {
        _transaction?.Commit();
        _transaction?.Dispose();
        _transaction = null;
        _connection.Close();
    }

    public void Rollback()
    {
        _transaction?.Rollback();
        _transaction?.Dispose();
        _transaction = null;
        _connection.Close();
    }

    public void Dispose()
    {
        _transaction?.Dispose();
        _connection?.Dispose();
    }
}

事务使用示例

public class OrderService
{
    private readonly IUnitOfWork _unitOfWork;
    private readonly IOrderRepository _orderRepository;
    private readonly IProductRepository _productRepository;

    public OrderService(IUnitOfWork unitOfWork, IOrderRepository orderRepository, 
        IProductRepository productRepository)
    {
        _unitOfWork = unitOfWork;
        _orderRepository = orderRepository;
        _productRepository = productRepository;
    }

    public async Task<bool> CreateOrderAsync(Order order, List<OrderItem> items)
    {
        try
        {
            _unitOfWork.BeginTransaction();

            // 创建订单
            var orderId = await _orderRepository.CreateAsync(order);

            // 更新库存
            foreach (var item in items)
            {
                item.OrderId = orderId;
                await _orderRepository.AddItemAsync(item);
                
                // 减少库存
                await _productRepository.DecreaseStockAsync(item.ProductId, item.Quantity);
            }

            _unitOfWork.Commit();
            return true;
        }
        catch
        {
            _unitOfWork.Rollback();
            throw;
        }
    }
}

性能优化策略

批量操作优化

public async Task<int> BulkInsertUsersAsync(IEnumerable<User> users)
{
    const string sql = @"
        INSERT INTO Users (UserName, Email, CreatedAt, IsActive)
        VALUES (@UserName, @Email, @CreatedAt, @IsActive)";

    return await _connection.ExecuteAsync(sql, users);
}

public async Task<int> BulkUpdateUsersAsync(IEnumerable<User> users)
{
    const string sql = @"
        UPDATE Users 
        SET UserName = @UserName, Email = @Email, IsActive = @IsActive
        WHERE Id = @Id";

    return await _connection.ExecuteAsync(sql, users);
}

查询性能优化

public async Task<User> GetUserWithOptimizedQueryAsync(int id)
{
    const string sql = @"
        SELECT 
            u.Id, u.UserName, u.Email, u.CreatedAt, u.IsActive,
            p.Id, p.Name, p.Price
        FROM Users u
        LEFT JOIN UserProducts up ON u.Id = up.UserId
        LEFT JOIN Products p ON up.ProductId = p.Id
        WHERE u.Id = @Id";

    var userDict = new Dictionary<int, User>();

    await _connection.QueryAsync<User, Product, User>(
        sql,
        (user, product) => 
        {
            if (!userDict.TryGetValue(user.Id, out var currentUser))
            {
                currentUser = user;
                currentUser.Products = new List<Product>();
                userDict.Add(user.Id, currentUser);
            }
            
            if (product != null)
                currentUser.Products.Add(product);
            
            return currentUser;
        },
        new { Id = id },
        splitOn: "Id"
    );

    return userDict.Values.FirstOrDefault();
}

错误处理与日志记录

全局异常处理中间件

public class DapperExceptionMiddleware
{
    private readonly RequestDelegate _next;
    private readonly ILogger<DapperExceptionMiddleware> _logger;

    public DapperExceptionMiddleware(RequestDelegate next, ILogger<DapperExceptionMiddleware> logger)
    {
        _next = next;
        _logger = logger;
    }

    public async Task InvokeAsync(HttpContext context)
    {
        try
        {
            await _next(context);
        }
        catch (SqlException ex)
        {
            _logger.LogError(ex, "数据库操作异常");
            await HandleSqlExceptionAsync(context, ex);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "未处理的异常");
            await HandleExceptionAsync(context, ex);
        }
    }

    private static Task HandleSqlExceptionAsync(HttpContext context, SqlException exception)
    {
        context.Response.ContentType = "application/json";
        context.Response.StatusCode = StatusCodes.Status500InternalServerError;

        var errorResponse = new
        {
            Code = "DATABASE_ERROR",
            Message = "数据库操作失败",
            Details = exception.Message
        };

        return context.Response.WriteAsync(JsonSerializer.Serialize(errorResponse));
    }

    private static Task HandleExceptionAsync(HttpContext context, Exception exception)
    {
        context.Response.ContentType = "application/json";
        context.Response.StatusCode = StatusCodes.Status500InternalServerError;

        var errorResponse = new
        {
            Code = "INTERNAL_ERROR",
            Message = "服务器内部错误",
            Details = exception.Message
        };

        return context.Response.WriteAsync(JsonSerializer.Serialize(errorResponse));
    }
}

在Program.cs中注册中间件

// 添加异常处理中间件
app.UseMiddleware<DapperExceptionMiddleware>();

测试策略

单元测试示例

[TestFixture]
public class UserRepositoryTests
{
    private Mock<IDbConnection> _mockConnection;
    private UserRepository _repository;

    [SetUp]
    public void Setup()
    {
        _mockConnection = new Mock<IDbConnection>();
        _repository = new UserRepository(_mockConnection.Object);
    }

    [Test]
    public async Task GetByIdAsync_ShouldReturnUser_WhenUserExists()
    {
        // Arrange
        var expectedUser = new User { Id = 1, UserName = "testuser" };
        _mockConnection.Setup(c => c.QueryFirstOrDefaultAsync<User>(
            It.IsAny<string>(), 
            It.IsAny<object>(), 
            null, null, null))
            .ReturnsAsync(expectedUser);

        // Act
        var result = await _repository.GetByIdAsync(1);

        // Assert
        Assert.That(result, Is.Not.Null);
        Assert.That(result.Id, Is.EqualTo(1));
        Assert.That(result.UserName, Is.EqualTo("testuser"));
    }

    [Test]
    public async Task CreateAsync_ShouldReturnNewId_WhenUserCreated()
    {
        // Arrange
        var newUser = new User { UserName = "newuser", Email = "test@example.com" };
        _mockConnection.Setup(c => c.ExecuteScalarAsync<int>(
            It.IsAny<string>(), 
            It.IsAny<object>(), 
            null, null, null))
            .ReturnsAsync(123);

        // Act
        var result = await _repository.CreateAsync(newUser);

        // Assert
        Assert.That(result, Is.EqualTo(123));
    }
}

最佳实践总结

代码组织规范

mermaid

性能优化清单

连接管理
- 使用依赖注入管理连接生命周期
- 及时关闭连接和事务
- 避免频繁创建连接对象
查询优化
- 使用参数化查询防止SQL注入
- 合理使用异步方法
- 批量操作减少数据库往返
内存管理
- 及时释放大型数据集
- 使用分页处理大数据量
- 避免不必要的对象映射
监控与日志
- 记录慢查询日志
- 监控数据库连接池状态
- 设置合理的超时时间

安全注意事项

始终使用参数化查询
验证用户输入数据
限制数据库用户权限
定期更新数据库安全补丁
使用加密连接字符串

结语

Dapper在ASP.NET Core中的应用为开发者提供了高性能、轻量级的数据访问解决方案。通过合理的架构设计、性能优化和错误处理，可以构建出既高效又稳定的数据访问层。在实际项目中，应根据具体需求选择合适的ORM工具，Dapper特别适合对性能要求较高的场景。

记住，没有最好的工具，只有最适合的工具。Dapper与EF Core各有优势，合理的选择和使用才能发挥最大的价值。

创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考