Blog

Using CRUD Operations with jQuery igGrid, Entity Framework and ASP.Net MVC3

[Infragistics] Mihail Mateev / Sunday, September 25, 2011

The NetAdvantage for jQuery product contains a bunch of client-side components. Probably the most used one is jQuery igGrid. This component comes with an ASP.NET MVC Html Helper. The Helper makes it easy to new up the grid within your MVC views connecting to data form your model. Detailed information how to use NetAdvantage jQuery grid with ASP.Net MVC3 you could find here. In this blog, you will learn how to perform operations CRUD using NetAdvantage jQuery igGrid, Entity Framework and MVC3. The acronym CRUD (Create, Read, Update and Delete) refers to all of the major functions that need to be implemented in a relational database application. In the sample application you will see how to create MVC3 ASP.Net application with Database First and Entity Framework, add jQuery igGrid and configure it to support CRUD operations.

Requirements:

Steps to reproduce the sample application:

  • Create the MVC3 Application
  • Create an Entity Data Model from a Northwind database to represent the entities for customers and products.
  • Generate classes from the model using the ADO.NET DbContext Generator template
  • Build a simple ASP.NET MVC 3 application that propose controllers and views
  • Implement controllers to list and edit customers and products.
  • Implement views to list customers and products using NetAdvantage jQuery Grid.

Create a new MVC3 Application.

Confirm that you want to create an Internet Application and select Razor as view engine.

Add new ADO.NET Entity Data Model and name it "Northwind".

Select from Northwind database tables "Customers" and "Products"

 Generate classes from the model using the ADO.NET DbContext Generator template

Setting up the project

Add reference to Infragistics.Web.Mvc assembly:

When you create a new ASP.NET MVC 3 project it comes with Scripts and a Content folders where all the default JavaScript files and CSS contents are located respectively.

You should  use the same folder and insert the Infragistics Scripts & CSS from NetAdvantage for jQuery install folder.

In the NorthwindEntities class add two static methods: GetCustomerList() and GetProductList().

   1: using System.Linq;
   2:  
   3: namespace IgGridMvcCRUD.Models
   4: {
   5:     using System;
   6:     using System.Data.Entity;
   7:     using System.Data.Entity.Infrastructure;
   8:     
   9:     public partial class NorthwindEntities : DbContext
  10:     {
  11:         public NorthwindEntities()
  12:             : base("name=NorthwindEntities")
  13:         {
  14:         }
  15:     
  16:         protected override void OnModelCreating(DbModelBuilder modelBuilder)
  17:         {
  18:             throw new UnintentionalCodeFirstException();
  19:         }
  20:     
  21:         public DbSet<Customer> Customers { get; set; }
  22:         public DbSet<Product> Products { get; set; }
  23:  
  24:         public static IOrderedQueryable<Customer> GetCustomerList()
  25:         {
  26:             var northWindEntities = new NorthwindEntities();
  27:  
  28:             var customers = from c in northWindEntities.Customers
  29:                             orderby c.CustomerID
  30:                             select c;
  31:  
  32:             return customers;
  33:         }
  34:  
  35:         public static IOrderedQueryable<Product> GetProductList()
  36:         {
  37:             var northWindEntities2 = new NorthwindEntities();
  38:  
  39:             var products = from c in northWindEntities2.Products
  40:                            orderby c.ProductID
  41:                            select c;
  42:             return products;
  43:         }
  44:     }
  45: }

 

Add new controller, named NorthwindController

Add in the controller the code below:

   1: using System;
   2: using System.Collections.Generic;
   3: using System.Linq;
   4: using System.Web;
   5: using System.Web.Mvc;
   6: using IgGridMvcCRUD.Models;
   7: using Infragistics.Web.Mvc;
   8:  
   9:  
  10: namespace IgGridMvcCRUD.Controllers
  11: {
  12:     public class NorthwindController : Controller
  13:     {
  14:         //
  15:         // GET: /Northwind/
  16:  
  17:         [GridDataSourceAction]
  18:         public ActionResult CustomersList()
  19:         {
  20:             return View(NorthwindEntities.GetCustomerList());
  21:         }
  22:  
  23:  
  24:         [GridDataSourceAction]
  25:         public ActionResult ProductList()
  26:         {
  27:             return View(NorthwindEntities.GetProductList());
  28:         }
  29:  
  30:  
  31:         public ActionResult EditingCustomers()
  32:         {
  33:             var ctx = new NorthwindEntities();
  34:             var ds = ctx.Customers;
  35:  
  36:  
  37:             ViewData["GenerateCompactJSONResponse"] = false;
  38:             GridModel m = new GridModel();
  39:  
  40:             List<Transaction<Customer>> transactions = m.LoadTransactions<Customer>(HttpContext.Request.Form["ig_transactions"]);
  41:  
  42:             foreach (Transaction<Customer> t in transactions)
  43:             {
  44:                 switch (t.type)
  45:                 {
  46:                     case "row":
  47:  
  48:                         string x = t.rowId;
  49:                         var customer = (from p in ctx.Customers where p.CustomerID == x select p).Single();
  50:  
  51:  
  52:                         if (t.row.CompanyName != null)
  53:                         {
  54:                             customer.CompanyName = t.row.CompanyName;
  55:                         }
  56:                         if (t.row.ContactTitle != null)
  57:                         {
  58:  
  59:                             customer.ContactTitle = t.row.ContactTitle;
  60:                         }
  61:                         if (t.row.ContactName != null)
  62:                         {
  63:  
  64:                             customer.ContactName = t.row.ContactName;
  65:                         }
  66:  
  67:                         if (t.row.Country != null)
  68:                         {
  69:  
  70:                             customer.Country = t.row.Country;
  71:                         }
  72:  
  73:  
  74:                         break;
  75:                     case "newrow":
  76:  
  77:                         var newCustomer = new Customer
  78:                         {
  79:                             CompanyName = t.row.CompanyName,
  80:                             ContactTitle = t.row.ContactTitle,
  81:                             ContactName = t.row.ContactName,
  82:                             Country = t.row.Country,
  83:  
  84:                         };
  85:  
  86:                         ctx.Customers.Add(newCustomer);
  87:  
  88:  
  89:                         break;
  90:                     case "deleterow":
  91:                         string id = t.rowId;
  92:                         var deleteProduct = (from p in ctx.Products where p.ProductName == id select p).Single();
  93:                         if (deleteProduct != null)
  94:                         {
  95:                             ctx.Products.Add(deleteProduct);
  96:                         }
  97:  
  98:                         break;
  99:                 }
 100:  
 101:             }
 102:  
 103:  
 104:             ctx.SaveChanges();
 105:             return RedirectToAction("CustomersList", ds);
 106:         }
 107:  
 108:         public ActionResult EditingSaveChanges()
 109:         {
 110:  
 111:             var ctx = new NorthwindEntities();
 112:             var ds = ctx.Products;
 113:  
 114:  
 115:             ViewData["GenerateCompactJSONResponse"] = false;
 116:             GridModel m = new GridModel();
 117:  
 118:  
 119:  
 120:             List<Transaction<Product>> transactions = m.LoadTransactions<Product>(HttpContext.Request.Form["ig_transactions"]);
 121:  
 122:             foreach (Transaction<Product> t in transactions)
 123:             {
 124:                 switch (t.type)
 125:                 {
 126:                     case "row":
 127:  
 128:                         string x = t.rowId;
 129:                         var product = (from p in ctx.Products where p.ProductName == x select p).Single();
 130:  
 131:  
 132:                         if (t.row.ProductName != null)
 133:                         {
 134:                             product.ProductName = t.row.ProductName;
 135:                         }
 136:                         if (t.row.QuantityPerUnit != null)
 137:                         {
 138:  
 139:                             product.QuantityPerUnit = t.row.QuantityPerUnit;
 140:                         }
 141:                         if (t.row.UnitPrice != null)
 142:                         {
 143:  
 144:                             product.UnitPrice = t.row.UnitPrice;
 145:                         }
 146:  
 147:                         if (t.row.UnitsInStock != null)
 148:                         {
 149:  
 150:                             product.UnitsInStock = t.row.UnitsInStock;
 151:                         }
 152:  
 153:                         if (t.row.UnitsOnOrder != null)
 154:                         {
 155:  
 156:                             product.UnitsOnOrder = t.row.UnitsOnOrder;
 157:                         }
 158:  
 159:                         break;
 160:                     case "newrow":
 161:  
 162:                         var newProduct = new Product
 163:                         {
 164:                             ProductID = t.row.ProductID,
 165:                             ProductName = t.row.ProductName,
 166:                             QuantityPerUnit = t.row.QuantityPerUnit,
 167:                             UnitPrice = t.row.UnitPrice,
 168:                             UnitsInStock = t.row.UnitsInStock
 169:                         };
 170:  
 171:                         var productCheck = (from p in ctx.Products where p.ProductName.Equals(newProduct.ProductName) select p);
 172:  
 173:                         if ((productCheck.Count() == 0))
 174:                         {
 175:                             ctx.Products.Add(newProduct);
 176:                         }
 177:  
 178:                         break;
 179:                     case "deleterow":
 180:                         string id = t.rowId;
 181:                         var deleteProduct = (from p in ctx.Products where p.ProductName == id select p).Single();
 182:                         if (deleteProduct != null)
 183:                         {
 184:                             ctx.Products.Add(deleteProduct);
 185:                         }
 186:  
 187:                         break;
 188:                 }
 189:             }
 190:  
 191:             ctx.SaveChanges();
 192:             return RedirectToAction("ProductList", ds);
 193:         }
 194:  
 195:     }
 196: }

 

Check in the _ViewStart.cshtml that the default view is "~/Views/Shared/_Layout.cshtml";

Replace the content of the _Layout.cshtml with the code below. This view contains all script references. You have no need to add these references in each one of views.

   1: <!DOCTYPE html>
   2: <html>
   3: <head>
   4:     <meta charset="utf-8" />
   5:     <title>@ViewBag.Title</title>
   6:     <link href="@Url.Content("~/Content/Site.css")" rel="stylesheet" type="text/css" />
   7:     <link href="@Url.Content("~/Content/IGStyles/ig/jquery.ui.custom.css")" rel="stylesheet" type="text/css" />
   8:     <link href="@Url.Content("~/Content/IGStyles/base/ig.ui.grid.css")" rel="stylesheet" type="text/css" />
   9:     <link href="@Url.Content("~/Content/IGStyles/base/ig.ui.editors.css")" rel="stylesheet" type="text/css" />
  10:     @*<script src="@Url.Content("~/Scripts/jquery-1.4.4.min.js")" type="text/javascript"></script>
   1: *@
   2:     <script src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.4.4.min.js" type="text/javascript">
   1: </script>
   2:     <script type="text/javascript" src="http://ajax.microsoft.com/ajax/jquery.templates/beta1/jquery.tmpl.js">
   1: </script>  
   2:     <script src="@Url.Content("~/Scripts/jquery-ui-1.8.11.min.js")" type="text/javascript">
   1: </script>
   2:     <script src="@Url.Content("~/Scripts/modernizr-1.7.min.js")" type="text/javascript">
   1: </script>
   2:     <script src="@Url.Content("~/Scripts/IG/ig.ui.min.js")" type="text/javascript">
</script>
  11: </head>
  12:  
  13:     <body>
  14:     <div class="page">
  15:         <header>
  16:             <div id="title">
  17:                 <h1>MVC 3 NetAdvantage jQuery Grid CRUD Sample</h1>
  18:             </div>
  19:             <div id="logindisplay">
  20:                 @Html.Partial("_LogOnPartial")
  21:             </div>
  22:             <nav>
  23:                 <ul id="menu">
  24:                     <li>@Html.ActionLink("Customers", "CustomersList", "Northwind")</li>
  25:                     <li>@Html.ActionLink("Products", "ProductList", "Northwind")</li>
  26:                     <li>@Html.ActionLink("Home", "Index", "Home")</li>
  27:                     <li>@Html.ActionLink("About", "About", "Home")</li>
  28:                 </ul>
  29:             </nav>
  30:         </header>
  31:         <section id="main">
  32:             @RenderBody()
  33:         </section>
  34:         <footer>
  35:         </footer>
  36:     </div>
  37:  
  38: </body>
  39: </html>

 

Add code below in the   ProductList view (ProductList.cshtml). The sample shows how to use the MVC helper that generates the jQuery grid in the view. Add jQuery “live” events to support database updates. It is also possible to use igGrid updating “autoCommint” property. With NetAdvantage for jQuery 2011 Vol.1 where editing is still CTP, me recommendation is to update database explicit using the code below.

   1: inherits System.Web.Mvc.WebViewPage<IOrderedQueryable<IgGridMvcCRUD.Models.Customer>>
   2: @using Infragistics.Web.Mvc;
   3:  
   4: <!DOCTYPE html>
   5:  
   6: <html>
   7:  
   8: <head>
   9:     <title>Customers</title>
  10:  
  11:     <script type="text/javascript">
   1:  
   2:         function saveChanges2() {
   3:             $("#grid3").igGrid("saveChanges");
   4:             return false;
   5:         }
   6:         $('#grid3').live('iggridupdatingrowadded', function () { $("#grid3").igGrid("saveChanges") });
   7:  
   8:  
   9:         $('#grid3').live('iggridupdatingeditrowended', function (event, ui) {
  10:             if (ui.rowAdding == false) {
  11:                 $("#grid3").igGrid("saveChanges");
  12:             }
  13:         });
  14:  
  15:         $('#grid3').live('iggridupdatingrowdeleted', function () { $("#grid3").igGrid("saveChanges") });
  16:     
</script>
  12: </head>
  13: <body>
  14:     <div>
  15:     <p>Customers</p>
  16:  
  17:      @(Html.Infragistics().Grid(Model).ID("grid3").UpdateUrl("EditingCustomers").PrimaryKey("CustomerID")
  18:             .AutoGenerateColumns(true)
  19:             .Columns(column =>
  20:             {
  21:                 column.For(x => x.CustomerID).DataType("string").HeaderText("Customer ID");
  22:                 column.For(x => x.CompanyName).DataType("string").HeaderText("Company Name");
  23:                 column.For(x => x.ContactTitle).DataType("string").HeaderText("Contact Title");
  24:                 column.For(x => x.ContactName).DataType("string").HeaderText("Contact Name");
  25:                 column.For(x => x.Country).DataType("string").HeaderText("Country");
  26:             })
  27:             .Features(features =>
  28:             {
  29:                 features.Paging().VisiblePageCount(5).ShowPageSizeDropDown(false).PageSize(10).PrevPageLabelText("Previous").NextPageLabelText("Next");
  30:                 features.Sorting().Mode(SortingMode.Single).ColumnSettings(settings =>
  31:                 {
  32:                     settings.ColumnSetting().ColumnKey("CustomerID").AllowSorting(true);
  33:  
  34:                 });
  35:                 features.Selection().MouseDragSelect(true).MultipleSelection(true).Mode(SelectionMode.Row);
  36:                 features.Selection().MouseDragSelect(true).MultipleSelection(true).Mode(SelectionMode.Cell);
  37:                 features.Updating(); 
  38:             })
  39:                     .DataSourceUrl(Url.Action("CustomersList"))            
  40:             .Width("100%")
  41:             .Height("350px")
  42:             .DataBind()
  43:             .Render()       
  44:         )
  45:      <br />
  46:      <br />
  47:      <Input type="button" onclick="saveChanges2(); return false;">Save Changes </Input>
  48:     </div>
  49: </body>
  50: </html>

 

 

 

Add code below in the CustomersList view (CustomersList.cshtml)

   1: @inherits System.Web.Mvc.WebViewPage<IOrderedQueryable<IgGridMvcCRUD.Models.Product>>
   2: @using Infragistics.Web.Mvc;
   3:  
   4: <!DOCTYPE html>
   5:  
   6: <html>
   7:  
   8: <head>
   9:     <title>Products</title>
  10:  
  11:     <script type="text/javascript">
   1:  
   2:         function saveChanges2() {
   3:             $("#grid2").igGrid("saveChanges");
   4:             return false;
   5:             }
   6:             $('#grid2').live('iggridupdatingrowadded', function () { $("#grid2").igGrid("saveChanges") });
   7:  
   8:             $('#grid2').live('iggridupdatingeditrowended', function (event, ui) {
   9:                 if (ui.rowAdding == false) {
  10:                     $("#grid2").igGrid("saveChanges");
  11:                 }
  12:             });
  13:  
  14:             $('#grid2').live('iggridupdatingrowdeleted', function () { $("#grid2").igGrid("saveChanges") });
  15:     
</script>
  12: </head>
  13: <body>
  14:     <div>
  15:     <p>Product List</p>
  16:  
  17:        @( Html.Infragistics().Grid(Model).ID("grid2").UpdateUrl("EditingSaveChanges").PrimaryKey("ProductName").AutoGenerateColumns(true)
  18:         .Columns(column =>
  19:         {
  20:             column.For(x => x.ProductID).DataType("int").HeaderText("Product ID");
  21:             column.For(x => x.ProductName).DataType("string").HeaderText("Product Name");
  22:             column.For(x => x.QuantityPerUnit).DataType("string").HeaderText("Quantity Per Unit");
  23:             column.For(x => x.UnitPrice).DataType("string").HeaderText("Unit Price");
  24:             column.For(x => x.UnitsInStock).DataType("int").HeaderText("Units In Stock");
  25:             column.For(x => x.UnitsOnOrder).DataType("int").HeaderText("Units In Order");
  26:         })
  27:         .Features(features =>
  28:         {
  29:             features.Paging().VisiblePageCount(5).ShowPageSizeDropDown(false).PageSize(10).PrevPageLabelText("Previous").NextPageLabelText("Next");
  30:             features.Sorting().Mode(SortingMode.Single).ColumnSettings(settings =>
  31:             {
  32:                 settings.ColumnSetting().ColumnKey("ProductName").AllowSorting(true);
  33:  
  34:             });
  35:              features.Filtering().ColumnSettings(settings =>{
  36:                  settings.ColumnSetting().ColumnKey("ProductID").AllowFiltering(false).FilterCondition("equals");
  37:                  settings.ColumnSetting().ColumnKey("ProductName").AllowFiltering(true).FilterCondition("startsWith");    
  38:              });
  39:             features.Selection().MouseDragSelect(true).MultipleSelection(true).Mode(SelectionMode.Row);
  40:             features.Selection().MouseDragSelect(true).MultipleSelection(true).Mode(SelectionMode.Cell);
  41:             features.Updating(); 
  42:         })
  43:         .DataSourceUrl(Url.Action("ProductList"))      
  44:         .Width("100%")
  45:         .Height("350px")
  46:         .DataBind()
  47:         .Render()  
  48:     )
  49:     <br />
  50:     <br />
  51:      <Input type="button" onclick="saveChanges2(); return false;">Save Changes </Input>
  52:     </div>
  53: </body>
  54: </html>    
  55: 

 

 

Run the application. Select “Customers” link – jQuery igGrid appears with information about Customers.

Grid supports data editing : edit record, add new record, delete record

Select “Products” link and add a new record. Add random data.

Go to the last record. New data is available at the end of the table.

Open Microsoft SQL Management Studio and list records in Northwind –> Products table. The new added record is available in the table.