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:

using System.Collections.Generic;

namespace GetMultipleTableRecordsUsingDapper.Models
{
    public  class Customer
    {
        public string Name { get; set; }
        public string CustomerType { get; set; }
        public string Gender { get; set; }

    }
    public class Region
    {
        public string Country { get; set; }
        public string State { get; set; }
        public string City { get; set; }
    }
    public class MasterDetails
    {
        public List<Customer> CustPersonal { get; set; }
        public List <Region> CustRegions { get; set; }

    }
}

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,

Create PROCEDURE GetMasterDetails
as
BEGIN

    SET NOCOUNT ON;

 Select Name,CustomerType,Gender  from CustomerMaster
 Select Country,State,City from RegionMaster
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

using Dapper;
using GetMultipleTableRecordsUsingDapper.Models;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace GetMultipleTableRecordsUsingDapper.Repository
{
    public class CustomerRepo
    {
        //To Handle connection related activities
        SqlConnection con;
        private void connection()
        {
            string constr = ConfigurationManager.ConnectionStrings["SqlConn"].ToString();
            con = new SqlConnection(constr);
        }
        /// <summary>
        /// Get Multiple Table details
        /// </summary>
        /// <returns></returns>
        public IEnumerable<MasterDetails> GetMasterDetails()
        {
            connection();
            con.Open();
            var objDetails = SqlMapper.QueryMultiple(con, "GetMasterDetails",commandType: CommandType.StoredProcedure);
              MasterDetails ObjMaster = new MasterDetails();

            //Assigning each Multiple tables data to specific single model class
            ObjMaster.CustPersonal = objDetails.Read<Customer>().ToList();
            ObjMaster.CustRegions = objDetails.Read<Region>().ToList();

            List<MasterDetails> CustomerObj = new List<MasterDetails>();
            //Add list of records into MasterDetails list
            CustomerObj.Add(ObjMaster);
            con.Close();

            return CustomerObj;

        }
    }
}

 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

using GetMultipleTableRecordsUsingDapper.Models;
using GetMultipleTableRecordsUsingDapper.Repository;
using System.Collections.Generic;
using System.Linq;
using System.Web.Mvc;

namespace GetMultipleTableRecordsUsingDapper.Controllers
{
    public class CustomerController : Controller
    {
        // GET: Customer
        public ActionResult MasterDetail()
        {
            CustomerRepo objDet = new CustomerRepo();
             MasterDetails CustData = new MasterDetails();

            List<MasterDetails> MasterData = objDet.GetMasterDetails().ToList();

            CustData.CustPersonal = MasterData[0].CustPersonal;
            CustData.CustRegions = MasterData[0].CustRegions;


            return View(CustData);
        }
    }
}

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

@model GetMultipleTableRecordsUsingDapper.Models.MasterDetails

@{
    ViewBag.Title = "www.compilemode.com";
}

@using (Html.BeginForm())
{
    <div class="form-horizontal">
      <hr />
        <div class="form-group">
            <h5 class="btn btn-primary">Customer Type Master</h5>
            <div class="col-md-12">
                <table class="table table-hover col-md-3">

                        <tr>
                            <th>
                                Name
                            </th>
                            <th>
                                Customer Type
                            </th>
                            <th>
                                Gender
                            </th>

                        </tr>


                    @{
                        //To make unique Id
                        int i = 0;
                        foreach (var item in Model.CustPersonal)
                        {

                            <tr>
                                <td>


                                    @Html.EditorFor(o => o.CustPersonal[i].Name, new { @id = "Name_" + i })
                                </td>
                                <td>
                                    @Html.EditorFor(o => o.CustPersonal[i].CustomerType, new { @id = "NCustomerType_" + i })


                                </td>
                                <td>
                                    @Html.EditorFor(o => o.CustPersonal[i].Gender, new { @id = "Gender_" + i })
                                </td>

                            </tr>
                            i++;
                        }
                    }
                </table>
            </div>
        </div>

        <div class="form-group">
            <h5 class="btn btn-primary">Region Master</h5>
            <div class="col-md-12">
                <table class="table table-condensed table-hover col-md-3">
                    <tr>
                        <th>
                            Country
                        </th>
                        <th>
                            State
                        </th>
                        <th>
                            City
                        </th>

                    </tr>
                    @{
                        //To make unique Id
                        int j = 0;
                        foreach (var item in Model.CustRegions)
                        {

                            <tr>
                                <td>


                                    @Html.EditorFor(o => o.CustRegions[j].Country, new { @id = "Country_" + j })
                                </td>
                                <td>
                                    @Html.EditorFor(o => o.CustRegions[j].State, new { @id = "State_" + j })


                                </td>
                                <td>
                                    @Html.EditorFor(o => o.CustRegions[j].City, new { @id = "City_" + j })
                                </td>

                            </tr>
                            j++;
                        }
                    }
                </table>
            </div>
        </div>

    </div>
 }

 

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:

• Bind HTML Table Using JSON Data In ASP.NET MVC
• ActionResults in ASP.NET MVC