Display multiple Tables record in single View using multiple Model class in ASP.NET MVC

Last Reply one year ago By dharmendr

Posted one year ago

Hello Everyone, I am new to ASP MVC, I am doing a Hostel Management project for learning purposes. I am only using ADO.NET (no EF no Linq). I have two Models Residents and Allocations. I am trying to show the Residents and Allocations (room booking) in the same view. In the code below I am getting Residents Information first and then trying to get Rooms Allocated to each Resident but I am getting nowhere. Please see the code I have tried. 

In my code above, I want to show Resident's information (first name, last name, and email) from Resident's table which is related to the Allocations table, which holds the information about rooms. 

In the controller, I am trying to get the resident's information using lists and then check, is there any room allocated to that resident or not.

Will greatly appreciate any help, thanks in advance.

What I have tried:

public ActionResult ManageResidents()
{
    List<resident> mlist = new List<resident>();
    ResidentDB db = new ResidentDB();
    mlist = db.ResidentSelectAll(); 

    foreach (Resident rs in mlist)
    {
        Allocation mAllocation = new Allocation();
        AllocationDB db2 = new AllocationDB();
        mAllocation = db2.AllocationSelect(rs.ResidentAllocation.AllocationID);
        rs.ResidentAllocation = mAllocation;
        mlist.Add(rs);
    }
    ViewBag.Residents = mlist;
    return View(mlist);
}
Posted one year ago

Hi enceladus,

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

Model

CategoryModel

public class CategoryModel
{
    public int CategoryID { get; set; }
    public string CategoryName { get; set; }
    public string Description { get; set; }
}

ProductModel

public class ProductModel
{
    public int ProductID { get; set; }
    public string ProductName { get; set; }
    public decimal UnitPrice { get; set; }
    public int CategoryID { get; set; }
    public CategoryModel Category { get; set; }
}

Controller

public class HomeController : Controller
{
    // GET: /Home/
    public ActionResult Index()
    {
        List<ProductModel> products = GetProducts();
        foreach (ProductModel product in products)
        {
            CategoryModel category = GetCategory(product.CategoryID);
            product.Category = category;
        }

        return View(products);
    }

    private List<ProductModel> GetProducts()
    {
        List<ProductModel> products = new List<ProductModel>();
        string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        string query = "SELECT TOP 10 ProductID,ProductName,UnitPrice,CategoryID FROM Products ORDER BY NEWID()";
        using (SqlConnection con = new SqlConnection(conString))
        {
            using (SqlCommand cmd = new SqlCommand(query, con))
            {
                con.Open();
                SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    products.Add(new ProductModel
                    {
                        ProductID = Convert.ToInt32(sdr["ProductID"]),
                        ProductName = sdr["ProductName"].ToString(),
                        UnitPrice = Convert.ToDecimal(sdr["UnitPrice"]),
                        CategoryID = Convert.ToInt32(sdr["CategoryID"])
                    });
                }

                con.Close();
            }
        }

        return products;
    }

    private CategoryModel GetCategory(int categoryID)
    {
        CategoryModel category = new CategoryModel();
        string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        string query = "SELECT CategoryID,CategoryName,Description FROM Categories WHERE CategoryID = @CategoryID";
        using (SqlConnection con = new SqlConnection(conString))
        {
            using (SqlCommand cmd = new SqlCommand(query, con))
            {
                cmd.Parameters.AddWithValue("@CategoryID", categoryID);
                con.Open();
                SqlDataReader sdr = cmd.ExecuteReader();
                if (sdr.Read())
                {
                    category.CategoryID = Convert.ToInt32(sdr["CategoryID"]);
                    category.CategoryName = sdr["CategoryName"].ToString();
                    category.Description = sdr["Description"].ToString();

                }
                con.Close();
            }
        }

        return category;
    }
}

View

<%@ Page Language="C#" Inherits="System.Web.Mvc.ViewPage<IEnumerable<_185123_Multiple_Model_View.Models.ProductModel>>" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Index</title>    
</head>
<body>
    <table>
        <tr>
            <th>Product ID</th>
            <th>Product Name</th>
            <th>Price</th>
            <th>Category Name</th>
            <th>Description</th>
        </tr>
        <% foreach (var item in Model)
           { %>
        <tr>
            <td><%: item.ProductID %></td>
            <td><%: item.ProductName %></td>
            <td><%: String.Format("{0:F}", item.UnitPrice) %></td>
            <td><%: item.Category.CategoryName %></td>
            <td><%: item.Category.Description %></td>
        </tr>
        <% } %>
    </table>
</body>
</html>

Screenshot