Server 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 = '';
  		//Getting the source data with ajax GET request
  		source = {
  			datatype: "json",
  			datafields: [
  			{ name: 'CompanyName' },
  			{ name: 'ContactName' },
  			{ name: 'ContactTitle' },
  			{ name: 'City' },
  			{ name: 'Country' },
  			{ name: 'Address' }
  			],
  			filter: function () {
  				$("#jqxgrid").jqxGrid('updatebounddata', 'filter');
  			},
  			beforeprocessing: function (data) {
  				var returnData = {};
  				returnData.records = data.d;
  				return returnData;
  			},
  			type: 'GET',
  			formatdata: function (data) {
  				data.filterscount = data.filterscount || 0;
  				formatedData = buildQueryString(data);
  				return formatedData;
  			},
  			url: 'WebService1.asmx/GetCustomers'
  		};
  		var dataAdapter = new $.jqx.dataAdapter(source, {
  			contentType: 'application/json; charset=utf-8', type: 'GET',
  			loadError: function (xhr, status, error) {
  				alert(error);
  			}
  		});
  		$("#jqxgrid").jqxGrid({
  			source: dataAdapter,
  			filterable: true,
  			height: "400px",
  			rendergridrows: function (data) {
  				return data.records;
  			},
  			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.
• Double-click on the WebService1.asmx. Uncomment the [System.Web.Script.Services.ScriptService] attribute which will allow us to call the Web Service from a script.
  

          [WebMethod]
          [ScriptMethod(UseHttpGet = true, ResponseFormat = ResponseFormat.Json)]
          public object GetCustomers()
          {
              var queryString = this.Context.Request.QueryString;
              var query = this.BuildQuery(queryString);
              SqlCommand cmd = new SqlCommand(query);
              // Populate the DataSet.
              var data = this.GetData(cmd);
              return new JavaScriptSerializer().DeserializeObject(data);
          }
          // Gets the Data as JSON.
          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 string BuildQuery(System.Collections.Specialized.NameValueCollection query)
          {
              string queryString = ""
              + "  SELECT * FROM Customers "
              + " ";
              string[] values = query.GetValues("filterscount");
              if (values != null && values.Length > 0)
              {
                  var filtersCount = int.Parse(query.GetValues("filterscount")[0]);
                  var where = "";
                  if (filtersCount > 0)
                  {
                      where += " WHERE (" + this.BuildFilters(filtersCount, query);
                  }
                  queryString += where;
              }
              return queryString;
          }
          // Build the WHERE query. 
          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;
          }
          // Get the SQL string for the Filter Condition. The parameters are the filter's condition, column's dataField and the filter's value passed by the Grid.
          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: