Server Sorting 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 populate the Grid with data from the Customers table. *You can also add a connection from the "Server Explorer".
• In the "Solution Explorer", right-click on the Project 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'}],
                  sort: function () {
                      $("#jqxgrid").jqxGrid('updatebounddata', 'sort');
                  },
                  beforeprocessing: function (data) {
                      var returnData = {};
                      returnData.records = data.d;
                      return returnData;
                  },
                  type: 'get',
                  formatdata: function (data) {
                      data.sortdatafield = data.sortdatafield || '';
                      data.sortorder = data.sortorder || '';
                      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,
                  sortable: true,
                  height: 500,
                  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 sort callback function to the source object, jqxGrid will call that function when the Sort order is changed. 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.
  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.
  

      [WebService(Namespace = "http://tempuri.org/")]
      [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
      // 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);
              SqlCommand cmd = new SqlCommand(query);
              // Populate the DataSet.
              var data = this.GetData(cmd);
              return new JavaScriptSerializer().DeserializeObject(data);
          }
          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);
              }
              sbJson.Replace("},", "}]", sbJson.Length - 2, 2);
              return sbJson.ToString();
          }
          private string BuildQuery(System.Collections.Specialized.NameValueCollection query)
          {
              string dataField = query.GetValues("sortdatafield")[0];
              string order = query.GetValues("sortorder")[0];
              if (order != "")
              {
                  string queryString = ""
                  + "  SELECT * FROM Customers "
                  + "ORDER BY " + dataField + " " + order;
                  return queryString;
              }
              else
              {
                  string queryString = "SELECT * FROM Customers";
                  return queryString;
              }
          }        
      }
  

  
  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.
• Set default.aspx as a start page and start the Project
• As a result, you should see: