Bind jqxGrid to SQL Database using ASP.NET MVC4

In this tutorial we are going to introduce you how to use jqxGrid in ASP.NET MVC 4.

If you haven't already installed ASP.NET MVC 4 use this resource: http://www.asp.net/mvc/mvc4
For this tutorial you're also going to need the Entity Framework: http://www.microsoft.com/download/en/details.aspx?id=18504
The database which we will use is the AdventureWorks database which you can download from here. So let's begin!

Create a new ASP.NET MVC 4 project. For "View engine" select "Razor".

1. Click "Tools" and then click "Connect to Database".
2. Browse to the database file and click the "OK" button.
3. Open "Solution Explorer" and right-click on your project.
4. Choose "Add>New Item...".
5. In the "Add New Item" dialog, select Data and "ADO .NET Entity Model".
   
   
   
   
6. In the "Entity Data Model Wizard" choose "Generate from Database" and click the "Next" button.
   
   
   
   Select your connection and click the "Next" button again.
   
   
   
   
7. In the "Solution Explorer", select "Controllers" and right-click. Choose "Add>Controller".
   
   
   
   
8. In the "Add Controller" dialog, set the Controller name to "EmployeeController". The Model class should be set to "Employee". The Data context class should be set to point to the Model container.
   
   
   
   
9. In the "Solution Explorer", expand "Controllers" and double-click the "EmployeeController". Add a method called "GetEmployees".

   using System;
   using System.Collections.Generic;
   using System.Data;
   using System.Data.Entity;
   using System.Linq;
   using System.Web;
   using System.Web.Mvc;
   namespace Grid_MVC4.Controllers
   {
       public class EmployeeController : Controller
       {
           private Model1Container db = new Model1Container();
           public JsonResult GetEmployees()
           {
               var dbResult = db.Employees.ToList();
               var employees = (from employee in dbResult
                                select new
                                {
                                    employee.BirthDate,
                                    employee.Gender,
                                    employee.JobTitle,
                                    employee.SickLeaveHours,
                                    employee.VacationHours
                                });
               return Json(employees, JsonRequestBehavior.AllowGet);
           }
           //
           // GET: /Employee/
           public ActionResult Index()
           {
               return View(db.Employees.ToList());
           }
           //
           // GET: /Employee/Details/5
           public ActionResult Details(int id = 0)
           {
               Employee employee = db.Employees.Find(id);
               if (employee == null)
               {
                   return HttpNotFound();
               }
               return View(employee);
           }
           //
           // GET: /Employee/Create
           public ActionResult Create()
           {
               return View();
           }
           //
           // POST: /Employee/Create
           [HttpPost]
           public ActionResult Create(Employee employee)
           {
               if (ModelState.IsValid)
               {
                   db.Employees.Add(employee);
                   db.SaveChanges();
                   return RedirectToAction("Index");
               }
               return View(employee);
           }
           //
           // GET: /Employee/Edit/5
           public ActionResult Edit(int id = 0)
           {
               Employee employee = db.Employees.Find(id);
               if (employee == null)
               {
                   return HttpNotFound();
               }
               return View(employee);
           }
           //
           // POST: /Employee/Edit/5
           [HttpPost]
           public ActionResult Edit(Employee employee)
           {
               if (ModelState.IsValid)
               {
                   db.Entry(employee).State = EntityState.Modified;
                   db.SaveChanges();
                   return RedirectToAction("Index");
               }
               return View(employee);
           }
           //
           // GET: /Employee/Delete/5
           public ActionResult Delete(int id = 0)
           {
               Employee employee = db.Employees.Find(id);
               if (employee == null)
               {
                   return HttpNotFound();
               }
               return View(employee);
           }
           //
           // POST: /Employee/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);
           }
       }
   }
   

10. In the "Solution Explorer", expand "Views" and double-click the "Employee". Then open "Index.cshtml".
11. Remove all code in the "Index.cshtml" and add the following initialization code.

    @model IEnumerable<Grid_MVC4.Employee>
    @{
        ViewBag.Title = "Index";
    }
    @section scripts {
    <script type="text/javascript" src="http://jqwidgets.com/jquery-widgets-demo/scripts/jquery-1.11.1.min.js"></script>
    <script type="text/javascript" src="http://jqwidgets.com/jquery-widgets-demo/jqwidgets/jqx-all.js"></script>
    <link rel="stylesheet" type="text/css" href="http://jqwidgets.com/jquery-widgets-demo/jqwidgets/styles/jqx.base.css" />    
    <script type="text/javascript">
        $(document).ready(function () {
            // prepare the data
            var source =
                {
                    datatype: "json",
                    datafields: [
    					 { name: 'BirthDate', type: 'date' },
    					 { name: 'Gender', type: 'string' },
    					 { name: 'JobTitle', type: 'string' },
    					 { name: 'SickLeaveHours', type: 'string' },
    					 { name: 'VacationHours', type: 'string' }
                    ],
                    url: 'Employee/GetEmployees'
                };
            var dataAdapter = new $.jqx.dataAdapter(source);
            // initialize jqxGrid
            $("#grid").jqxGrid(
                {
                    width: 600,
                    source: dataAdapter,
                    columns: [
                        { text: "Birth Date", datafield: "BirthDate", cellsformat: 'd' },
                        { text: "Gender", datafield: "Gender" },
                        { text: "Job Title", datafield: "JobTitle" },
                        { text: "Sick Leave Hours", datafield: "SickLeaveHours" },
                        { text: "Vacation Hours", datafield: "VacationHours" }
                    ]
                });
        });
    </script>
    }
    <h2>Index</h2>
    <div id="grid"></div>
    

    
    If you are just starting with jqxGrid, please take a look at the following help topics about it:
    • Getting Started with jqxGrid
    • Data Adapter
    • Grid Data Sources
    
    The source object's "url" should point to the "GetEmployees" method.
12. In the "Solution Explorer" expand "App_Start" and double click on "RouteConfig.cs"

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.Mvc;
    using System.Web.Routing;
    namespace Grid_MVC4
    {
        public class RouteConfig
        {
            public static void RegisterRoutes(RouteCollection routes)
            {
                routes.IgnoreRoute("{resource}.axd/{*pathInfo}");
                routes.MapRoute(
                    name: "Employee",
                    url: "{controller}/{action}/{id}",
                    defaults: new { controller = "Employee", action = "Index", id = UrlParameter.Optional }
                );
            }
        }
    }
    

In Visual Studio, select "Debug>Start Debugging" or press F5. The result is: