// Load data action
public JsonResult Load(int page, int itemsPerPage, string sortBy, string sortDirection, string searchQuery)
{
// Construct new params
FetchDtParams parameters = new FetchDtParams();
parameters.pageNumber = page;
parameters.pageNumItems = itemsPerPage;
parameters.sortColumn = sortBy;
parameters.sortDirection = sortDirection;
parameters.searchQuery = searchQuery;
// Get data from database
FetchDtResultsMusic files = musicFiles.fetchDt(parameters);
// Return JSON data
return Json(files, JsonRequestBehavior.AllowGet);
}
// Fetch records for datatable
public FetchDtResultsMusic fetchDt(FetchDtParams parameters)
{
// Get datatable column list
PropertyInfo[] infoDt = typeof(MusicFileDtRow).GetProperties();
List<String> columnListDt = (from r in infoDt select r.Name).ToList();
// Explode if we don't have any columns
if (columnListDt.Count < 1) {
throw new GlitterException("No columns have been specified for datatable query.");
}
// Check sort column
if (!columnListDt.Contains(parameters.sortColumn)) {
throw new GlitterException("Invalid sort column for datatable query.");
}
// Check sort direction
if (parameters.sortDirection != "ASC" && parameters.sortDirection != "DESC") {
throw new GlitterException("Invalid sort direction for datatable query.");
}
// Check page number
if (parameters.pageNumber < 1) {
throw new GlitterException("Invalid page number for datatable query.");
}
// Check number of items per page
if (parameters.pageNumItems < 1 || parameters.pageNumItems > 2000) {
throw new GlitterException("Invalid number of items per page for datatable query.");
}
// Make sure we didn't specify an insane length for search query
if (parameters.searchQuery != null)
{
if (parameters.searchQuery.Length > 100)
{
throw new GlitterException("Invalid search query length for datatable query.");
}
}
// Generate WHERE clause
List<String> wheres = new List<String>();
foreach (String columnDt in columnListDt) {
wheres.Add("(" + columnDt + " LIKE {0})");
}
// Construct query to get total rows
String queryFullResults = @"SELECT " + string.Join(", ", columnListDt.ToArray()) + " FROM " + TableName;
// Construct query to retrieve records
String query = @"SELECT " + string.Join(", ", columnListDt.ToArray()) + " FROM " + TableName;
// Add the search query if it is populated
if (parameters.searchQuery != null)
{
queryFullResults += " WHERE " + string.Join(" OR ", wheres.ToArray());
query += " WHERE " + string.Join(" OR ", wheres.ToArray());
}
queryFullResults += " ORDER BY " + parameters.sortColumn + " " + parameters.sortDirection;
query += " ORDER BY " + parameters.sortColumn + " " + parameters.sortDirection + @"
OFFSET " + ((parameters.pageNumber - 1) * parameters.pageNumItems).ToString() + @" ROWS
FETCH NEXT " + parameters.pageNumItems.ToString() + " ROWS ONLY";
Debug.WriteLine("Fail in query: " + queryFullResults);
// Get the total number of rows available
List<MusicFileDtRow> rowCountResults = gdc.Database.SqlQuery<MusicFileDtRow>(queryFullResults, "%" + parameters.searchQuery + "%").ToList();
int numRows = rowCountResults.Count;
// Model database results for full results
Dictionary<String, MusicFileDtRow> dbResultsFull = new Dictionary<String, MusicFileDtRow>();
foreach (MusicFileDtRow row in rowCountResults)
{
//dbResultsFull.Add(row.Id.ToString(), row);
}
// Get database results
var dbResults = gdc.Database.SqlQuery<MusicFileDtRow>(query, "%" + parameters.searchQuery + "%").ToList();
// Create results object
return new FetchDtResultsMusic(dbResults, dbResultsFull, numRows, parameters.pageNumItems, parameters.pageNumItems * (parameters.pageNumber - 1));
}
