Server-Side Filtering with jqxGrid, ASP.NET MVC 3 and SQL

In this help topic we are going to introduce you how to make server side filtering with jqxGrid and ASP.NET MVC 3.

If you haven't already installed ASP.NET MVC 3 use this resource: http://www.asp.net/mvc/MVC 3
For this help topic 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". New Project

  1. 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". App_Data
  2. After that click with the right mouse button on the "Scripts" directory and choose "Add -> Existing Item". Add scripts In the opened dialog select the following JavaScript files: "jquery-1.11.1.min.js, jqxbuttons.js, jqxcore.js, jqxdata.js, jqxgrid.js, jqxgrid.selection.js, jqxgrid.filter.js, jqxmenu.js, jqxscrollbar.js" from your jqwidgets folder. Add scripts
  3. 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". Add styles
  4. 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. Shared folder After finishing the last step your "_Layout.cshtml" should look like this: 
    <!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.11.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.filter.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>
        <script src="@Url.Content("~/Scripts/jqxlistbox.js")" 
type="text/javascript"></script>
        <script src="@Url.Content("~/Scripts/jqxdropdownlist.js")" 
type="text/javascript"></script>
    </head>
    <body>
        @RenderBody()
    </body>
    </html>
    jqxGrid is using few images. For best look of the widget we recommend you to add them into the project. You can do this as dragging the images folder (located in the folder containing the CSS files included in the previous section) and dropping it over the "Content" folder.
  5. 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". Add model In the "Choose Model Contents" section select "Generate from database" and click Next. Add model 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". Add model
  6. For our purpose we are going to use the "Orders" 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".Code generation item
  7. After that press F6 to Build your project.
  8. Now we are ready to add our Controller. Right click on the "Controller" folder and after that choose "Add -> Controller". Rename it "OrdersController". The choosen template should be "Controller with read/write actions and views, using Entity Framework". For Model class select "Order (Project.Models)" and for Data context class "NORTHWNDEntities2 (Project.Models)" after that choose "Add". Controller
  9. After the generation of the controller have been completed go to the "Controllers" folder and double click on "OrdersController.cs". Add the following method after the "Index" method: 
    public JsonResult GetOrders()
    {
        var query = Request.QueryString;
        var dbResult = db.Database.SqlQuery<Order>(this.BuildQuery(query));
        var orders = from order in dbResult
                        select new Order
                        {
                            ShippedDate = order.ShippedDate,
                            ShipName = order.ShipName,
                            ShipAddress = order.ShipAddress,
                            ShipCity = order.ShipCity,
                            ShipCountry = order.ShipCountry
                        };
        return Json(orders, JsonRequestBehavior.AllowGet);
    }
    private string BuildQuery(
System.Collections.Specialized.NameValueCollection query)
    {
        var filtersCount =  int.Parse(query.GetValues("filterscount")[0]);
        var queryString = @"SELECT * FROM Orders ";
        var tmpDataField = "";
        var tmpFilterOperator = "";
        var where = "";
        if (filtersCount > 0)
        {
            where = " WHERE (";
        }
        for (var i = 0; i < filtersCount; i += 1)
        {
            var filterValue = query.GetValues("filtervalue" + i)[0];
            var filterCondition = query.GetValues("filtercondition" + i)[0];
            var filterDataField = query.GetValues("filterdatafield" + i)[0];
            var filterOperator = query.GetValues("filteroperator" + i)[0];
            if (tmpDataField == "")
    		{
    			tmpDataField = filterDataField;			
    		}
    		else if (tmpDataField != filterDataField)
    		{
    			where += ") AND (";
    		}
    		else if (tmpDataField == filterDataField)
    		{
                if (tmpFilterOperator == "")
                {
                    where += " AND ";
                }
                else
                {
                    where += " OR ";
                }
    		}
    		// build the "WHERE" clause depending 
//on the filter's condition, value and datafield.
    		where += 
this.GetFilterCondition(filterCondition, filterDataField, filterValue);
    		if (i == filtersCount - 1)
    		{
    			where += ")";
    		}
    		tmpFilterOperator = filterOperator;
    		tmpDataField = filterDataField;
        }
        queryString += where;
        return queryString;
    }
    private string GetFilterCondition(string filterCondition, 
string filterDataField, string filterValue)
    {
        switch (filterCondition)
        {
            case "CONTAINS":
                return " " + filterDataField + " LIKE '%" + filterValue + "%'";
            case "DOES_NOT_CONTAIN":
                return " " + filterDataField + " NOT LIKE '%" + filterValue + "%'";
            case "EQUAL":
                return " " + filterDataField + " = '" + filterValue + "'";
            case "NOT_EQUAL":
                return " " + filterDataField + " <> '" + filterValue + "'";
            case "GREATER_THAN":
                return " " + filterDataField + " > '" + filterValue + "'";
            case "LESS_THAN":
                return " " + filterDataField + " < '" + filterValue + "'";
            case "GREATER_THAN_OR_EQUAL":
                return " " + filterDataField + " >= '" + filterValue + "'";
            case "LESS_THAN_OR_EQUAL":
                return " " + filterDataField + " <= '" + filterValue + "'";
            case "STARTS_WITH":
                return " " + filterDataField + " LIKE '" + filterValue + "%'";
            case "ENDS_WITH":
                return " " + filterDataField + " LIKE '%" + filterValue + "'";
            case "NOT_EMPTY":
                return " " + filterDataField + " <> ''";
        }
        return "";
    }
  10. After that go to the "Views/Orders" folder in your project. Double click on "Index.cshtml". Put there the following content: 
    <script type="text/javascript">
        $(document).ready(function () {
            // prepare the data
            var theme = 'classic';
            var source =
            {
                datatype: "json",
                datafields: [
    				{ name: 'ShippedDate', type: 'date' },
    				{ name: 'ShipName' },
    				{ name: 'ShipAddress' },
    				{ name: 'ShipCity' },
    				{ name: 'ShipCountry' }
                ],
                url: 'Orders/GetOrders',
                filter: function () {
                    // update the grid and send a request to the server.
                    $("#jqxgrid").jqxGrid('updatebounddata');
                }
            };
            // initialize jqxGrid
            $("#jqxgrid").jqxGrid(
            {
                source: source,
                theme: theme,
                filterable: true,
                columns: [
                        { text: 'Shipped Date', datafield: 'ShippedDate', 
cellsformat: 'd', width: 200 },
                        { text: 'Ship Name', datafield: 'ShipName', width: 200 },
                        { text: 'Address', datafield: 'ShipAddress', width: 180 },
                        { text: 'City', datafield: 'ShipCity', width: 100 },
                        { text: 'Country', datafield: 'ShipCountry', width: 140 }
                    ]
            });
        });
    </script>
    <h2>Index</h2>
    <div id="jqxgrid"></div>
  11. 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(
            "Orders", // Route name
            "{controller}/{action}/{id}", // URL with parameters
            new { controller = "Orders", action = "Index", 
id = UrlParameter.Optional } // Parameter defaults
        );
    }
Press F5 and see the result: jqxGrid