通过ASP.NET核心Web API使用存储过程从SQL Server返回多个行集

// 数据访问层
public class DataAccessLayer
{
    private readonly string _connectionString;

    public DataAccessLayer(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("DefaultConnection");
    }

    public async Task<IEnumerable<IEnumerable<dynamic>>> ExecuteStoredProcedure()
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            await connection.OpenAsync();

            using (var command = new SqlCommand("YourStoredProcedureName", connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                var reader = await command.ExecuteReaderAsync();

                var result = new List<IEnumerable<dynamic>>();

                do
                {
                    var rows = new List<dynamic>();

                    while (await reader.ReadAsync())
                    {
                        var row = new ExpandoObject() as IDictionary<string, object>;

                        for (var i = 0; i < reader.FieldCount; i++)
                        {
                            row.Add(reader.GetName(i), reader[i]);
                        }

                        rows.Add(row);
                    }

                    result.Add(rows);
                }
                while (await reader.NextResultAsync());

                return result;
            }
        }
    }
}

// 控制器
[ApiController]
[Route("api/[controller]")]
public class YourController : ControllerBase
{
    private readonly DataAccessLayer _dataAccessLayer;

    public YourController(DataAccessLayer dataAccessLayer)
    {
        _dataAccessLayer = dataAccessLayer;
    }

    [HttpGet]
    public async Task<ActionResult<IEnumerable<IEnumerable<dynamic>>>> Get()
    {
        var result = await _dataAccessLayer.ExecuteStoredProcedure();

        return Ok(result);
    }
}