/// <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 = "WorkflowId"; //string sortBy = "WorkflowId 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? workflowId = null; string workflowName = null; int? levelOfApprovals = null; string createdBy = null; DateTime?createdOn = null; string updatedby = null; DateTime?updatedon = null; // 1. select a specific number of sorted records starting from the index you specify based on Search Parameters List <WorkflowMaster> objWorkflowMasterCol = WorkflowMaster.SelectSkipAndTakeDynamicWhere(workflowId, workflowName, levelOfApprovals, createdBy, createdOn, updatedby, updatedon, numberOfRecordsToRetrieve, startRetrievalFromRecordIndex, sortBy); // to use objWorkflowMasterCol 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 WorkflowMaster(s). The example above will only loop for 10 items. }
/// <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? workflowId = null; string workflowName = String.Empty; int? levelOfApprovals = null; string createdBy = String.Empty; DateTime?createdOn = null; string updatedby = String.Empty; DateTime?updatedon = null; 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() == "workflowid") { workflowId = Convert.ToInt32(rule["data"].Value); } if (rule["field"].Value.ToLower() == "workflowname") { workflowName = rule["data"].Value; } if (rule["field"].Value.ToLower() == "levelofapprovals") { levelOfApprovals = Convert.ToInt32(rule["data"].Value); } if (rule["field"].Value.ToLower() == "createdby") { createdBy = rule["data"].Value; } if (rule["field"].Value.ToLower() == "createdon") { createdOn = Convert.ToDateTime(rule["data"].Value); } if (rule["field"].Value.ToLower() == "updatedby") { updatedby = rule["data"].Value; } if (rule["field"].Value.ToLower() == "updatedon") { updatedon = Convert.ToDateTime(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 (workflowId == -1) { workflowId = null; } if (levelOfApprovals == -1) { levelOfApprovals = null; } } int totalRecords = WorkflowMaster.GetRecordCountDynamicWhere(workflowId, workflowName, levelOfApprovals, createdBy, createdOn, updatedby, updatedon); int startRowIndex = ((_page * rows) - rows); List <WorkflowMaster> objWorkflowMasterCol = WorkflowMaster.SelectSkipAndTakeDynamicWhere(workflowId, workflowName, levelOfApprovals, createdBy, createdOn, updatedby, updatedon, rows, startRowIndex, sidx + " " + sord); int totalPages = (int)Math.Ceiling((float)totalRecords / (float)rows); if (objWorkflowMasterCol is null) { return(new JsonResult("{ total = 0, page = 0, records = 0, rows = null }")); } var jsonData = new { total = totalPages, _page, records = totalRecords, rows = ( from objWorkflowMaster in objWorkflowMasterCol select new { id = objWorkflowMaster.WorkflowId, cell = new string[] { objWorkflowMaster.WorkflowId.ToString(), objWorkflowMaster.WorkflowName, objWorkflowMaster.LevelOfApprovals.HasValue ? objWorkflowMaster.LevelOfApprovals.Value.ToString() : "", objWorkflowMaster.CreatedBy, objWorkflowMaster.CreatedOn.ToString("d"), objWorkflowMaster.Updatedby, objWorkflowMaster.Updatedon.ToString("d") } }).ToArray() }; return(new JsonResult(jsonData)); }