Server Sorting, Paging and Filtering with jqxGrid, ASP.NET and SQL

• Open Visual Studio.
• Create a new ASP .NET Web Application.
• Click the "Data" menu item in the Visual Studio's Menu.
• Add a new DataSource. In the sample, we will use the Northwind.mdf and will bind the Grid to the Customers table.
  
  
  
  
  
  
  
  
• Right-click on the Project in the Solution Explorer, and click "Add New Item".
  
• In the "Add New Item" dialog, select "Web Service" and click Add.
  
  
  
  
• In the "Solution Explorer", double click on the "Web.config" and add the following code:

  <webServices>
    <protocols>
  	<add name="HttpGet"/>
  	<add name="HttpPost"/>
    </protocols>
  </webServices>
  

• Add the connectionStrings in the Web.config.
  They should be similar to the code below:

  <connectionStrings>
  	<add name="ApplicationServices" connectionString="Data Source=JOHN-PC\SQL8;AttachDbFilename=|DataDirectory|\NORTHWND.MDF;Initial Catalog=NORTHWINDDB;Integrated Security=True"
  	providerName="System.Data.SqlClient" />
  	<add name="NORTHWNDConnectionString" connectionString="Data Source=JOHN-PC\SQL8;AttachDbFilename=|DataDirectory|\NORTHWND.MDF;Initial Catalog=NORTHWINDDB;Integrated Security=True"
  	providerName="System.Data.SqlClient" />
  </connectionStrings>
  

  
  *Note: To find your connection string, open the Server Explorer, click on the NORTHWIND.MDF under the Data Connections node, and the Connection String will be visible in the Properties Window.
• In the Solution Explorer, click Site.Master and in the head section, add references to the jQuery Framework, jQWidgets Framework(jqx-all.js) and the Theme Files(jqx.base.css and any other theme file like jqx.classic.css).
• Double-click on the "Default.aspx" file.
  Add the following initialization code for jqxGrid. The Grid in the code is initialized in a DIV tag with id="jqxgrid".

      <script type="text/javascript">
          $(document).ready(function () {
              function buildQueryString(data) {
                  var str = '';
                  for (var prop in data) {
                      if (data.hasOwnProperty(prop)) {
                          str += prop + '=' + data[prop] + '&';
                      }
                  }
                  return str.substr(0, str.length - 1);
              }
              var formatedData = '';
              var totalrecords = 0;
              //Getting the source data with ajax GET request
              var source = {
                  datatype: "json",
                  datafields: [
                  { name: 'CompanyName' },
                  { name: 'ContactName' },
                  { name: 'ContactTitle' },
                  { name: 'City' },
                  { name: 'Country' },
                  { name: 'Address' }
                  ],
                  sort: function () {
                      $("#jqxgrid").jqxGrid('updatebounddata', 'sort');
                  },
                  filter: function () {
                      $("#jqxgrid").jqxGrid('updatebounddata', 'filter');
                  },
                  beforeprocessing: function (data) {
                      var returnData = {};
                      data = data.d;
                      totalrecords = data.count;
                      returnData.totalrecords = data.count;
                      returnData.records = data.data;
                      return returnData;
                  },
                  type: 'get',
                  sortcolumn: 'CompanyName',
                  sortdirection: 'asc',
                  formatdata: function (data) {
                      data.pagenum = data.pagenum || 0;
                      data.pagesize = data.pagesize || 10;
                      data.sortdatafield = data.sortdatafield || 'CompanyName';
                      data.sortorder = data.sortorder || 'asc';
                      data.filterscount = data.filterscount || 0;
                      formatedData = buildQueryString(data);
                      return formatedData;
                  },
                  url: 'Service.asmx/GetCustomers'
              };
              var dataAdapter = new $.jqx.dataAdapter(source, {
                  contentType: 'application/json; charset=utf-8',
                  loadError: function (xhr, status, error) {
                      alert(error);
                  }
              });
              $("#jqxgrid").jqxGrid({
                  source: dataAdapter,
                  pageable: true,
                  autoheight: true,
                  virtualmode: true,
                  sorttogglestates: 1,
                  filterable: true,
                  sortable: true,
                  rendergridrows: function (args) {
                      return args.data;
                  },
                  columns: [
                      { text: 'Company Name', dataField: 'CompanyName', width: 250 },
                      { text: 'Contact Name', dataField: 'ContactName', width: 150 },
                      { text: 'Contact Title', dataField: 'ContactTitle', width: 180 },
                      { text: 'Address', dataField: 'Address', width: 180 },
                      { text: 'City', dataField: 'City', width: 80 },
                      { text: 'Country', dataField: 'Country', width: 100 }
                  ]
              });
          });
      </script>
  

  
  By adding a filter callback function to the source object, jqxGrid will call that function when a Filter is applied or removed. The function calls the Grid's updatebounddata method which makes a server call. The server call contains data about the Filter, Sort Order, Page Size or Page Number. For more information about that data, visit: jquery-grid-extra-http-variables.htm.
  In the source object, we also add a sort callback function. It is called when the sort order is changed. The sort callback function makes a server call as well.
  The beforeprocessing callback function is called when the data is downloaded and before the data is loaded in the Grid. At this point, we update the totalrecords field which determines the total number of records in jqxGrid.
• Double-click on the Service.asmx. Uncomment the [System.Web.Script.Services.ScriptService] attribute which will allow us to call the Web Service from a script.
  

  using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Web;
  using System.Web.Services;
  using System.Net;
  using System.IO;
  using System.Web.Script.Services;
  using System.Web.Script.Serialization;
  using System.Web.Script;
  using System.Data.SqlClient;
  using System.Data;
  using System.Configuration;
  using System.Text;
  namespace WebService
  {
      /// <summary>
      /// Summary description for Service
      /// </summary>
      [WebService(Namespace = "http://tempuri.org/")]
      [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
      [ScriptService]
      // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
      // [System.Web.Script.Services.ScriptService]
      public class Service : System.Web.Services.WebService
      {
          [WebMethod]
          [ScriptMethod(UseHttpGet = true, ResponseFormat = ResponseFormat.Json)]
          public object GetCustomers()
          {
              var queryString = this.Context.Request.QueryString;
              var query = this.BuildQuery(queryString);
              var pagenum = int.Parse(queryString.GetValues("pagenum")[0]);
              var pagesize = int.Parse(queryString.GetValues("pagesize")[0]);
              var endWhere = " a WHERE row > " + pagenum * pagesize + " AND row <= " + (pagenum + 1) * pagesize;
              query = "SELECT * FROM (" + query + ")" + endWhere;
              SqlCommand cmd = new SqlCommand(query);
              // Populate the DataSet.
              var data = this.GetData(cmd);
              data = "{ \"count\": " + this.GetTotalRowsCount() + ", \"data\":" + data + "}";
              return new JavaScriptSerializer().DeserializeObject(data);
          }
          public int GetTotalRowsCount()
          {
              string query = this.BuildQuery(this.Context.Request.QueryString);
              SqlCommand countCmd = new SqlCommand("SELECT COUNT(*) as c FROM (" + query + ") as T");
              int count = this.GetRowsCount(countCmd);
              return count;
          }
          private string GetData(SqlCommand cmd)
          {
              string json;
              string strConnString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
              using (SqlConnection con = new SqlConnection(strConnString))
              {
                  cmd.Parameters.AddWithValue("@Param", "value");
                  cmd.Connection = con;
                  con.Open();
                  using (SqlDataReader reader = cmd.ExecuteReader())
                  {
                      json = this.ReadToJson(reader);
                  }
                  con.Close();
              }
              return json;
          }
          public string ReadToJson(SqlDataReader reader)
          {
              List<string> cols = new List<string>(10);
              int ncols = reader.FieldCount;
              for (int i = 0; i < ncols; ++i)
              {
                  cols.Add(reader.GetName(i));
              }
              StringBuilder sbJson = new StringBuilder("[");
              //process each row
              while (reader.Read())
              {
                  sbJson.Append("{");
                  foreach (string col in cols)
                  {
                      sbJson.AppendFormat("\"{0}\":\"{1}\", ", col, reader[col]);
                  }
                  sbJson.Replace(", ", "},", sbJson.Length - 2, 2);
              }
              if (sbJson.Length < 2) return "[]";
              sbJson.Replace("},", "}]", sbJson.Length - 2, 2);
              return sbJson.ToString();
          }
          private int GetRowsCount(SqlCommand cmd)
          {
              string strConnString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
              SqlConnection con = new SqlConnection(strConnString);
              cmd.Connection = con;
              con.Open();
              SqlDataReader reader = cmd.ExecuteReader();
              reader.Read();
              int rows = int.Parse(reader["c"].ToString());
              con.Close();
              return rows;
          }
          private string BuildQuery(System.Collections.Specialized.NameValueCollection query)
          {
              string queryString = ""
              + "  SELECT *, ROW_NUMBER() OVER (ORDER BY " + query.GetValues("sortdatafield")[0] + " "
              + query.GetValues("sortorder")[0].ToUpper() + ") as row FROM Customers "
              + " ";
              var filtersCount = int.Parse(query.GetValues("filterscount")[0]);
              var where = "";
              if (filtersCount > 0)
              {
                  where += " WHERE (" + this.BuildFilters(filtersCount, query);
              }           
              queryString += where;
              return queryString;
          }
          private string BuildFilters(int filtersCount, System.Collections.Specialized.NameValueCollection query)
          {
              var tmpDataField = "";
              var where = "";
              var tmpFilterOperator = "";
              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;
              }
              return where;
          }
          private string GetFilterCondition(string filterCondition, string filterDataField, string filterValue)
          {
              switch (filterCondition)
              {
                  case "NOT_EMPTY":
                  case "NOT_NULL":
                      return " " + filterDataField + " NOT LIKE '" + "" + "'";
                  case "EMPTY":
                  case "NULL":
                      return " " + filterDataField + " LIKE '" + "" + "'";
                  case "CONTAINS_CASE_SENSITIVE":
                      return " " + filterDataField + " LIKE '%" + filterValue + "%'" + " COLLATE SQL_Latin1_General_CP1_CS_AS";
                  case "CONTAINS":
                      return " " + filterDataField + " LIKE '%" + filterValue + "%'";
                  case "DOES_NOT_CONTAIN_CASE_SENSITIVE":
                      return " " + filterDataField + " NOT LIKE '%" + filterValue + "%'" + " COLLATE SQL_Latin1_General_CP1_CS_AS"; ;
                  case "DOES_NOT_CONTAIN":
                      return " " + filterDataField + " NOT LIKE '%" + filterValue + "%'";
                  case "EQUAL_CASE_SENSITIVE":
                      return " " + filterDataField + " = '" + filterValue + "'" + " COLLATE SQL_Latin1_General_CP1_CS_AS"; ;
                  case "EQUAL":
                      return " " + filterDataField + " = '" + filterValue + "'";
                  case "NOT_EQUAL_CASE_SENSITIVE":
                      return " BINARY " + 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_CASE_SENSITIVE":
                      return " " + filterDataField + " LIKE '" + filterValue + "%'" + " COLLATE SQL_Latin1_General_CP1_CS_AS"; ;
                  case "STARTS_WITH":
                      return " " + filterDataField + " LIKE '" + filterValue + "%'";
                  case "ENDS_WITH_CASE_SENSITIVE":
                      return " " + filterDataField + " LIKE '%" + filterValue + "'" + " COLLATE SQL_Latin1_General_CP1_CS_AS"; ;
                  case "ENDS_WITH":
                      return " " + filterDataField + " LIKE '%" + filterValue + "'";
              }
              return "";
          }
      }
  }
  

  
  The GetCustomers method returns the JSON data to be displayed in the Grid. It uses several utility methods like BuildQuery which builds the SQL query depending on the parameters passed by the Grid. The ReadToJSON function builds the JSON string after the query is executed.
  The BuildFilters utility method, builds the WHERE clause using the parameters sent by jqxGrid.
• Set default.aspx as a start page and start the Project
• As a result, you should see: