// 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)); }