Issues with Pagination, sorting and search stored procedure in Datatable server side operation
Issues with Pagination, sorting and search stored procedure in Datatable server side operation
![Jbritto](https://secure.gravatar.com/avatar/8f6ff42a0cad5322141d9271a371c425/?default=https%3A%2F%2Fvanillicon.com%2F8f6ff42a0cad5322141d9271a371c425_200.png&rating=g&size=120)
Good day out there:
I implemented the tutorial below successfully in my project
=> https://www.ezzylearning.net/tutorial/jquery-datatables-paging-sorting-and-filtering-in-asp-net-core-using-dapper-orm
However the Jquery datatable is throwing an AJAX error just only when the SQL stored procedure returns nothing when the search doesn't yield result. Obviously the error is in the server side and is a System.ArgumentOutOf RangeException: Index was out of range. My hint is that because I'm don't find what I'm looking for , the database return nothing then when I want to convert the result of the query to an array then this error pops up ????? ( see code below).
( THIS IS THE CALL TO THE DATABASE)
public async Task<List<Progressreport>> GetProgressReport(DBRequestForSearchPagingAndFiltering request)
{
try
{
var parameters = new DynamicParameters();
parameters.Add("SearchValue", request.SearchValue, DbType.String);
parameters.Add("PageNo", request.PageNo, DbType.Int32);
parameters.Add("PageSize", request.PageSize, DbType.Int32);
parameters.Add("SortColumn", request.SortColumn, DbType.Int32);
parameters.Add("SortDirection", request.SortDirection, DbType.String);
return (await _sqlConnection.QueryAsync<Progressreport>("sp_ProgressreportView", parameters, transaction: _dbTransaction, commandType: CommandType.StoredProcedure)).ToList();
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
THIS IS THE METHOD THAT I TAKE THE REQUEST PARAMETERS FROM THE JQUERY DATATABLE AND PASS IT TO SQL STORED PROCEDURE AND THEN RETURN A RESPPONSE TO THE API ENDPONT
public async Task<DatatableResponseToAPI<Progressreport>> GetProgressReportAsync(DataTableApiRequest request)
{
var req = new DBRequestForSearchPagingAndFiltering()
{
PageNo = Convert.ToInt32(request.start / request.length),
PageSize = request.length,
SortColumn = request.Order[0].Column,
SortDirection = request.Order[0].Dir,
SearchValue = request.Search != null ? request.Search.Value.Trim() : ""
};
var products = await GetProgressReport(req); => if the search in the database return no data then this variable is zero
return new DatatableResponseToAPI<Progressreport>()
{
Draw = request.draw,
RecordsTotal = products[0].TotalCount,
RecordsFiltered = products[0].FilteredCount,
Data = products.ToArray(), => Think that the problem is here. I can't convert a list to an array when there isn't data
Error = ""
};
}
Can you please assist me in how sort this out. The datatable sorting and paging works just fine ...it's only this detail
Thanks in advance
Answers
I can't realistically be expected to support third party articles and code, but I don't see anything in your code about it getting
TotalCount
orFilteredCount
.I see the call to
GetProgressReport
which makes a single query - maybe the stored procedure gets all that information. I'm afraid that is outside of the scope of the free help that I can provide. You'd need to contact the author of the article if you are having problems with what is described in it.Beyond that, I'd suggest debugging the stored procedure to check if it is getting the data that is needed.
Allan
Hi Allan:
Thanks for your response. As I previously said, the jquery datatable works perfect and yes, the stored procedure returns the Total Count and FilteredCount in one go. My question is more related to the conversion of a List into nan array when actually there isn't data. that is the case when the stored procedure returns zero records.
I understand ....thanks anyway
Can you just check
products.Count()
and if zero send back a fabricated empty reply? i.e. setRecordsTotal
and friends to 0.Allan