/// <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 = "StepId";
//string sortBy = "StepId 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? stepId = null;
int? worKflowId = null;
string approvalLevel = null;
string description = null;
string autoApprove = null;
int? waitTime = 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 <WorkflowStepsMaster> objWorkflowStepsMasterCol = WorkflowStepsMaster.SelectSkipAndTakeDynamicWhere(stepId, worKflowId, approvalLevel, description, autoApprove, waitTime, createdBy, createdOn, updatedBy, updatedOn, numberOfRecordsToRetrieve, startRetrievalFromRecordIndex, sortBy);
// to use objWorkflowStepsMasterCol 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 WorkflowStepsMaster(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? stepId = null;
int? worKflowId = null;
string approvalLevel = String.Empty;
string description = String.Empty;
string autoApprove = String.Empty;
int? waitTime = 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() == "stepid")
{
stepId = Convert.ToInt32(rule["data"].Value);
}
if (rule["field"].Value.ToLower() == "workflowid")
{
worKflowId = Convert.ToInt32(rule["data"].Value);
}
if (rule["field"].Value.ToLower() == "approvallevel")
{
approvalLevel = rule["data"].Value;
}
if (rule["field"].Value.ToLower() == "description")
{
description = rule["data"].Value;
}
if (rule["field"].Value.ToLower() == "autoapprove")
{
autoApprove = rule["data"].Value;
}
if (rule["field"].Value.ToLower() == "waittime")
{
waitTime = 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 (stepId == -1)
{
stepId = null;
}
if (worKflowId == -1)
{
worKflowId = null;
}
if (waitTime == -1)
{
waitTime = null;
}
}
int totalRecords = WorkflowStepsMaster.GetRecordCountDynamicWhere(stepId, worKflowId, approvalLevel, description, autoApprove, waitTime, createdBy, createdOn, updatedBy, updatedOn);
int startRowIndex = ((_page * rows) - rows);
List <WorkflowStepsMaster> objWorkflowStepsMasterCol = WorkflowStepsMaster.SelectSkipAndTakeDynamicWhere(stepId, worKflowId, approvalLevel, description, autoApprove, waitTime, createdBy, createdOn, updatedBy, updatedOn, rows, startRowIndex, sidx + " " + sord);
int totalPages = (int)Math.Ceiling((float)totalRecords / (float)rows);
if (objWorkflowStepsMasterCol is null)
{
return(new JsonResult("{ total = 0, page = 0, records = 0, rows = null }"));
}
var jsonData = new
{
total = totalPages,
_page,
records = totalRecords,
rows = (
from objWorkflowStepsMaster in objWorkflowStepsMasterCol
select new
{
id = objWorkflowStepsMaster.StepId,
cell = new string[] {
objWorkflowStepsMaster.StepId.ToString(),
objWorkflowStepsMaster.WorKflowId.HasValue ? objWorkflowStepsMaster.WorKflowId.Value.ToString() : "",
objWorkflowStepsMaster.ApprovalLevel,
objWorkflowStepsMaster.Description,
objWorkflowStepsMaster.AutoApprove,
objWorkflowStepsMaster.WaitTime.HasValue ? objWorkflowStepsMaster.WaitTime.Value.ToString() : "",
objWorkflowStepsMaster.CreatedBy,
objWorkflowStepsMaster.CreatedOn.ToString("d"),
objWorkflowStepsMaster.UpdatedBy,
objWorkflowStepsMaster.UpdatedOn.ToString("d")
}
}).ToArray()
};
return(new JsonResult(jsonData));
}