Get Multiple Tables Using Dapper With Stored Procedure In ASP.NET Core

It’s possible that you’d like to execute a batch of SQL statements that return more than one result set. This prevents the need for additional database roundtrips.

I want two separate asynchronous calls to the database if the two objects are independent of one other. However, you don’t always have this option, or maybe you’re in a situation where a plain and simple solution is preferred.

In any case, Dapper can help you with numerous resultsets using the QueryMultiple method:

QueryMultiple delivers a GridReader object that allows you to access all of the available resultsets in sequence, from first to last. To use each result, simply call the Read method as explained in the previous code. In fact, it includes all of the specific methods that Query does.

Let’s have a look at an example.

Step 1: Make a DataBase With Several Tables and Stored Procedure

Here I created Table Employee and EmploeeDetails As well as created “GetEmployeeDetails” Stored Procedure

//Stored Procedure
ALTER Procedure [dbo].[GetEmployeeDetails]
As
Begin
   select * from Employee;

   select * from EmployeeDetails;
End

Step 2: Install the Nuget Packages listed below in a new Asp.net core web application project.

•        Dapper
•        System.Data.SqlClient
•        System.Configuration.ConfigurationManager

Step 3: Create Models 

Here I’ve Created two models here. EmployeeModel and EmployeeDetailsModel are two different types of employee models.

public class EmployeeModel
{
    public int EmpID { get; set; }
    public string EmpName { get; set; }
    public string Designation { get; set; }
    public string Department { get; set; }
    public string JoiningDate { get; set; }
}

public class EmployeeDetailsModel
{
    public int EmpID { get; set; }
    public string EmpName { get; set; }
    public string Mobile { get; set; }
    public string PresentAddress { get; set; }
    public string Area { get; set; }
    public string City { get; set; }
    public string Country { get; set; }
    public string Qualification { get; set; }
    public string Email { get; set; }
}

Step 4:  Add this code to the Controller

using AspNetDapper.Services;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;

namespace AspNetDapper.Controllers
{
    public class HomeController: Controller
    {
        private readonly ILogger<HomeController> _logger;
        private readonly IConfiguration _configuration;
        private readonly IEmployeeService _employeeServices;
        public IConfiguration configuation { get; private set; }

        public HomeController(ILogger<HomeController> logger, IEmployeeService employeeServices)
        {
            _configuration = configuation;
            _employeeServices = employeeServices;
            _logger = logger;
        }
        
        [HttpGet]
        public IActionResult EmployeeDetailsList()
        {
          var list = _employeeServices.GetEmployees();
          return View(list);
        }
    }
}

Step 5:  Create a Service and Paste code into it

using AspNetDapper.Models;
using Dapper;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;

namespace AspNetDapper.Services
{

  public class EmployeeService: IEmployeeService
  {
    private readonly IConfiguration _configuration;
    public string ConnectionString { get; private set; }
    public string providerName { get; private set; }

    public EmployeeService(IConfiguration configuration)
    {
       _configuration = configuration;
       ConnectionString = _configuration.GetConnectionString("DBConnection");
       providerName = "System.Data.SqlClient";
    }

    public IDbConnection Connection
    {
      get
      {
        return new SqlConnection(ConnectionString);
      }
    }
    public object GetEmployees()
    {
      try
      {
        using (IDbConnection dbConnection = Connection)
        {
          dbConnection.Open();
          var data = dbConnection.QueryMultiple("EXEC GetEmployeeDetails");
          var employeeListAndDetails = new Model
          {
            Employee = data.Read<EmployeeModel>().ToList(),
            EmployeesDetails = data.Read<EmployeeDetailsModel>().ToList()
           };
          return employeeListAndDetails;
        }
      }
      catch (Exception ex)
      {
        string errMsg = ex.Message;
        throw new NotImplementedException();
      }
    }
  }

  public interface IEmployeeService
  {
    public object GetEmployees();
  }
}