MVC: Showing the results on page from more than 1 database table using View Models.
In this article, i will explain how to use ViewModels to show records from more than 1 database tables and display it to the user on page. The ViewModel will be passed in iEnumerable fashion to the View and the List will contain the records of all the tables used.
Using ViewModels to show the results of 2 tables on a page using MVC
Scenario: Its a library management System. Created 2 tables: Book and User. Book table will store data related to books and User table will store user details. We need to find out all books that are present with a user(i.e not available) and subsequently we will find out if a specific user possess how many number of books.
AIM:See below Image:
Using MVC (Model-View-Controller)
ViewModel:
BookStoreViewModel is our View model. We will create properties of each table ie; Book and User table. The return type will be its List. Lets take a look at it:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using NavedMVCApp.Models;
namespace NavedMVCApp.ViewModels
{
public class BookStoreViewModel
{
public List
public List
}
}
Model:
we need to find those books that are not available in the library and those users that possess the book. The LINQ to SQL query is used to achieve the target:
public IQueryable
{
return from book in BookUsers.Books
join users in BookUsers.Users
on book.BookId equals users.BookId
select book;
}
public IQueryable
{
return from users in BookUsers.Users
join book in BookUsers.Books
on users.BookId equals book.BookId
select users;
}
Controller:
We need to make sure the model will pass records from both the tables(Book and User)
BookStoreViewModel bookViewModel = new BookStoreViewModel();
public ActionResult BooksIssued()
{
bookViewModel.bookStore= bStore.FindBooksNotAvailable().ToList();
bookViewModel.user = bStore.FindUsersWithBook().ToList();
List
bookListViewModel.Add(bookViewModel);
return View(bookListViewModel);
}
View:
From controller we are passing List of type BookStoreViewModel to the view. The LIst type contains the records of both user and book table in an iEnumerated fashion. We have to use the namespace carefully when we are working with ViewModels. The code to be used under View is as below:
@model IEnumerable NavedMVCApp.ViewModels.BookStoreViewModel
@{
ViewBag.Title = "BooksIssued1";
}
Books issued to users:
Book Title
Book Author
User Name
Book Requested Date
Book Return Date
@foreach (var item in Model)
{
var m =0;
foreach (var i in item.bookStore)
{
var n = 0;
m++;
@Html.ActionLink("Return to library", "TakeBook", "User", new { id = i.BookId }, new { onclick = "javascript:return confirm('Are you sure you wish to return this book to the library?');" })
@i.BookTitle
@i.BookAuthor
@foreach (var j in item.user)
{
n++;
if(m==n)
{
@j.UserName
@j.BookRequestDate
@j.BookReturnDate
}
}
}
}