How to search a jquery datatable properly from a SQL stored procedure

Question

Good day

I'm doing a sorting, search and pagination to a jquery datatable via a stored procedure

ALTER PROCEDURE [dbo].[sp_ProgressreportView] 
(  
  @SearchValue NVARCHAR(255)=NULL ,  
  @PageNo INT = 1,
  @PageSize INT = 10,
  @SortColumn INT = 0,
  @SortDirection NVARCHAR(10) = 'asc'
)
AS
BEGIN
  SET NOCOUNT ON;
  SET ANSI_NULLS ON
  SET QUOTED_IDENTIFIER ON
    
  DECLARE @TotalCount AS INT = (SELECT COUNT(Id) AS records
FROM            dbo.ProgressreportView)
 
  DECLARE @FirstRec int, @LastRec int
  SET @FirstRec = @PageNo * @PageSize + 1;
  SET @LastRec = (@PageNo + 1) * @PageSize;
 
  SET @SearchValue = LTRIM(RTRIM(@SearchValue)) 
 
  ; WITH CTE_Results AS  
  (  
    SELECT ROW_NUMBER() OVER (ORDER BY 
 
      CASE WHEN (@SortColumn = 0 AND @SortDirection='asc')  
            THEN Plot
      END ASC,  
      CASE WHEN (@SortColumn = 0 AND @SortDirection='desc')  
          THEN Plot
      END DESC, 
      CASE WHEN (@SortColumn = 1 AND @SortDirection='asc')  
            THEN Status
      END ASC,  
      CASE WHEN (@SortColumn = 1 AND @SortDirection='desc')  
            THEN Status 
      END DESC,  
      CASE WHEN (@SortColumn = 2 AND @SortDirection='asc')  
            THEN Report_Date 
      END ASC,  
      CASE WHEN (@SortColumn = 2 AND @SortDirection='desc')  
            THEN Report_Date  
      END DESC,
      CASE WHEN (@SortColumn = 3 AND @SortDirection='asc')  
            THEN Contractor  
      END ASC,  
      CASE WHEN (@SortColumn = 3 AND @SortDirection='desc')  
            THEN Contractor 
      END DESC,
	  
      CASE WHEN (@SortColumn = 4 AND @SortDirection='asc')  
            THEN Projectname  
      END ASC,  
      CASE WHEN (@SortColumn = 4 AND @SortDirection='desc')  
            THEN Projectname 
      END DESC,
	   CASE WHEN (@SortColumn = 5 AND @SortDirection='asc')  
            THEN Tipologyname  
      END ASC,  
      CASE WHEN (@SortColumn = 5 AND @SortDirection='desc')  
            THEN Tipologyname 
      END DESC,
	   CASE WHEN (@SortColumn = 6 AND @SortDirection='asc')  
            THEN Inspector  
      END ASC,  
      CASE WHEN (@SortColumn = 6 AND @SortDirection='desc')  
            THEN Inspector 
      END DESC
	
    )
    AS RowNum,
    COUNT(*) OVER() as FilteredCount,
  
    Plot, 
    Status, 
    Report_Date,
	Contractor,
	Projectname,
	Tipologyname,
	Inspector
    FROM [dbo].[ProgressreportView] 
        
    WHERE ISNULL(@SearchValue, '') = '' OR @SearchValue != '' 
    AND Plot LIKE '%' + @SearchValue + '%'
    OR Status LIKE '%' + @SearchValue + '%'
	OR  REPLACE(CONVERT(VARCHAR(21), Report_Date, 13),' ','-') LIKE '%'+@SearchValue+'%'   
	OR Contractor LIKE '%' + @SearchValue + '%'
	OR Projectname LIKE '%' + @SearchValue + '%'
	OR Tipologyname LIKE '%' + @SearchValue + '%'
	or Inspector LIKE '%' + @SearchValue + '%'
  ) 
 
  SELECT
     
    Plot, 
    Status, 
    Report_Date,
	Contractor,
	Projectname,
	Tipologyname,
	Inspector,
    FilteredCount,
    @TotalCount AS TotalCount
  FROM CTE_Results
  WHERE RowNum BETWEEN @FirstRec AND @LastRec  
    
end

The procedure works fine. I have a ASP.NET CORE WEN API that serves jquery datatable end point requests

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

 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);
     
     return new DatatableResponseToAPI<Progressreport>()
     {
         Draw = request.draw,
         RecordsTotal = products[0].TotalCount,
         RecordsFiltered = products[0].FilteredCount,
         Data = products.ToArray(),
         Error = ""
     }

Up to this point, everything works 100 % . the WEB APi pages, sorts and search properly. the issue is when the stored procedure returns no result when it can find what the users looks for in the jquery datatable search box so the API debugger is telling me that there is an System.ArgumentOutOf RangeException: Index was out of range. Everything points to the above code where I'm trying to array the result of the stored procedure which isn't returning no value

( Data=products.Toarray()) is there any form that I circumvent this issue by modifying the response I sent to the user jquery datatable.( see below) I repeat ....everything works like a charm except this detail

public class DatatableResponseToAPI<T>
  {
      //maping  JQueryDataTables response parameters
      public int Draw { get; set; }
      public int RecordsTotal { get; set; }
      public int RecordsFiltered { get; set; }
      public T[]? Data { get; set; }
      public string Error { get; set; }
  }

Thanks in advance.

Answer 1

Hi @Jose Daniel Navarro Brito,

Could you set a breakpoint in this line var products = await GetProgressReport(req); ? Check if it is null or not.

Here's an optimization of your code.

            var products = await GetProgressReport(req);
            int recordsTotal = 0;
            int recordsFiltered = 0;
            Progressreport[] dataArray = Array.Empty<Progressreport>();
            if (products.Count > 0)
            {
                recordsTotal = products[0].TotalCount;
                recordsFiltered = products[0].FilteredCount;
                dataArray = products.ToArray();
            }
            return new DatatableResponseToAPI<Progressreport>()
            {
                Draw = request.draw,
                RecordsTotal = recordsTotal,
                RecordsFiltered = recordsFiltered,
                Data = dataArray,
                Error = ""
            };

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best Regards

Jason