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.
