CRUD with jqxGrid, ASP.NET MVC3 and SQL
If you haven't already installed ASP.NET MVC 3 use this resource: http://www.asp.net/mvc/mvc3
For this tutorial you're also going to need the Entity Framework: http://www.microsoft.com/download/en/details.aspx?id=18504
For our purpose we will use the Northwind database which you can download from here. So let's begin!
Create new ASP.NET MVC 3 project and choose the "Empty project" option for template. For "View engine" select "Razor".
- You have to load the database. Drag the files: "NORTHWND.LDF" and "NORTHWND.MDF" and drop them over the "App_Data" directory in your project. If there's no "App_Data" folder then right click on the white space in the "Solution Explorer" choose "Add -> Add ASP.NET Folder -> App_Data".
- Add all required JavaScript and CSS files. In the sample we added references to jqx-all.js, jqx.base.css and jqx.classic.css.
- Expand the "View" directory after that the "Shared" and double click on "_Layout.cshtml". Include all the files you've added into the previous steps. If there are older versions of jQuery included, in the "_Layout.cshtml" file, just delete them. After finishing the last step your "_Layout.cshtml" should look like this:
- In the next step we're going to create our Models. Now right click on the "Models" folder. Select "Add -> New Item". Choose "Data" from the tree view in left. Select "ADO.NET Entity Data Model" and click "Add".
In the "Choose Model Contents" section select "Generate from database" and click Next.
In the "Choose Your Data Connection" section click next. The the next section ("Choose Your Database Objects") check the "Tables" and "Stored Procedures" checkboxes and click "Finish".
- For our purpose we are going to use the "Employees" table. To add entity objects and DbContext you have to expand the Models directory. Double click on "Model1.edmx". In the diagram appeared, right click on the white space and choose "Add Code Generation Item". In the tree view in left, select "Code", choose "ADO.NET DbContext Generator" and click "Add".
- After that press F6 to Build your project.
- Now we are ready to add our Controller. Right click on the "Controller" folder and after that choose "Add -> Controller". Rename it "EmployeesController". The choosen template should be "Controller with read/write actions and views, using Entity Framework". For Model class select "Customer (Project.Models)" and for Data context class "NORTHWNDEntities2 (Project.Models)" after that choose "Add".
- After the generation of the controller have been completed go to the "Controllers" folder and double click on "EmployeesController.cs". Add the following code:
using System;using System.Collections.Generic;using System.Data;using System.Data.Entity;using System.Linq;using System.Web;using System.Web.Mvc;using Project.Models;namespace Project.Controllers{ public class EmployeesController : Controller { private NORTHWNDEntities2 db = new NORTHWNDEntities2(); // // GET: /Employees/ public ViewResult Index() { var employees = db.Employees.Include(e => e.Employee1); return View(employees.ToList()); } public JsonResult GetEmployees() { var dbResult = db.Employees.ToList(); var employees = from e in dbResult select new { e.EmployeeID, e.FirstName, e.LastName, e.Title, e.Address, e.City, e.Country }; return Json(employees, JsonRequestBehavior.AllowGet); } [HttpPost] public ActionResult Update(Employee employee) { if (ModelState.IsValid) { db.Entry(employee).State = EntityState.Modified; db.SaveChanges(); return Json("true"); } return Json("false"); } public void Delete(int EmployeeID) { try { var employee = db.Employees.Find(EmployeeID); db.Employees.Remove(employee); db.SaveChanges(); } catch (Exception) { } } [HttpPost] public ActionResult Add(Employee employee) { if (ModelState.IsValid) { db.Employees.Add(employee); db.SaveChanges(); return Json("true"); } return Json("false"); } // // GET: /Employees/Details/5 public ViewResult Details(int id) { Employee employee = db.Employees.Find(id); return View(employee); } // // GET: /Employees/Create public ActionResult Create() { ViewBag.ReportsTo = new SelectList(db.Employees, "EmployeeID", "LastName"); return View(); } // // POST: /Employees/Create [HttpPost] public ActionResult Create(Employee employee) { if (ModelState.IsValid) { db.Employees.Add(employee); db.SaveChanges(); return RedirectToAction("Index"); } ViewBag.ReportsTo = new SelectList(db.Employees, "EmployeeID", "LastName", employee.ReportsTo); return View(employee); } // // GET: /Employees/Edit/5 public ActionResult Edit(int id) { Employee employee = db.Employees.Find(id); ViewBag.ReportsTo = new SelectList(db.Employees, "EmployeeID", "LastName", employee.ReportsTo); return View(employee); } // // POST: /Employees/Edit/5 [HttpPost] public ActionResult Edit(Employee employee) { if (ModelState.IsValid) { db.Entry(employee).State = EntityState.Modified; db.SaveChanges(); return RedirectToAction("Index"); } ViewBag.ReportsTo = new SelectList(db.Employees, "EmployeeID", "LastName", employee.ReportsTo); return View(employee); } // // POST: /Employees/Delete/5 [HttpPost, ActionName("Delete")] public ActionResult DeleteConfirmed(int id) { Employee employee = db.Employees.Find(id); db.Employees.Remove(employee); db.SaveChanges(); return RedirectToAction("Index"); } protected override void Dispose(bool disposing) { db.Dispose(); base.Dispose(disposing); } }}
jqxGrid automatically passes the Sort, Page and Filter parameters(Read more about that here). The GetEmployees method returns the set of employees in the Employees table. The Update, Delete and Add methods will update, delete and add employees. They are called from the script through Ajax. - After that go to the "Views/Employees" folder in your project. Double click on "Index.cshtml". Put there the following content:
<script type="text/javascript"> $(document).ready(function () { // prepare sample data. var data = {}; var firstNames = ["Nancy", "Andrew", "Janet", "Margaret", "Steven", "Michael", "Robert", "Laura", "Anne"]; var lastNames = ["Davolio", "Fuller", "Leverling", "Peacock", "Buchanan", "Suyama", "King", "Callahan", "Dodsworth"]; var titles = ["Sales Representative", "Vice President, Sales", "Sales Representative", "Sales Representative", "Sales Manager", "Sales Representative", "Sales Representative", "Inside Sales Coordinator", "Sales Representative"]; var address = ["507 - 20th Ave. E. Apt. 2A", "908 W. Capital Way", "722 Moss Bay Blvd.", "4110 Old Redmond Rd.", "14 Garrett Hill", "Coventry House", "Miner Rd.", "Edgeham Hollow", "Winchester Way", "4726 - 11th Ave. N.E.", "7 Houndstooth Rd."]; var city = ["Seattle", "Tacoma", "Kirkland", "Redmond", "London", "London", "London", "Seattle", "London"]; var country = ["USA", "USA", "USA", "USA", "UK", "UK", "UK", "USA", "UK"]; var generaterow = function (id) { var row = {}; var firtnameindex = Math.floor(Math.random() * firstNames.length); var lastnameindex = Math.floor(Math.random() * lastNames.length); var k = firtnameindex; row["EmployeeID"] = id; row["FirstName"] = firstNames[firtnameindex]; row["LastName"] = lastNames[lastnameindex]; row["Title"] = titles[k]; row["Address"] = address[k]; row["City"] = city[k]; row["Country"] = country[k]; row["Notes"] = row["FirstName"] + ' received a BA in computer science from the University of Washington'; return row; } var source = { datatype: "json", datafields: [ { name: 'EmployeeID' }, { name: 'FirstName' }, { name: 'LastName' }, { name: 'Title' }, { name: 'Address' }, { name: 'City' }, { name: 'Country' }, { name: 'Notes' } ], id: 'EmployeeID', url: 'Employees/GetEmployees', addrow: function (rowid, rowdata, position, commit) { // synchronize with the server - send insert command $.ajax({ cache: false, dataType: 'json', url: 'Employees/Add', data: rowdata, type: "POST", success: function (data, status, xhr) { // insert command is executed. commit(true); }, error: function (jqXHR, textStatus, errorThrown) { alert(errorThrown); commit(false); } }); }, deleterow: function (rowid, commit) { // synchronize with the server - send delete command $.ajax({ dataType: 'json', cache: false, url: '/Employees/Delete/5', type: "POST", success: function (data, status, xhr) { // delete command is executed. commit(true); }, error: function (jqXHR, textStatus, errorThrown) { alert(jqXHR.statusText); commit(false); } }); }, updaterow: function (rowid, rowdata, commit) { // synchronize with the server - send update command rowdata.EmployeeID = rowid; $.ajax({ cache: false, dataType: 'json', url: 'Employees/Update', data: rowdata, type: "POST", success: function (data, status, xhr) { // update command is executed. commit(true); }, error: function (jqXHR, textStatus, errorThrown) { alert(errorThrown); commit(false); } }); } }; var dataAdapter = new $.jqx.dataAdapter(source); // initialize jqxGrid $("#jqxgrid").jqxGrid( { width: 500, height: 350, source: dataAdapter, columns: [ { text: 'EmployeeID', datafield: 'EmployeeID', width: 100 }, { text: 'First Name', datafield: 'FirstName', width: 100 }, { text: 'Last Name', datafield: 'LastName', width: 100 }, { text: 'Title', datafield: 'Title', width: 180 }, { text: 'Address', datafield: 'Address', width: 180 }, { text: 'City', datafield: 'City', width: 100 }, { text: 'Country', datafield: 'Country' } ] }); $("#addrowbutton").jqxButton(); $("#deleterowbutton").jqxButton(); $("#updaterowbutton").jqxButton(); // update row. $("#updaterowbutton").bind('click', function () { var datarow = generaterow(); var selectedrowindex = $("#jqxgrid").jqxGrid('getselectedrowindex'); var rowscount = $("#jqxgrid").jqxGrid('getdatainformation').rowscount; if (selectedrowindex >= 0 && selectedrowindex < rowscount) { var id = $("#jqxgrid").jqxGrid('getrowid', selectedrowindex); $("#jqxgrid").jqxGrid('updaterow', id, datarow); } }); // create new row. $("#addrowbutton").bind('click', function () { var rowscount = $("#jqxgrid").jqxGrid('getdatainformation').rowscount; var datarow = generaterow(rowscount + 1); $("#jqxgrid").jqxGrid('addrow', null, datarow); }); // delete row. $("#deleterowbutton").bind('click', function () { var selectedrowindex = $("#jqxgrid").jqxGrid('getselectedrowindex'); var rowscount = $("#jqxgrid").jqxGrid('getdatainformation').rowscount; if (selectedrowindex >= 0 && selectedrowindex < rowscount) { var id = $("#jqxgrid").jqxGrid('getrowid', selectedrowindex); $("#jqxgrid").jqxGrid('deleterow', id); } }); });</script><h2>Index</h2><div id='jqxWidget' style="font-size: 13px; font-family: Verdana; float: left;"> <div style="float: left;" id="jqxgrid"> </div> <div style="margin-left: 30px; float: left;"> <div> <input id="addrowbutton" type="button" value="Add New Row" /> </div> <div style="margin-top: 10px;"> <input id="deleterowbutton" type="button" value="Delete Selected Row" /> </div> <div style="margin-top: 10px;"> <input id="updaterowbutton" type="button" value="Update Selected Row" /> </div> </div></div>
- In the last step expand "Global.asax" and double click on "Global.asax.cs". Change the "RegisterRoutes" method to look like this:
public static void RegisterRoutes(RouteCollection routes){ routes.IgnoreRoute("{resource}.axd/{*pathInfo}"); routes.MapRoute( "Employees", // Route name "{controller}/{action}/{id}", // URL with parameters new { controller = "Employees", action = "Index", id = UrlParameter.Optional } // Parameter defaults );} - Press F5 to run the project.