如何在ASP.NET Core Web API中用Dapper通过存储过程插入主从表多行数据？

Great question! When handling master-detail insert scenarios like creating an order and its associated line items, the critical piece is ensuring atomicity—meaning either both the master record and all detail records are saved, or none are. Dapper makes this straightforward with database transactions. Let's walk through a complete implementation for your ASP.NET Core Web API:

1. Define Your Entities and Request Models #

First, create classes to represent your database tables and the API request payload:

// 主表实体（对应OrdersMaster表）
public class OrdersMaster
{
    public int OrderId { get; set; } // 自增主键
    public DateTime OrderDate { get; set; }
    public string CustomerName { get; set; }
    // 添加其他订单相关字段，比如ShippingAddress、TotalAmount等
}

// 子表实体（对应OrderDetails表）
public class OrderDetails
{
    public int DetailId { get; set; } // 自增主键
    public int OrderId { get; set; } // 关联主表的外键
    public string ProductName { get; set; }
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }
    // 添加其他订单项字段，比如ProductId、Discount等
}

// API请求模型（接收前端传入的订单数据）
public class OrderCreateRequest
{
    public string CustomerName { get; set; }
    public List<OrderDetailRequest> OrderDetails { get; set; }
}

public class OrderDetailRequest
{
    public string ProductName { get; set; }
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }
}

2. Implement the Data Access Logic with Transactions #

Create a service class to handle the database operations—this is where we'll use Dapper with a transaction to ensure data consistency:

public interface IOrderService
{
    Task<int> CreateOrderWithDetailsAsync(OrdersMaster order, List<OrderDetails> details);
}

public class OrderService : IOrderService
{
    private readonly string _connectionString;

    // 通过依赖注入获取数据库连接字符串
    public OrderService(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("DefaultConnection");
    }

    public async Task<int> CreateOrderWithDetailsAsync(OrdersMaster order, List<OrderDetails> details)
    {
        using var connection = new SqlConnection(_connectionString);
        await connection.OpenAsync();

        // 开启事务：所有操作要么一起成功，要么一起回滚
        using var transaction = connection.BeginTransaction();
        try
        {
            // 1. 插入主表并获取自动生成的OrderId（SQL Server示例，其他数据库语法略有不同）
            var insertMasterSql = @"
                INSERT INTO OrdersMaster (OrderDate, CustomerName)
                OUTPUT INSERTED.OrderId
                VALUES (@OrderDate, @CustomerName);";

            var generatedOrderId = await connection.QuerySingleAsync<int>(insertMasterSql, order, transaction);

            // 2. 批量插入子表，关联刚生成的OrderId
            if (details?.Any() == true)
            {
                // 给每个订单项设置关联的OrderId
                foreach (var detail in details)
                {
                    detail.OrderId = generatedOrderId;
                }

                var insertDetailsSql = @"
                    INSERT INTO OrderDetails (OrderId, ProductName, Quantity, UnitPrice)
                    VALUES (@OrderId, @ProductName, @Quantity, @UnitPrice);";

                // Dapper的ExecuteAsync支持批量插入，效率远高于循环单条插入
                await connection.ExecuteAsync(insertDetailsSql, details, transaction);
            }

            // 提交事务：确认所有操作成功
            transaction.Commit();
            return generatedOrderId;
        }
        catch (Exception ex)
        {
            // 回滚事务：如果任何一步出错，撤销所有操作
            transaction.Rollback();
            // 可以在这里添加日志记录，或者抛出自定义异常给上层处理
            throw new Exception("Failed to create order and details", ex);
        }
    }
}

3. Wire Up the API Controller #

Create an API controller to expose the endpoint for creating orders:

[ApiController]
[Route("api/orders")]
public class OrdersController : ControllerBase
{
    private readonly IOrderService _orderService;

    public OrdersController(IOrderService orderService)
    {
        _orderService = orderService;
    }

    [HttpPost]
    public async Task<IActionResult> CreateOrder([FromBody] OrderCreateRequest request)
    {
        if (!ModelState.IsValid)
        {
            return BadRequest(ModelState);
        }

        try
        {
            // 把请求模型转换为数据库实体
            var order = new OrdersMaster
            {
                OrderDate = DateTime.UtcNow,
                CustomerName = request.CustomerName
                // 映射其他字段...
            };

            var orderDetails = request.OrderDetails.Select(d => new OrderDetails
            {
                ProductName = d.ProductName,
                Quantity = d.Quantity,
                UnitPrice = d.UnitPrice
                // 映射其他字段...
            }).ToList();

            var newOrderId = await _orderService.CreateOrderWithDetailsAsync(order, orderDetails);

            // 返回201 Created状态码和订单ID
            return CreatedAtAction(nameof(GetOrderById), new { id = newOrderId }, 
                new { OrderId = newOrderId, Message = "Order created successfully" });
        }
        catch (Exception ex)
        {
            return StatusCode(StatusCodes.Status500InternalServerError, 
                new { Message = ex.Message });
        }
    }

    // 可选：添加根据ID查询订单的接口
    [HttpGet("{id}")]
    public async Task<IActionResult> GetOrderById(int id)
    {
        // 实现订单查询逻辑...
        return Ok();
    }
}

4. Key Notes to Remember #

• Transaction Safety: Never skip using a transaction here—without it, you risk having an order in the master table with no corresponding line items (or vice versa) if one operation fails.
• Database-Specific ID Generation: The example uses SQL Server's OUTPUT INSERTED.OrderId to get the auto-generated ID. For other databases:
  • MySQL: Use SELECT 656316 after inserting the master record
  • PostgreSQL: Use RETURNING OrderId in the insert statement
• Dependency Injection: Don't forget to register your service in Program.cs:

  builder.Services.AddScoped<IOrderService, OrderService>();
  

• Validation: Add proper validation (e.g., data annotations on request models) to ensure incoming data is valid before hitting the database.

内容的提问来源于stack exchange，提问作者blue