/// <summary> /// Gets the list of data for use by the jqgrid plug-in /// </summary> public IActionResult OnGetGridDataWithFilters(string sidx, string sord, int _page, int rows, string filters) { int? adminid = null; string password = String.Empty; if (!String.IsNullOrEmpty(filters)) { // deserialize json and get values being searched var jsonResult = JsonConvert.DeserializeObject <Dictionary <string, dynamic> >(filters); foreach (var rule in jsonResult["rules"]) { if (rule["field"].Value.ToLower() == "adminid") { adminid = Convert.ToInt32(rule["data"].Value); } if (rule["field"].Value.ToLower() == "password") { password = rule["data"].Value; } } // sometimes jqgrid assigns a -1 to numeric fields when no value is assigned // instead of assigning a null, we'll correct this here if (adminid == -1) { adminid = null; } } int totalRecords = LoginTable.GetRecordCountDynamicWhere(adminid, password); int startRowIndex = ((_page * rows) - rows); List <LoginTable> objLoginTableCol = LoginTable.SelectSkipAndTakeDynamicWhere(adminid, password, rows, startRowIndex, sidx + " " + sord); int totalPages = (int)Math.Ceiling((float)totalRecords / (float)rows); if (objLoginTableCol is null) { return(new JsonResult("{ total = 0, page = 0, records = 0, rows = null }")); } var jsonData = new { total = totalPages, _page, records = totalRecords, rows = ( from objLoginTable in objLoginTableCol select new { id = objLoginTable.Adminid, cell = new string[] { objLoginTable.Adminid.ToString(), objLoginTable.Password } }).ToArray() }; return(new JsonResult(jsonData)); }
/// <summary> /// Shows how to get a specific number of sorted records, starting from an index, based on Search Parameters. The number of records are also retrieved. /// </summary> private void SelectSkipAndTakeDynamicWhere() { int startRetrievalFromRecordIndex = 0; int numberOfRecordsToRetrieve = 10; string sortBy = "Adminid"; //string sortBy = "Adminid desc"; // search parameters, everything is nullable, only items being searched for should be filled // note: fields with String type uses a LIKE search, everything else uses an exact match // also, every field you're searching for uses the AND operator // e.g. int? productID = 1; string productName = "ch"; // will translate to: SELECT....WHERE productID = 1 AND productName LIKE '%ch%' int? adminid = null; string password = null; // 1. select a specific number of sorted records starting from the index you specify based on Search Parameters List <LoginTable> objLoginTableCol = LoginTable.SelectSkipAndTakeDynamicWhere(adminid, password, numberOfRecordsToRetrieve, startRetrievalFromRecordIndex, sortBy); // to use objLoginTableCol please see the SelectAll() method examples // No need for Examples 1 and 2 because the Collection here is already sorted // Example 3: directly bind to a GridView - for ASP.NET Web Forms // Example 4: loop through all the LoginTable(s). The example above will only loop for 10 items. }