/// <summary> /// Shows how to get the total number of records based on Search Parameters. /// </summary> private void GetRecordCountDynamicWhere() { // 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? userId = null; string userName = null; string password = null; string email = null; DateTime?createdOn = null; string createdBy = null; DateTime?modifiedOn = null; string modifiedBy = null; int totalRecordCount = UserMaster.GetRecordCountDynamicWhere(userId, userName, password, email, createdOn, createdBy, modifiedOn, modifiedBy); }
/// <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? userId = null; string userName = String.Empty; string password = String.Empty; string email = String.Empty; DateTime?createdOn = null; string createdBy = String.Empty; DateTime?modifiedOn = null; string modifiedBy = 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() == "userid") { userId = Convert.ToInt32(rule["data"].Value); } if (rule["field"].Value.ToLower() == "username") { userName = rule["data"].Value; } if (rule["field"].Value.ToLower() == "password") { password = rule["data"].Value; } if (rule["field"].Value.ToLower() == "email") { email = rule["data"].Value; } if (rule["field"].Value.ToLower() == "createdon") { createdOn = Convert.ToDateTime(rule["data"].Value); } if (rule["field"].Value.ToLower() == "createdby") { createdBy = rule["data"].Value; } if (rule["field"].Value.ToLower() == "modifiedon") { modifiedOn = Convert.ToDateTime(rule["data"].Value); } if (rule["field"].Value.ToLower() == "modifiedby") { modifiedBy = 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 (userId == -1) { userId = null; } } int totalRecords = UserMaster.GetRecordCountDynamicWhere(userId, userName, password, email, createdOn, createdBy, modifiedOn, modifiedBy); int startRowIndex = ((_page * rows) - rows); List <UserMaster> objUserMasterCol = UserMaster.SelectSkipAndTakeDynamicWhere(userId, userName, password, email, createdOn, createdBy, modifiedOn, modifiedBy, rows, startRowIndex, sidx + " " + sord); int totalPages = (int)Math.Ceiling((float)totalRecords / (float)rows); if (objUserMasterCol is null) { return(new JsonResult("{ total = 0, page = 0, records = 0, rows = null }")); } var jsonData = new { total = totalPages, _page, records = totalRecords, rows = ( from objUserMaster in objUserMasterCol select new { id = objUserMaster.UserId, cell = new string[] { objUserMaster.UserId.ToString(), objUserMaster.UserName, objUserMaster.Password, objUserMaster.Email, objUserMaster.CreatedOn.ToString("d"), objUserMaster.CreatedBy, objUserMaster.ModifiedOn.HasValue ? objUserMaster.ModifiedOn.Value.ToString("d") : "", objUserMaster.ModifiedBy } }).ToArray() }; return(new JsonResult(jsonData)); }