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 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

 

 

 

Choose Project type as MVC, as shown below.

 

The project is created and opens as shown below.

 

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.

 

 

 

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

 

 

CREATE TABLE [dbo].[Category] (
    [CategoryId]   INT           NOT NULL,
    [CategoryName] NVARCHAR (30) NOT NULL,
    [Remark]       NVARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([CategoryId] ASC)
);

INSERT INTO [dbo].[Category] ([CategoryId], [CategoryName], [Remark]) VALUES (1, N'Computer', N'Desktop & Laptopd')
INSERT INTO [dbo].[Category] ([CategoryId], [CategoryName], [Remark]) VALUES (2, N'Storage', N'Pendrive & HardDisk')
INSERT INTO [dbo].[Category] ([CategoryId], [CategoryName], [Remark]) VALUES (3, N'Acssories', N'Mouse etc')

CREATE TABLE [dbo].[Product] (
    [ProductId]    INT           NOT NULL,
    [ProductName]  NVARCHAR (30) NOT NULL,
    [Price]        DECIMAL (18)  NOT NULL,
    [ReorderLevel] INT           NOT NULL,
    [CatId]        INT           NOT NULL,
    PRIMARY KEY CLUSTERED ([ProductId] ASC),
    CONSTRAINT [fk_cat_product_id] FOREIGN KEY ([CatId]) REFERENCES [dbo].[Category] ([CategoryId]) ON DELETE CASCADE
);

INSERT INTO [dbo].[Product] ([ProductId], [ProductName], [Price], [ReorderLevel], [CatId]) VALUES (1, N'Lenovo5000', CAST(25500 AS Decimal(18, 0)), 24, 1)
INSERT INTO [dbo].[Product] ([ProductId], [ProductName], [Price], [ReorderLevel], [CatId]) VALUES (2, N'SanDisk Penrive', CAST(250 AS Decimal(18, 0)), 300, 2)
INSERT INTO [dbo].[Product] ([ProductId], [ProductName], [Price], [ReorderLevel], [CatId]) VALUES (3, N'sonyharddisk', CAST(3000 AS Decimal(18, 0)), 31, 2)
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

 

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,

 

Similarly create Product table and insert Product records.

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

 

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

Right Click on Models Folder - Add - New Item

 

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

 

 

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

 

 

Modify the ProductWithCategory class like below,

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace ShoppingCart.Models
{
    public class ProductWithCategory
    {

        public ProductWithCategory()
        {

        }
        public ProductWithCategory(Product p,Category c)
        {
            //TO DO: Complete Member Initialization
            this.Product = p;
            this.Category = c;
        }
        public Product Product { get; set; }
        public Category Category { get; set; }
    }
}

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

 

Modify the Index() Method like below,

Using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using ShoppingCart.Models;

namespace ShoppingCart.Controllers
{
    public class HomeController : Controller
    {
        DBContext db = new DBContext();
        public ActionResult Index()
        {
            var query = from p in db.Products
                                 join c in db.Categories on p.CatId equals c.CategoryId

                                 select new ProductWithCategory{ Product=p, Category=c };
            var model = query.ToList();
            return View(model);
        }

        public ActionResult About()
        {
            ViewBag.Message = "Your application description page.";

            return View();
        }

        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";

            return View();
        }
    }
}

Connection String in Web.Config File is,

<connectionStrings>
    <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" />
</connectionStrings>

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

 

Replace Code in Index.cshtml with Code below

@model IEnumerable<ShoppingCart.Models.ProductWithCategory>

@{
    ViewBag.Title = "Index";
    Layout = null;

    WebGrid webGrid = new WebGrid(Model, canPage: true, rowsPerPage: 2,
    selectionFieldName: "selectedRow", ajaxUpdateContainerId: "gridContent");
    webGrid.Pager(WebGridPagerModes.All);

}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <style type="text/css">
        body {
            font-family: Arial;
            font-size: 10pt;
        }

        .Grid {
            border: 1px solid #ccc;
            border-collapse: collapse;
        }

            .Grid th {
                background-color: #F7F7F7;
                font-weight: bold;
            }

            .Grid th, .Grid td {
                padding: 5px;
                border: 1px solid #ccc;
            }

            .Grid, .Grid table td {
                border: 0px solid #ccc;
            }

                .Grid th a, .Grid th a:visited {
                    color: #333;
                }
    </style>
</head>
<body>

    <center>

        <h1>Products</h1>

        <p style="align-items:initial;">
            @Html.ActionLink("Create New", "Create")
        </p>

        @webGrid.GetHtml(
        htmlAttributes: new { @id = "WebGrid", @class = "Grid" },
        columns: webGrid.Columns(
                 webGrid.Column("Product.ProductId", "Product Id"),
                 webGrid.Column("Product.ProductName", "Product Name"),
                 webGrid.Column("Product.Price", "Price"),
                 webGrid.Column("Product.ReorderLevel", "Reorder Level"),
                   webGrid.Column("Product.Category.CategoryName", "Category"),

  webGrid.Column(header: "Action", format: @<text>@Html.ActionLink("Select", null, null, new { @onclick = "return GetSelectedRow(this);" })</text>)
                 ))
        <script type="text/javascript">
            function GetSelectedRow(link) {
                var row = link.parentNode.parentNode;
                var message = "Selected Row:";
                message += "\n\n Product Id: " + row.getElementsByTagName("TD")[0].innerHTML;
                message += "\n Product Name: " + row.getElementsByTagName("TD")[1].innerHTML;
                message += "\n Price: " + row.getElementsByTagName("TD")[2].innerHTML;
                message += "\n ReorderLevel: " + row.getElementsByTagName("TD")[3].innerHTML;
                message += "\n Category Name: " + row.getElementsByTagName("TD")[4].innerHTML;
                alert(message);
                return false;
            }
        </script>
    </center>
</body>
</html>

 

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.