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

JbrittoJbritto Posts: 3Questions: 2Answers: 0
edited July 22 in Free community support

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

  • allanallan Posts: 63,419Questions: 1Answers: 10,456 Site admin

    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 or FilteredCount.

    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

  • JbrittoJbritto Posts: 3Questions: 2Answers: 0

    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

  • allanallan Posts: 63,419Questions: 1Answers: 10,456 Site admin

    Can you just check products.Count() and if zero send back a fabricated empty reply? i.e. set RecordsTotal and friends to 0.

    Allan

Sign In or Register to comment.