ASP.NET MVC 5 Webgrid Displaying Data From Two Tables (Models) With Pagination With Entity Framework Database First Method

This is a simple tutorial describing how to use WEB GRID to display data from two linked tables. The Tables are Category Table and Product Table. With Web Grid, it is easy to do pagination and sorting of records. It is an ASP.NET MVC5 Application with SQL server Database.

This is a simple tutorial describing how to use Web Grid to display the data from two linked tables. The tables are Category and Product respectively. With Web Grid, it is easy to perform the pagination and sorting of records. It is an ASP.NET MVC5 Application with SQL Server Database.

Softwares Required

  1. Visual Studio2013 or above
  2. Entity Framework 6.0(Available with Visual Studio)
  3. NET Framework 4.5 or above(Available with Visual Studio)
  4. SQL Server Database (Available with Visual Studio)

Start Visual Studio.

Then, create a new MVC Project as displayed below.

File - New - Project

Name the Project as ShoppingCart

ASP.NET MVC 5 Webgrid Displaying Data From Two Tables (Models) With Pagination With Entity Framework Database First Method 
 
ASP.NET MVC 5 Webgrid Displaying Data From Two Tables (Models) With Pagination With Entity Framework Database First Method 

Choose Project type as MVC, as shown below.

ASP.NET MVC 5 Webgrid Displaying Data From Two Tables (Models) With Pagination With Entity Framework Database First Method 

The project is created and opens as shown below.

ASP.NET MVC 5 Webgrid Displaying Data From Two Tables (Models) With Pagination With Entity Framework Database First Method 

Creating Database file Store.mdf

Right click on App_Data Folder - Add - New Item - Choose SQL Server Database

Name it Store.mdf as displayed below. Server Explorer window opens. Create two tables - Category and Product, and populate them by executing the provided queries below.

ASP.NET MVC 5 Webgrid Displaying Data From Two Tables (Models) With Pagination With Entity Framework Database First Method 
 
ASP.NET MVC 5 Webgrid Displaying Data From Two Tables (Models) With Pagination With Entity Framework Database First Method 

Store.mdf Database File is created as shown below. Right click on Store.mdf file - Open

ASP.NET MVC 5 Webgrid Displaying Data From Two Tables (Models) With Pagination With Entity Framework Database First Method
 
ASP.NET MVC 5 Webgrid Displaying Data From Two Tables (Models) With Pagination With Entity Framework Database First Method 
  1. CREATE TABLE [dbo].[Category] (  
  2.     [CategoryId]   INT           NOT NULL,  
  3.     [CategoryName] NVARCHAR (30) NOT NULL,  
  4.     [Remark]       NVARCHAR (50) NOT NULL,  
  5.     PRIMARY KEY CLUSTERED ([CategoryId] ASC)  
  6. );  
  7.   
  8. INSERT INTO [dbo].[Category] ([CategoryId], [CategoryName], [Remark]) VALUES (1, N'Computer', N'Desktop & Laptopd')  
  9. INSERT INTO [dbo].[Category] ([CategoryId], [CategoryName], [Remark]) VALUES (2, N'Storage', N'Pendrive & HardDisk')  
  10. INSERT INTO [dbo].[Category] ([CategoryId], [CategoryName], [Remark]) VALUES (3, N'Acssories', N'Mouse etc')  
  11.   
  12. CREATE TABLE [dbo].[Product] (  
  13.     [ProductId]    INT           NOT NULL,  
  14.     [ProductName]  NVARCHAR (30) NOT NULL,  
  15.     [Price]        DECIMAL (18)  NOT NULL,  
  16.     [ReorderLevel] INT           NOT NULL,  
  17.     [CatId]        INT           NOT NULL,  
  18.     PRIMARY KEY CLUSTERED ([ProductId] ASC),  
  19.     CONSTRAINT [fk_cat_product_id] FOREIGN KEY ([CatId]) REFERENCES [dbo].[Category] ([CategoryId]) ON DELETE CASCADE  
  20. );  
  21.   
  22. INSERT INTO [dbo].[Product] ([ProductId], [ProductName], [Price], [ReorderLevel], [CatId]) VALUES (1, N'Lenovo5000'CAST(25500 AS Decimal(18, 0)), 24, 1)  
  23. INSERT INTO [dbo].[Product] ([ProductId], [ProductName], [Price], [ReorderLevel], [CatId]) VALUES (2, N'SanDisk Penrive'CAST(250 AS Decimal(18, 0)), 300, 2)  
  24. INSERT INTO [dbo].[Product] ([ProductId], [ProductName], [Price], [ReorderLevel], [CatId]) VALUES (3, N'sonyharddisk'CAST(3000 AS Decimal(18, 0)), 31, 2)  
  25. INSERT INTO [dbo].[Product] ([ProductId], [ProductName], [Price], [ReorderLevel], [CatId]) VALUES (4, N'Sony Mouse'CAST(350 AS Decimal(18, 0)), 21, 3)  

Right-click on Tables - New Query

ASP.NET MVC 5 Webgrid Displaying Data From Two Tables (Models) With Pagination With Entity Framework Database First Method 

Copy and paste the following SQL for creating category table as above, and execute it by clicking the arrow symbol on left. Category Table will be created.

Insert the records by executing insert SQL as displayed below,

ASP.NET MVC 5 Webgrid Displaying Data From Two Tables (Models) With Pagination With Entity Framework Database First Method 

Similarly create Product table and insert Product records.

Refresh the Tables folder in server explorer window, The two newly created tables will be seen.

ASP.NET MVC 5 Webgrid Displaying Data From Two Tables (Models) With Pagination With Entity Framework Database First Method 

Entity Framework database models will be created by the Database-First method.

Right Click on Models Folder - Add - New Item

ASP.NET MVC 5 Webgrid Displaying Data From Two Tables (Models) With Pagination With Entity Framework Database First Method 

Select ADO.NET Entity Data Model and change the name to StoreModel then click the Add button

ASP.NET MVC 5 Webgrid Displaying Data From Two Tables (Models) With Pagination With Entity Framework Database First Method 
 
ASP.NET MVC 5 Webgrid Displaying Data From Two Tables (Models) With Pagination With Entity Framework Database First Method 

Select EF Designer from database as shown above and click next.

ASP.NET MVC 5 Webgrid Displaying Data From Two Tables (Models) With Pagination With Entity Framework Database First Method 

Choose Store.mdf Database File and name the connection string DBContext as displayed above.

ASP.NET MVC 5 Webgrid Displaying Data From Two Tables (Models) With Pagination With Entity Framework Database First Method 

Then click the next button. Select the tables as displayed below and click the Finish button.

ASP.NET MVC 5 Webgrid Displaying Data From Two Tables (Models) With Pagination With Entity Framework Database First Method 

The below window will be displayed three times and click on the ok button three times

ASP.NET MVC 5 Webgrid Displaying Data From Two Tables (Models) With Pagination With Entity Framework Database First Method 

StoreModel.edmex File will be created in Models Folder along with Category.cs and Product.cs Model classes.

ASP.NET MVC 5 Webgrid Displaying Data From Two Tables (Models) With Pagination With Entity Framework Database First Method 

Joining Category.cs and Product.cs Model classes, a new model class will be created named ProductWithCategory.cs .

ASP.NET MVC 5 Webgrid Displaying Data From Two Tables (Models) With Pagination With Entity Framework Database First Method 

Right Click on Models Folder - Add - Select Class as displayed above.

ASP.NET MVC 5 Webgrid Displaying Data From Two Tables (Models) With Pagination With Entity Framework Database First Method 
 
ASP.NET MVC 5 Webgrid Displaying Data From Two Tables (Models) With Pagination With Entity Framework Database First Method 

Modify the ProductWithCategory class like below,

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5.   
  6. namespace ShoppingCart.Models  
  7. {  
  8.     public class ProductWithCategory  
  9.     {  
  10.          
  11.         public ProductWithCategory()  
  12.         {  
  13.   
  14.         }  
  15.         public ProductWithCategory(Product p,Category c)  
  16.         {  
  17.             //TO DO: Complete Member Initialization  
  18.             this.Product = p;  
  19.             this.Category = c;  
  20.         }  
  21.         public Product Product { getset; }  
  22.         public Category Category { getset; }  
  23.     }  
  24. }  

In the Controller folder click on the HomeController.cs. It is displayed like below

ASP.NET MVC 5 Webgrid Displaying Data From Two Tables (Models) With Pagination With Entity Framework Database First Method 

Modify the Index() Method like below,

  1. Using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.Mvc;  
  6. using ShoppingCart.Models;  
  7.   
  8. namespace ShoppingCart.Controllers  
  9. {  
  10.     public class HomeController : Controller  
  11.     {  
  12.         DBContext db = new DBContext();  
  13.         public ActionResult Index()  
  14.         {  
  15.             var query = from p in db.Products  
  16.                                  join c in db.Categories on p.CatId equals c.CategoryId   
  17.                                 
  18.                                  select new ProductWithCategory{ Product=p, Category=c };  
  19.             var model = query.ToList();  
  20.             return View(model);  
  21.         }  
  22.   
  23.         public ActionResult About()  
  24.         {  
  25.             ViewBag.Message = "Your application description page.";  
  26.   
  27.             return View();  
  28.         }  
  29.   
  30.         public ActionResult Contact()  
  31.         {  
  32.             ViewBag.Message = "Your contact page.";  
  33.   
  34.             return View();  
  35.         }  
  36.     }  
  37. }  

Connection String in Web.Config File is,

  1. <connectionStrings>      
  2.     <add name="DBContext" connectionString="metadata=res://*/Models.StoreModel.csdl|res://*/Models.StoreModel.ssdl|res://*/Models.StoreModel.msl;provider=System.Data.SqlClient;provider connection string="data source=(LocalDB)\MSSQLLocalDB;attachdbfilename=|DataDirectory|\Store.mdf;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />  
  3. </connectionStrings>  

Open Views Folder - Home Folder->Open Index.cshtml File.

ASP.NET MVC 5 Webgrid Displaying Data From Two Tables (Models) With Pagination With Entity Framework Database First Method 

Replace Code in Index.cshtml with Code below

  1. @model IEnumerable<ShoppingCart.Models.ProductWithCategory>  
  2.   
  3. @{  
  4.     ViewBag.Title = "Index";  
  5.     Layout = null;  
  6.   
  7.     WebGrid webGrid = new WebGrid(Model, canPage: true, rowsPerPage: 2,  
  8.     selectionFieldName: "selectedRow", ajaxUpdateContainerId: "gridContent");  
  9.     webGrid.Pager(WebGridPagerModes.All);  
  10.   
  11. }  
  12.   
  13. <!DOCTYPE html>  
  14.   
  15. <html>  
  16. <head>  
  17.     <meta name="viewport" content="width=device-width" />  
  18.     <title>Index</title>  
  19.     <style type="text/css">  
  20.         body {  
  21.             font-family: Arial;  
  22.             font-size: 10pt;  
  23.         }  
  24.   
  25.         .Grid {  
  26.             border: 1px solid #ccc;  
  27.             border-collapse: collapse;  
  28.         }  
  29.   
  30.             .Grid th {  
  31.                 background-color: #F7F7F7;  
  32.                 font-weight: bold;  
  33.             }  
  34.   
  35.             .Grid th, .Grid td {  
  36.                 padding: 5px;  
  37.                 border: 1px solid #ccc;  
  38.             }  
  39.   
  40.             .Grid, .Grid table td {  
  41.                 border: 0px solid #ccc;  
  42.             }  
  43.   
  44.                 .Grid th a, .Grid th a:visited {  
  45.                     color: #333;  
  46.                 }  
  47.     </style>  
  48. </head>  
  49. <body>  
  50.   
  51.     <center>  
  52.   
  53.         <h1>Products</h1>  
  54.   
  55.         <p style="align-items:initial;">  
  56.             @Html.ActionLink("Create New""Create")  
  57.         </p>  
  58.   
  59.         @webGrid.GetHtml(  
  60.         htmlAttributes: new { @id = "WebGrid", @class = "Grid" },  
  61.         columns: webGrid.Columns(  
  62.                  webGrid.Column("Product.ProductId""Product Id"),  
  63.                  webGrid.Column("Product.ProductName""Product Name"),  
  64.                  webGrid.Column("Product.Price""Price"),  
  65.                  webGrid.Column("Product.ReorderLevel""Reorder Level"),  
  66.                    webGrid.Column("Product.Category.CategoryName""Category"),  
  67.   
  68.   webGrid.Column(header: "Action", format: @<text>@Html.ActionLink("Select"nullnullnew { @onclick = "return GetSelectedRow(this);" })</text>)  
  69.                  ))  
  70.         <script type="text/javascript">  
  71.             function GetSelectedRow(link) {  
  72.                 var row = link.parentNode.parentNode;  
  73.                 var message = "Selected Row:";  
  74.                 message += "\n\n Product Id: " + row.getElementsByTagName("TD")[0].innerHTML;  
  75.                 message += "\n Product Name: " + row.getElementsByTagName("TD")[1].innerHTML;  
  76.                 message += "\n Price: " + row.getElementsByTagName("TD")[2].innerHTML;  
  77.                 message += "\n ReorderLevel: " + row.getElementsByTagName("TD")[3].innerHTML;  
  78.                 message += "\n Category Name: " + row.getElementsByTagName("TD")[4].innerHTML;  
  79.                 alert(message);  
  80.                 return false;  
  81.             }  
  82.         </script>  
  83.     </center>  
  84. </body>  
  85. </html>  

 ASP.NET MVC 5 Webgrid Displaying Data From Two Tables (Models) With Pagination With Entity Framework Database First Method

Then build the project and run it.

Index webpage opens with data from the Category Table and Product Table as displayed below. After clicking the "Select" link, the record is displayed in apopupp window.

ASP.NET MVC 5 Webgrid Displaying Data From Two Tables (Models) With Pagination With Entity Framework Database First Method