Display Multiple Tables Data in Single View From DataBase in ASP.NET MVC Using Dapper

Background

In earlier ASP.NET it was very straightforward and easy to display multiple tables data using DataSet but in ASP.NET MVC we need to mostly work with generic lists. Also in many forum posts and in my event speaking people asking me how to display multiple tables data in single view from database in ASP.NET MVC . So considering the preceding requirement I have decided to write this article .Now let's learn step by step, which helps beginners to learn how to display multiple tables data in single view from database in ASP.NET MVC.
 
Scenario

Lets consider we have a requirement  to display the master data in single view where Data coming from multiple tables . So in this scenario we need to create complex model class from multiple model classes.We have following two tables in Database from which we are going to display the data as in following screen shots.

 

So lets demonstrate preceding scenario by creating one simple ASP.NET MVC application
 
Step 1: Create an MVC Application.

Now let us start with a step by step approach from the creation of a simple MVC application as in the following:

  1. "Start", then "All Programs" and select "Microsoft Visual Studio 2015".

  2. "File", then "New" and click "Project", then select "ASP.NET Web Application Template", then provide the Project a name as you wish and click OK. After clicking, the following window will appear:
 

Step 2 : Add The Reference of Dapper ORM into Project.

Now the next step is to add the reference of Dapper ORM into our created MVC Project. Here are the steps:
  1. Right click on Solution ,find Manage NuGet Package manager and click on it.
  2. After as shown into the image and type in search box "dapper".
  3. Select Dapper as shown into the image .
  4. Choose version of dapper library and click on install button.
 
Step 3: Create Model Class

Now let us create the model class file named CustomerModel.cs by right clicking on model folder as in the following screenshot:
 
 

Note:

It is not mandatory that Model class should be in Models folder, it is just for better readability; you can create this class anywhere in the solution explorer. This can be done by creating different folder names or without folder name or in a separate class library.

CustomerModel.cs class file code snippet:
  1. using System.Collections.Generic;  
  2.   
  3. namespace GetMultipleTableRecordsUsingDapper.Models  
  4. {  
  5.     public  class Customer  
  6.     {  
  7.         public string Name { getset; }  
  8.         public string CustomerType { getset; }  
  9.         public string Gender { getset; }  
  10.   
  11.     }  
  12.     public class Region  
  13.     {  
  14.         public string Country { getset; }  
  15.         public string State { getset; }  
  16.         public string City { getset; }  
  17.     }  
  18.     public class MasterDetails  
  19.     {  
  20.         public List<Customer> CustPersonal { getset; }  
  21.         public List <Region> CustRegions { getset; }  
  22.   
  23.     }  

Step 4: Add Controller Class.

Now let us add the MVC 5 controller as in the following screenshot:
 
 

After clicking on Add button it will show the window. Specify the Controller name as Customer with suffix Controller.

Note:
  • Controller name must be having suffix as 'Controller' after specifying the name of controller.
Step 5 : Create  Stored procedure.

Create the stored procedure to get the multiple table records from database as,
  1. Create PROCEDURE GetMasterDetails  
  2. as  
  3. BEGIN  
  4.       
  5.     SET NOCOUNT ON;  
  6.   
  7.  Select Name,CustomerType,Gender  from CustomerMaster  
  8.  Select Country,State,City from RegionMaster  
  9. END 
Run the preceding script in sql it will generates the stored procedure to get the multiple table records from database .

Step 6: Create Repository class.

Now create Repository folder and Add CustomerRepo.cs class for database related operations, Now create methods in CustomerRepo.cs to get the multiple table data from stored procedure with the help of Dapper ORM as in the following code snippet:

CustomerRepo.cs
  1. using Dapper;  
  2. using GetMultipleTableRecordsUsingDapper.Models;  
  3. using System.Collections.Generic;  
  4. using System.Configuration;  
  5. using System.Data;  
  6. using System.Data.SqlClient;  
  7. using System.Linq;  
  8.   
  9. namespace GetMultipleTableRecordsUsingDapper.Repository  
  10. {  
  11.     public class CustomerRepo  
  12.     {  
  13.         //To Handle connection related activities   
  14.         SqlConnection con;  
  15.         private void connection()  
  16.         {  
  17.             string constr = ConfigurationManager.ConnectionStrings["SqlConn"].ToString();  
  18.             con = new SqlConnection(constr);  
  19.         }  
  20.         /// <summary>  
  21.         /// Get Multiple Table details  
  22.         /// </summary>  
  23.         /// <returns></returns>  
  24.         public IEnumerable<MasterDetails> GetMasterDetails()  
  25.         {  
  26.             connection();  
  27.             con.Open();  
  28.             var objDetails = SqlMapper.QueryMultiple(con, "GetMasterDetails",commandType: CommandType.StoredProcedure);  
  29.               MasterDetails ObjMaster = new MasterDetails();  
  30.   
  31.             //Assigning each Multiple tables data to specific single model class  
  32.             ObjMaster.CustPersonal = objDetails.Read<Customer>().ToList();  
  33.             ObjMaster.CustRegions = objDetails.Read<Region>().ToList();  
  34.   
  35.             List<MasterDetails> CustomerObj = new List<MasterDetails>();  
  36.             //Add list of records into MasterDetails list  
  37.             CustomerObj.Add(ObjMaster);  
  38.             con.Close();  
  39.               
  40.             return CustomerObj;  
  41.   
  42.         }  
  43.     }  

 Note
  1. In the above code we are manually opening and closing connection, however you can directly pass the connection string to the dapper without opening it. Dapper will automatically handle it.
Step 7: Create Method into the CustomerController.cs file.

Now modify the default code in CustomerController.cs class file to bind multiple HTML tables in single view from strongly typed complex model class with list of records, After modifying code will look like as follows,

CustomerController.cs
  1. using GetMultipleTableRecordsUsingDapper.Models;  
  2. using GetMultipleTableRecordsUsingDapper.Repository;  
  3. using System.Collections.Generic;  
  4. using System.Linq;  
  5. using System.Web.Mvc;  
  6.   
  7. namespace GetMultipleTableRecordsUsingDapper.Controllers  
  8. {  
  9.     public class CustomerController : Controller  
  10.     {  
  11.         // GET: Customer  
  12.         public ActionResult MasterDetail()  
  13.         {  
  14.             CustomerRepo objDet = new CustomerRepo();  
  15.              MasterDetails CustData = new MasterDetails();  
  16.   
  17.             List<MasterDetails> MasterData = objDet.GetMasterDetails().ToList();  
  18.   
  19.             CustData.CustPersonal = MasterData[0].CustPersonal;  
  20.             CustData.CustRegions = MasterData[0].CustRegions;  
  21.              
  22.   
  23.             return View(CustData);  
  24.         }  
  25.     }  
  26. }  
Step 8 : Creating strongly typed view named MasterDetail using MasterDetails class .

Right click on View folder of created application and choose add view , select MasterDetails class and choose 'create' scaffolding template as
 
 

Click on Add button then it will create the view named MasterDetail, Now open the MasterDetail.cshtml view, Then some default code you will see which is generated by MVC scaffolding template, Now modify default code to make as per our requirements, After modifying the code it will look like as in the following,

MasterDetail.cshtml
  1. @model GetMultipleTableRecordsUsingDapper.Models.MasterDetails  
  2.   
  3. @{  
  4.     ViewBag.Title = "www.compilemode.com";  
  5. }  
  6.   
  7. @using (Html.BeginForm())  
  8. {  
  9.     <div class="form-horizontal">  
  10.       <hr />  
  11.         <div class="form-group">  
  12.             <h5 class="btn btn-primary">Customer Type Master</h5>  
  13.             <div class="col-md-12">  
  14.                 <table class="table table-hover col-md-3">  
  15.                      
  16.                         <tr>  
  17.                             <th>  
  18.                                 Name  
  19.                             </th>  
  20.                             <th>  
  21.                                 Customer Type  
  22.                             </th>  
  23.                             <th>  
  24.                                 Gender  
  25.                             </th>  
  26.   
  27.                         </tr>  
  28.                       
  29.                       
  30.                     @{  
  31.                         //To make unique Id  
  32.                         int i = 0;  
  33.                         foreach (var item in Model.CustPersonal)  
  34.                         {  
  35.   
  36.                             <tr>  
  37.                                 <td>  
  38.   
  39.   
  40.                                     @Html.EditorFor(o => o.CustPersonal[i].Name, new { @id = "Name_" + i })  
  41.                                 </td>  
  42.                                 <td>  
  43.                                     @Html.EditorFor(o => o.CustPersonal[i].CustomerType, new { @id = "NCustomerType_" + i })  
  44.   
  45.   
  46.                                 </td>  
  47.                                 <td>  
  48.                                     @Html.EditorFor(o => o.CustPersonal[i].Gender, new { @id = "Gender_" + i })  
  49.                                 </td>                               
  50.   
  51.                             </tr>  
  52.                             i++;  
  53.                         }  
  54.                     }  
  55.                 </table>  
  56.             </div>  
  57.         </div>  
  58.         
  59.         <div class="form-group">  
  60.             <h5 class="btn btn-primary">Region Master</h5>  
  61.             <div class="col-md-12">  
  62.                 <table class="table table-condensed table-hover col-md-3">  
  63.                     <tr>  
  64.                         <th>  
  65.                             Country  
  66.                         </th>  
  67.                         <th>  
  68.                             State  
  69.                         </th>  
  70.                         <th>  
  71.                             City  
  72.                         </th>  
  73.   
  74.                     </tr>  
  75.                     @{  
  76.                         //To make unique Id  
  77.                         int j = 0;  
  78.                         foreach (var item in Model.CustRegions)  
  79.                         {  
  80.   
  81.                             <tr>  
  82.                                 <td>  
  83.   
  84.   
  85.                                     @Html.EditorFor(o => o.CustRegions[j].Country, new { @id = "Country_" + j })  
  86.                                 </td>  
  87.                                 <td>  
  88.                                     @Html.EditorFor(o => o.CustRegions[j].State, new { @id = "State_" + j })  
  89.   
  90.   
  91.                                 </td>  
  92.                                 <td>  
  93.                                     @Html.EditorFor(o => o.CustRegions[j].City, new { @id = "City_" + j })  
  94.                                 </td>  
  95.   
  96.                             </tr>  
  97.                             j++;  
  98.                         }  
  99.                     }  
  100.                 </table>  
  101.             </div>  
  102.         </div>  
  103.         
  104.     </div>  
  105.  } 
 
Common issues

While binding list of records your control Id's must be unique , otherwise same first record will repeat in all list So to avoid this we need to maintain unique id's for control . As we have maintained in preceding view manually by using incremental i and j variable counter and model properties .
Now after adding the Model, View and controller into our project. The solution explorer will look like as follows
 
 
Now we have done all coding to upload files .

Step 9 :
Now run the application.

After running the application then two table data will be shown in the following screen shot which look like as follows, 
 
 
I hope from all preceding examples we have learned how to display multiple tables data in single view from database in ASP.NET MVC using Dapper ORM

Note:
  • Download the Zip file of the sample application for a better understanding.
  • Since this is a demo, it might not be using proper standards, so improve it depending on your skills.
Summary

I hope this article is useful for all readers. If you have any suggestions please contact me.

Read more articles on ASP.NET: