Post
Ask Question

Fetching Multiple Tables With Dapper In .NET 7 API

Introduction 

In this article, I'm going to explain how we can fetch data from Multiple Tables with Dapper, so first let's create some tables in SQL Server. Here I'm using SQL Server Management Studio v18.10, you can use that one installed on your machine.

CREATE TABLE states(NameOfState VARCHAR(20), stateID BIGINT IDENTITY PRIMARY KEy)

 After executing this query it will create a table with name states, but we need at least two tables so I'm going to create a second table with the name City

CREATE TABLE City(NameOfCity varchar(20), CityID BIGINT IDENTITY PRIMARY KEY, 
 stateID BIGINT, FOREIGN KEY (stateID) REFERENCES states(stateID))

Here I have created Foreign key relationships with the states table. After executing this query it will create a table with the name City 

Now let's add some values to these tables.

Adding values in the States table

INSERT INTO states(NameOfState) VALUES('Uttar Pradesh');
INSERT INTO states(NameOfState) VALUES('Madhya Pradesh');
INSERT INTO states(NameOfState) VALUES('Maharastra');

Adding values in the City table

INSERT INTO City(NameOfCity,stateID) VALUES('Chitrakoot',1);
INSERT INTO City(NameOfCity,stateID) VALUES('banda',1);
INSERT INTO City(NameOfCity,stateID) VALUES('Karwi',1);
INSERT INTO City(NameOfCity,stateID) VALUES('MUMBAI',3);

Let's  check if all the values are inserted or not by using the SELECT command

SELECT *FROM states
SELECT *FROM City

The following result will be shown in the output window


Figure-Content of tables

Now let's create a Stored Procedure that returns these values 

CREATE PROCEDURE getStateAndCity
AS
BEGIN 
SELECT *FROM states
SELECT *FROM City
END

If we execute this procedure by EXEC getStateAndCity command you'll get the same output as in the above Figure-Content of tables. Now we have done with the SQL part. Let's move forward to Visual Studio 2022.

First, open visual studio and click on create a new project


Figure-Creating new project

Next, we need to choose the API template with c# and click on the next button.


Figure -Choosing ASP.NET Core Web API Template

Now enter the project name and click on the next button.


Figure-Project Name 

Next, choose the .NET version, I'm using the .NET 7 that has Standard term support, and click on create button


Figure-Choosing the.NET version

Now our .NET 7 API Template is ready with the default weather forecast API. Now I'm going to add a Class with the name CountryDetail. To add this class just right-click on the project name click on add then choose the class and enter the class name CountryDetail and click on add button. This class will work as a model class.

namespace MltipletableWithDapperApi
{
    public class CountryDetail
    {
        public IEnumerable<States> States{ get; set; }
        public IEnumerable<City> City { get; set; }
    }
    public class States 
    {
        public string NameOfState { get; set; }
        public int stateID { get; set; }
    }
    public class City
    {
        public string NameOfCity { get; set; }
        public int CityID { get; set; }
        public int stateID { get; set; }
    }
}

 Here I have defined two model classes city and states with the same properties that we have created in the SQL server. We are using Dapper so here I'm going to use IEnumerable of those classes to hold the data from tables. Now our model is ready, now I'm going to use the WeatherForecastController to fetch the data from the SQL server.

[HttpGet]
[Route("StateAndCity")]
public async Task<IActionResult> StateAndCity()
{
}

This call will return the data from both tables states and cities. First of all, we need to add Dapper, and SQL client to our project. So let's add both using the NuGet package manager, please follow these steps to add these packages to our project.

Step 1

Right-click on the project name and click on the NuGet package manager


Figure-Open NuGet Package Manager

Step 2

Click on browse and type Dapper


Figure-searching for Dapper

Here we can see that it has already 190M downloads at the time I'm writing this article. 

Step 3

Now just check the check box with the project name and click on the install button and dapper will be added to our project 


Figure- Install dapper 

Now just repeat these steps for the SQL client, Now the Project configuration is done, now I want to introduce you to the terms that are being used to fetch data from the SQL server with Dapper

  1. SqlMapper
  2. QueryMultipleAsync

sqlMapper is used to perform query multiple that holds multiple data tables and QueryMultipleAsync is used to execute our command. We also need a connection from the database so I'll create a method that returns the connection.

So let's code 

private IDbConnection CreateConnection()
              => new SqlConnection("server=*********;Initial Catalog=TestDb;User ID=********;Password=**************");

You have to use your connection string credentials to create a good connection. Now let's add code on the controller two fetch data

[HttpGet]
[Route("StateAndCity")]
public async Task<IActionResult> StateAndCity()
{
   CountryDetail _CountryDetail = new CountryDetail();
    try
      {
           var procedure = "getStateAndCity";
           var objDetails = await SqlMapper.QueryMultipleAsync(CreateConnection(), 
           procedure, null, commandType: CommandType.StoredProcedure);
           _CountryDetail.States = objDetails.Read<States>().ToList();
           _CountryDetail.City = objDetails.Read<City>().ToList();
           return Ok(_CountryDetail);
       }
       catch
       {
           throw;
       }
  }

Here I have created the object of CountryDetail Class and got values from the objDetail and map them with the Read method. Now let's have a look at the output


Figure-output

Conclusion

In this article, we have learned about fetching data from multiple tables with dapper, Now you can easily fetch data from multiple tables with dapper. Here I have used the QueryMultiplrAsync to get multiple resultsets and SQL mapper to hold those resultsets.


Similar Articles