Bind jqxGrid to SQL Database using ASP.NET MVC3

jqwidgets

Rate me:

5.00/5 (1 vote)

23 Mar 2012CPOL3 min read

20.3K

How to bind jqxGrid to SQL Database using ASP.NET MVC 3

In this post, we are going to introduce how to bind jqxGrid to SQL Database using ASP.NET MVC 3. If you haven’t already installed ASP.NET MVC 3, you can download it from here and the Entity Framework from here. We will also use the Northwind Database which you can download from here.

Create a 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”.
After that, click with the right mouse button on the “Scripts” directory and choose “Add -> Existing Item”. In the opened dialog, select the following JavaScript files: “jquery-1.7.1.min.js, jqxbuttons.js, jqxcore.js, jqxdata.js, jqxgrid.js, jqxgrid.selection.js, jqxmenu.js, jqxscrollbar.js” from your jqwidgets folder.
In the next step, you have to include the jqxGrid’s CSS dependencies – “jqx.base.css” and “jqx.classic.css”. Just right click on the “Content” directory, after that select “Add -> Existing Item”, choose “jqx.base.css” and “jqx.classic.css” from your folder and click “Add”.

Expand the “View” director,y 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:

HTML

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<title>@ViewBag.Title</title>
<link href="@Url.Content("~/Content/Site.css")"
rel="stylesheet" type="text/css" />
<link href="@Url.Content("~/Content/jqx.base.css")"
rel="stylesheet" type="text/css" />
<link href="@Url.Content("~/Content/jqx.classic.css")"
rel="stylesheet" type="text/css" />
<script src="@Url.Content("~/Scripts/jquery-1.7.1.min.js")"
type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jqxcore.js")"
type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jqxbuttons.js")"
type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jqxdata.js")"
type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jqxgrid.js")"
type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jqxgrid.selection.js")"
type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jqxmenu.js")"
type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jqxscrollbar.js")"
type="text/javascript"></script>
</head>
<body>
@RenderBody()
</body>
</html>

jqxGrid visual styles include few images like arrows and close button icons. For best look of the widget, we recommend you to add them into the project, too. You can do this by dragging the images folder (located in the folder containing the CSS files included in the previous section) and dropping it over the “Content” folder.

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 next section (“Choose Your Database Objects”) check the “Tables” and “Stored Procedures” checkboxes and click “Finish”.
As a DataTable, we are going to use the “Customers” 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 “CustomersController”. The chosen 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 has been completed, go to the “Controllers” folder and double click on “CustomersController.cs”. Add the following method after the “Index” method:

public JsonResult GetCustomers()
{
    var dbResult = db.Customers.ToList();
    var customers = from customer in dbResult

    select new { customer.CompanyName,
    customer.ContactName,
    customer.ContactTitle,
    customer.Address,
    customer.City };
    return Json(customers, JsonRequestBehavior.AllowGet);
}

After that, go to the “Views/Customers” folder in your project. Double click on “Index.cshtml”. Put the following content there:

JavaScript

<script type="text/javascript">
    $(document).ready(function () {
        // prepare the data
        var source = {
            datatype: "json",
            datafields: [{ name: 'CompanyName' }, { name: 'ContactName' },
            { name: 'ContactTitle' }, { name: 'Address' }, { name: 'City' }, ],
            url: 'Customers/GetCustomers'
        };
        $("#jqxgrid").jqxGrid({
            source: source,
            theme: 'classic',
            columns: [{ text: 'Company Name', datafield: 'CompanyName',
width: 250 }, { text: 'ContactName', datafield: 'ContactName', width: 150 },
{ text: 'Contact Title', datafield: 'ContactTitle', width: 180 },
{ text: 'Address', datafield: 'Address', width: 200 },
{ text: 'City', datafield: 'City', width: 120}]
        });
    });
</script>

<h2>Index</h2>
<div id="jqxgrid"></div>

Finally, 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(
        "Customers", // Route name
        "{controller}/{action}/{id}", // URL with parameters

         new { controller = "Customers", action = "Index",
         id = UrlParameter.Optional } // Parameter defaults
    );
}

Press F5 and see the result:

CodeProject

This article was originally posted at http://www.jqwidgets.com/bind-jquery-grid-to-sql-database-using-asp-net-mvc3

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By

jqwidgets

jQWidgets

United States

jQWidgets specializes in the development of platform independent and cross-browser compatible presentation layer components for building modern web-based applications for PC, Touch and Mobile. Our product provides developers with the essential set of User Interface widgets needed to streamline their development and reduce project costs.
Our goal is to help our customers deliver better web-based applications that can be accessed through any device and are pleasure to use.

This is a Organisation

13 members

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Bind jqxGrid to SQL Database using ASP.NET MVC3

License

Comments and Discussions