MENU

Show Multiple Table Data on View In ASP.NET MVC

Filed Under: , , , , on
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.


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.
Related articles

Post a Comment

www.CodeNirvana.in

Protected by Copyscape
Copyright © Compilemode