<webServices> <protocols> <add name="HttpGet"/> <add name="HttpPost"/> </protocols></webServices>
<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>
<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>
[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 ""; }