How to execute multiple SQL Server views in C#

I've had this problem come up over that past couple years so I decided to write a blog post about how to make this work. When I'm working with large datasets from multiple data sources I often use a SQL view to combine the data so it can be returned in a single query. There are times when I want my API to return the results from multiple views without having to call the database n-number of times. This blog post shows how to return data from multiple SQL Server views in a single database trip.

The three major sections of this process are as follows:

1. Use a Single Database Connection: Execute all views in a single query using a single database connection.
2. Use a Single Command: Combine the SQL commands into a single command.
3. Read Multiple Result Sets: Use DbDataReader to read multiple result sets efficiently.

Step-by-Step Guide

Step 1: Define Your Entities

Define the entities that correspond to the views. These results can be any POCO class or view model. They should represent the dataset that is returned from each of the views.

public class View1Result
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class View2Result
{
    public int Id { get; set; }
    public string Description { get; set; }
}

public class View3Result
{
    public int Id { get; set; }
    public DateTime Date { get; set; }
}

public class View4Result
{
    public int Id { get; set; }
    public decimal Amount { get; set; }
}

Step 2: Configure the method

Configure the method for fetching data from the views. In the method below you combine your select statements from each view together in single DbCommand so they execute within a single database connection - therefore bypassing the need to make multiple trips to the database.

The results are returned in sequential order to the DbReader with multiple result sets. After reading your result set, a call to .NextResultSetAsync() will move the reader to the next result set in order of execution and iterate through the results adding to your POCO collection. It's important to have your query order in the same order so you are mapping the correct data.

GetMyData.cs:

using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Threading.Tasks;

public class DataService
{
  private readonly ApplicationDbContext _context;
  
    public GetMyData(ApplicationDbContext context)
    {
        _context = context;
    }

    public async Task<(List<View1Result>, List<View2Result>, List<View3Result>, List<View4Result>)> GetViews()
    {
        var view1Results = new List<View1Result>();
        var view2Results = new List<View2Result>();
        var view3Results = new List<View3Result>();
        var view4Results = new List<View4Result>();

        using (var command = Database.GetDbConnection().CreateCommand())
        {
            command.CommandText = @"
                SELECT * FROM View1;
                SELECT * FROM View2;
                SELECT * FROM View3;
                SELECT * FROM View4;";
            command.CommandType = CommandType.Text;

            await Database.OpenConnectionAsync();

            using (var reader = await command.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    view1Results.Add(new View1Result
                    {
                        Id = reader.GetInt32(0),
                        Name = reader.GetString(1)
                    });
                }

                await reader.NextResultAsync();

                while (await reader.ReadAsync())
                {
                    view2Results.Add(new View2Result
                    {
                        Id = reader.GetInt32(0),
                        Description = reader.GetString(1)
                    });
                }

                await reader.NextResultAsync();

                while (await reader.ReadAsync())
                {
                    view3Results.Add(new View3Result
                    {
                        Id = reader.GetInt32(0),
                        Date = reader.GetDateTime(1)
                    });
                }

                await reader.NextResultAsync();

                while (await reader.ReadAsync())
                {
                    view4Results.Add(new View4Result
                    {
                        Id = reader.GetInt32(0),
                        Amount = reader.GetDecimal(1)
                    });
                }

                //....and so on and so on
            }
        }

        return (view1Results, view2Results, view3Results, view4Results);
    }
}

Step 3: Call the Method from the Controller

Use the method in your controller to fetch the data and return it in the API response.

ViewsController.cs:

using Microsoft.AspNetCore.Mvc;
using System.Threading.Tasks;

[ApiController]
[Route("api/[controller]")]
public class ViewsController : ControllerBase
{
    private readonly IDataService _dataService;

    public ViewsController(IDataService dataService)
    {
        _dataService = dataService;
    }

    [HttpGet("GetViewResults)]
    public async Task<IActionResult> GetViewResults()
    {
        var (view1Results, view2Results, view3Results, view4Results) = await _dataService.GetViewResults();
        return Ok(new 
        { 
            View1Results = view1Results, 
            View2Results = view2Results, 
            View3Results = view3Results, 
            View4Results = view4Results 
        });
    }
}

Summary

• Single Database Connection: Use a single database connection to execute all views in a single query.
• Efficient Reading: Use DbDataReader to efficiently read multiple result sets.
• Combine Queries: Combine the SQL queries for all views into a single command to minimize database round trips.

By following this approach, you can ensure that all views are executed efficiently and the results are returned to C# collections.