ASP.NET MVC WebGrid With CRUD Record From Two Tables And Custom Search Functionality Using Entity Framework Database First Method

This simple tutorial explains how to CRUD (Create, Retrieve, Update, and Delete) Records from two one-to-many linked tables (Category and Product) and display in a web grid. Data can be searched with multiple search facility, search by id or search by part of name. The Models are created using Entity Framework Database Method.

Software Required

Visual Studio 2013 or above
Entity Framework 6.0 (Available with Visual Studio)
.NET Framework 4.5 or above (Available with Visual Studio)
SQL Server Database (Available with Visual Studio)

Start Visual Studio and create a new MVC Project as displayed below.

Go to File >> New >> Project. Name the project as ShoppingCart.

ASP.NET MVC WebGrid With CRUD Record From Two Tables And Custom Search Functionality Using Entity Framework Database First Method

Choose project type as MVC, as shown below.

The project is created and opens.

Creating Database File Store.mdf

Right-click on App_Data folder and got to Add >> New Item >> SQL Server Database.

Name it as Store.mdf as displayed below. The 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')
INSERT INTO [dbo].[Category] ([CategoryId], [CategoryName], [Remark]) VALUES (4, N'Digital Camera', N'Digital Camera')
INSERT INTO [dbo].[Category] ([CategoryId], [CategoryName], [Remark]) VALUES (5, N'Camera Aceesories', N'Camera Acessories')
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)
INSERT INTO [dbo].[Product] ([ProductId], [ProductName], [Price], [ReorderLevel], [CatId]) VALUES (5, N'Sony PenDrive 16GB', CAST(470 AS Decimal(18, 0)), 45, 2)
INSERT INTO [dbo].[Product] ([ProductId], [ProductName], [Price], [ReorderLevel], [CatId]) VALUES (6, N'Zebronics Mouse', CAST(300 AS Decimal(18, 0)), 54, 3)
INSERT INTO [dbo].[Product] ([ProductId], [ProductName], [Price], [ReorderLevel], [CatId]) VALUES (7, N'Sony Camera Stand', CAST(2500 AS Decimal(18, 0)), 22, 5)
INSERT INTO [dbo].[Product] ([ProductId], [ProductName], [Price], [ReorderLevel], [CatId]) VALUES (8, N'Sony Camera Lens', CAST(65000 AS Decimal(18, 0)), 12, 5)
INSERT INTO [dbo].[Product] ([ProductId], [ProductName], [Price], [ReorderLevel], [CatId]) VALUES (9, N'Canon Camera Lens', CAST(30000 AS Decimal(18, 0)), 20, 5)

Right-click on Tables >> New Query.

Copy and paste the SQL query for creating the Category table as above and execute it by clicking the arrow symbol on the left. The Category table would be created.

Insert the records by executing the INSERT query as displayed below.

Similarly, create the Product table and insert Product records.

Refresh the Tables folder in the Server Explorer window. The newly created 2 tables will be seen.

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

Please click here to go to my article describing how to create models from database tables using the Database-First method.

Code for the CategoryModel Class is given below.

namespace ShoppingCart.Models
{
using System;
using System.Collections.Generic;
public partial class Category
{
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
public Category()
{
this.Products = new HashSet<Product>();
}
public int CategoryId { get; set; }
public string CategoryName { get; set; }
public string Remark { get; set; }
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<Product> Products { get; set; }
}
}

Code for the Product Model class is mentioned below.

namespace ShoppingCart.Models
{
using System;
using System.Collections.Generic;
public partial class Product
{
public int ProductId { get; set; }
public string ProductName { get; set; }
public decimal Price { get; set; }
public int ReorderLevel { get; set; }
public int CatId { get; set; }
public virtual Category Category { get; set; }
}
}

Creating View Model Class (ProductWithCategoryVM)

This class will be created by combining the properties from Category class and Product class respectively. It will be used in the Controller and to create View also.

Right-click on the Models folder and click Add >> Class.

Name it as ProductWithCategoryVM.

Code for the ProductWithCategory View Model Class is as below.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;
namespace ShoppingCart.Models
{
public class ProductWithCategoryVM
{
public int ProductId { get; set; }
public string ProductName { get; set; }
public decimal Price { get; set; }
public int ReorderLevel { get; set; }
public string CategoryName { get; set; }
public Product Product { get; set; }
}
}

Code for the Home Controller class is given below.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using System.Data.Entity;
using System.Net;
using ShoppingCart.Models;
namespace ShoppingCart.Controllers
{
public class HomeController : Controller
{
DBContext db = new DBContext();
public ActionResult Products(string q, string S)
{
//Populating Department DropDownList in View
ViewBag.Categories = new SelectList(db.Categories, "CategoryId", "CategoryName");
//Searching Record
int id = Convert.ToInt32(Request["SearchType"]);
var searchParameter = "Searching";
var ProductWithCategoryVMlist = (from P in db.Products
join C in db.Categories on
P.CatId equals C.CategoryId
select new ProductWithCategoryVM
{
ProductId = P.ProductId,
ProductName = P.ProductName,
Price = P.Price,
ReorderLevel = P.ReorderLevel,
CategoryName = P.Category.CategoryName
});
if (!string.IsNullOrWhiteSpace(q))
{
switch (id)
{
case 0:
int iQ = int.Parse(q);
ProductWithCategoryVMlist = ProductWithCategoryVMlist.Where(p => p.ProductId.Equals(iQ));
searchParameter += " ProductId for ' " + q + " '";
break;
case 1:
ProductWithCategoryVMlist = ProductWithCategoryVMlist.Where(p => p.ProductName.Contains(q));
searchParameter += " Product Name for ' " + q + " '";
break;
case 2:
ProductWithCategoryVMlist = ProductWithCategoryVMlist.Where(p => p.CategoryName.Contains(q));
searchParameter += " Category Name for '" + q + "'";
break;
}
}
else
{
searchParameter += "ALL";
}
ViewBag.SearchParameter = searchParameter;
return View(ProductWithCategoryVMlist.ToList()); //List of Products with Category Name)
//return View(ProductWithCategoryVMlist); //List of Products with Category Name)
}
public ActionResult About()
{
ViewBag.Message = "Your application description page.";
return View();
}
[HttpPost]
public ActionResult Insert()
{
try
{
string ProductName = Request["txtPName"].ToString();
int CatId = Convert.ToInt32(Request["Categories"].ToString());
decimal price = Convert.ToDecimal(Request["txtPrice"].ToString());
int ReorderLevel = Convert.ToInt32(Request["txtReorderLevel"].ToString());
int NextId = db.Products.Max(p => (int)p.ProductId) + 1;
Product NewProduct = new Product();
NewProduct.ProductId = NextId;
NewProduct.ProductName = ProductName;
NewProduct.CatId = CatId;
NewProduct.Price = price;
NewProduct.ReorderLevel = ReorderLevel;
db.Products.Add(NewProduct);
db.SaveChanges();
//Sending Data To Products Controller.
TempData["Message"] = "Record saved successfully";
}
catch
{
TempData["Message"] = "Error while saving record";
}
return RedirectToAction("Products");
}
// GET: /Product/Edit/5
public ActionResult Edit(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Product editProduct = db.Products.Find(id);
//ViewBag.CategoryListItems = db.Categories.Distinct().Select(i => new SelectListItem() { Text = i.CategoryName, Value = i.CategoryId.ToString() }).ToList();
//Populating Department DropDownList in View
ViewBag.Categories = new SelectList(db.Categories, "CategoryId", "CategoryName");
if (editProduct == null)
{
return HttpNotFound();
}
return View(editProduct);
}
// POST: /Product/Edit/5
// To protect from overposting attacks, please enable the specific properties you want to bind to, for
// more details see http://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit([Bind(Include = "ProductId,ProductName,CatId,Price,ReorderLevel")] Product editProduct)
{
try
{
if (ModelState.IsValid)
{
int CatId = Convert.ToInt32(Request["Categories"].ToString());
editProduct.CatId = CatId;
db.Entry(editProduct).State = EntityState.Modified;
db.SaveChanges();
editProduct = null;
TempData["Message"] = "Record updated successfully";
return RedirectToAction("Products");
}
}
catch
{
TempData["Message"] = "Error while updating record";
}
return RedirectToAction("Products");
}
// GET: /Product/Delete/5
public ActionResult Delete(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Product deleteProduct = db.Products.Find(id);
if (deleteProduct == null)
{
return HttpNotFound();
}
return View(deleteProduct);
}
// POST: /Product/Delete/5
[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public ActionResult DeleteConfirmed(int id)
{
try
{
Product deleteProduct = db.Products.Find(id);
db.Products.Remove(deleteProduct);
db.SaveChanges();
deleteProduct = null;
TempData["Message"] = "Record Deleted successfully";
return RedirectToAction("Products");
}
catch
{
TempData["Message"] = "Error while deleting record";
}
return RedirectToAction("Products");
}
public ActionResult Contact()
{
ViewBag.Message = "Your contact page.";
return View();
}
}
}

This is the code for the View Products.cshtml page.

@model List<ShoppingCart.Models.ProductWithCategoryVM>
@{
ViewBag.Title = "Product List";
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
<style type="text/css">
.webGrid {
margin: 4px;
border-collapse: collapse;
width: 700px;
font-family: Tahoma;
font-size: small;
}
.grid-header {
background-color: #990000;
font-weight: bold;
color: White !important;
}
.webGrid th a {
color: White;
text-decoration: none;
}
.webGrid th, .webGrid td {
border: 1px solid black;
padding: 5px;
}
.alt {
background-color: #F4EFEF;
}
.webGrid th a:hover {
text-decoration: underline;
}
.to-the-right {
text-align: right;
}
</style>
</head>
<body>
<center>
<h2>Custom Search for Product</h2>
@using (Html.BeginForm("Products", "Home", FormMethod.Get, new { @class = "Search-form" }))
{
<div id="txtBox">
@Html.Label("Search Products ")
<input type="text" name="q" />
</div>
<div id="radioList">
@Html.RadioButton("SearchType", "0")
@Html.Label("ID")
@Html.RadioButton("SearchType", "1", true)
@Html.Label("Product Name")
@Html.RadioButton("SearchType", "2")
@Html.Label("Category Name")
</div>
<div id="btnSearch">
<input type="submit" value="Search" id="btnSubmit" />
</div>
}
<br />
<div id="DivGrid">
@{
var grid = new WebGrid(source: Model, canPage: true, rowsPerPage: 2, defaultSort: "ProductId");
if (Model.Count() > 0)
{
<div><strong> @ViewBag.SearchParameter</strong> | @grid.TotalRowCount @Html.Label("Record(s) found")</div>
@grid.GetHtml(
tableStyle: "webGrid",
headerStyle: "grid-header",
rowStyle: "gridRow",
alternatingRowStyle: "alt",
mode: WebGridPagerModes.All,
firstText: "<< First",
previousText: " < Previous",
nextText: "Next >",
lastText: "Last >>",
caption: "Products",
columns: grid.Columns(
grid.Column("ProductId", "Product Id"),
grid.Column("ProductName", "Product Name"),
grid.Column("Price", "Price"),
grid.Column("ReorderLevel", "Reorder Level"),
grid.Column("CategoryName", "Category"),
grid.Column(header: "Action", format: (item) =>
{
var link = Html.ActionLink("Edit", "Edit", new { id = item.ProductId });
return link;
}),
grid.Column(header: "Action", format: (item) =>
{
var link = Html.ActionLink("Delete", "Delete", new { id = item.ProductId });
return link;
}),
grid.Column(header: "Action", format: @<text>@Html.ActionLink("Detail", null, null, new { @onclick = "return GetSelectedRow(this);" })</text>)
))
<script type="text/javascript">
function GetSelectedRow(link) {
var row = link.parentNode.parentNode;
var message = "Selected Product Detail";
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>
}
else
{
<hr />@Html.Label("No, Record(s) not found")<hr />
}
}
</div>
</center>
<div>
@using (Html.BeginForm("Insert", "Home", FormMethod.Post))
{
<center>
<table cellpadding="0" cellspacing="0">
<tr>
<th colspan="2" align="center">Add Product</th>
</tr>
<tr>
<td>@Html.Label("Enter Product Name:") </td>
<td>@Html.TextBox("txtPName")</td>
</tr>
<tr>
<td>@Html.Label("Select Category Type:")</td>
<td>
@Html.DropDownList("Categories", (IEnumerable<SelectListItem>)ViewBag.Categories, "Select Category")
</td>
</tr>
<tr>
<td>@Html.Label("Enter Product Price:")</td>
<td>@Html.TextBox("txtPrice")</td>
</tr>
<tr>
<td>@Html.Label("Enter ReorderLevel:")</td>
<td>@Html.TextBox("txtReorderLevel")</td>
</tr>
<tr >
<td></td>
<td style="text-align: center"><input type="submit" value="Submit" /></td>
</tr>
<tr ><td style="text-align: center;color:Red"><div style="color: Red">@ViewBag.Message</div></td></tr>
</table>
</center>
}
</div>
@if (@TempData["Message"] != null)
{
<script type="text/javascript">
window.onload = function () {
alert("@TempData["Message"]");
};
</script>
}
</body>
</html>

Create two new Views by right clicking the Home folder under Views folder - View.Name then Edit and Delete. Modify them as given below.

Edit View (Edit.cshtml)

@model ShoppingCart.Models.Product
@{
ViewBag.Title = "Edit";
}
<title>Edit</title>
@using (Html.BeginForm("Edit", "Home", FormMethod.Post))
{
@Html.AntiForgeryToken()
<div>
<h3 style="color:forestgreen;text-align:center;width:500px">Edit</h3>
<table width="500px" cellpadding="5" cellspacing="5" border="1" style="border: 1 solid black;
border-collapse: collapse;">
<tr>
<td colspan="2" align="center">Product</td>
<tr>
<td align="right">
Product Id :
</td>
<td align="left">
@Html.DisplayFor(model => model.ProductId)
@Html.Hidden("Id", Model.ProductId)
</td>
</tr>
<tr>
<td align="right">
Product Name :
</td>
<td align="left">
@Html.EditorFor(model => model.ProductName, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.ProductName, "", new { @class = "text-danger" })
@Html.ValidationSummary(true, "", new { @class = "text-danger" })
@*@Html.Hidden("Id", Model.ProductId)*@
@Html.HiddenFor(model => model.ProductId)
</td>
</tr>
<tr>
<td align="right">
Price :
</td>
<td align="left">
@Html.EditorFor(model => model.Price, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Price, "", new { @class = "text- danger" })
</td>
</tr>
<tr>
<td align="right">
Reorder Level:
</td>
<td align="left">
@Html.EditorFor(model => model.ReorderLevel, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.ReorderLevel, "", new { @class = "text-danger" })
</td>
</tr>
<tr>
<td align="right">
Category :
</td>
<td align="left">
@Html.DropDownList("Categories", (IEnumerable<SelectListItem>)ViewBag.Categories, "Select Category")
@Html.ValidationMessageFor(model => model.CatId, "", new { @class = "text- danger" })
</td>
</tr>
</table>
<div style="text-align:center;width:500px" class="form-actions no-color">
<input type="submit" value="Update" class="btn btn-primary" />
|
@Html.ActionLink("Back", "Products", "Home", new { @class = "btn btn-primary" })
@*@Html.ActionLink("Back to List", "Products")*@
</div>
</div>
}

Delete View (Delete.cshtml)

@model ShoppingCart.Models.Product
@{
ViewBag.Title = "Delete";
}
<title>Delete</title>
@using (Html.BeginForm("Delete", "Home", FormMethod.Post))
{
<div>
<h3 style="color:forestgreen;text-align:center;width:500px">Delete</h3>
<h4 style="color:red">Are you sure you want to delete this?Record once deleted can not be retrieved.</h4>
<table width="500px" cellpadding="5" cellspacing="5" border="1" style="border: solid black;
border-collapse: collapse;">
<tr>
<td colspan="2" align="center">Product</td>
</tr>
<tr>
<td align="right">
Product Id :
</td>
<td align="left">
@Html.DisplayFor(model => model.ProductId)
@Html.Hidden("Id", Model.ProductId)
</td>
</tr>
<tr>
<td align="right">
Product Name :
</td>
<td align="left">
@Html.DisplayFor(model => model.ProductName)
@Html.Hidden("Id", Model.ProductId)
</td>
</tr>
<tr>
<td align="right">
Price :
</td>
<td align="left">
@Html.DisplayFor(model => model.Price)
</td>
</tr>
<tr>
<td align="right">
Reorder Level:
</td>
<td align="left">
@Html.DisplayFor(model => model.ReorderLevel)
</td>
</tr>
<tr>
<td align="right">
Category :
</td>
<td align="left">
@Html.DisplayFor(model => model.Category.CategoryName)
</td>
</tr>
</table>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div style="text-align:center;width:500px" class="form-actions no-color">
<input type="submit" value="Delete" class="btn btn-primary" /> |
@*@Html.ActionLink("Back to List", "Products")*@
@Html.ActionLink("Back", "Products", "Home", new { @class = "btn btn-primary" })
</div>
}
</div>
}

Connection String in Web.Config file is as given below.

<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;connect timeout=30;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />
</connectionStrings>

Run the project. The page below is displayed, which contains web grid with data and search by id and name functionality. Add a record functionality there also:

Insert Record

Update Record

Delete Record

3 Comments

Join the conversation! Your thoughts help the community grow.

vel vel6y ago
Super very useful
DAVID POWELL6y ago
I keep getting error: HTTP 404. The resource you are looking for (or one of its dependencies) could have been removed, had its name changed, or is temporarily unavailable
Win Thura Kyaw Kyaw7y ago
How to get datetime value in insert controller ?