Issues with Pagination, sorting and search stored procedure in Datatable server side operation

Jbritto

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

allan

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

Jbritto

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

allan

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

Allan