How to page thru sqlkata PaginationResult?

Question

I am using .NET Core and SQLKata to access SQL Server database. I have a method to get all records from the database using SQLKata.Execution.PaginationResult.

This is in Repository:

public class MyTableRepository : IMyTableRepository
{
   private QueryFactory _db;
   public MyTableRepository(IConfiguration configuration)
   {
     var conn = new 
     SqlConnection(configuration.GetConnectionString("MyTable"));
     _db = new QueryFactory(conn, new SqlKata.Compilers.SqlServerCompiler());
   }

  public PaginationResult<MyModel> GetAll(int page = 0, int perPage = 25)
  {
    dbResult = _db.Query("MyTable").Paginate<MyModel>(page, perPage);
    return dbResult; 
  }

The above is called from my Controller like so:

    private readonly IMyTableRepository _MyTableRepository;
    public MyTableController(IMyTableRepository MyTableRepository)
    {
        _MyTableRepository = MyTableRepository;
    }
    [HttpGet]
    [Route("GetMyTable")]
    public List<MyModel> GetMyTable()
    {
        PaginationResult<MyModel> dbResult = MyTableRepository.GetAll(1, 
        25);
        List<MyModel> AccumResult = dbResult.List.ToList();
        while(dbResult.HasNext)    
        {       
            dbResult = dbResult.Next();
            AccumResult.AddRange(dbResult.List.ToList());
        }
        return AccumResult;  
    }

How do I get the Next set of result from dbResult ?

I tried below, after I execute GetMyTable, I execute GetNextMyTable, but in PaginationResult GetNext(), dbResult is always null.

In controller:

    [HttpGet]
    [Route("GetNextMyTable")]
    public List<MyTable> GetNextMyTable()
    {
        var result = _MyTableRepository.GetNext().List;
        return result.ToList();
    }

In Repository:

public PaginationResult<MyTable> GetNext()
{
  while(dbResult.HasNext)    //--->> dbResult is null
  {       
   dbResult = dbResult.Next();
   return dbResult; 
  }      
  return null;
}

If I do the Next method inside the Controller, I am also getting an error

private readonly IMyTableRepository _MyTableRepository;
private PaginationResult<SP_SMA_Reporting_Accts> dbResult;

[HttpGet]
[Route("GetMyTable")]
public List<MyModel> GetMyTable()
{
    var dbResult = _MyTableRepository.GetAll(1, 25).List;
    return dbResult.ToList();
}

[HttpGet]
[Route("GetNextMyTable")]
public List<MyTable> GetNextMyTable()
{
    var result = dbResult.Next().List;//->Error since dbResult is null
    return result.ToList();
}

why not returning `db.Query(...).Paginate(page, perPage)` directly inside `GetAll()`, then use the `result.Next()` method — amd, Jan 13 '22 at 12:52
Did you mean to also do result.Next() inside GetAll() ? dbResult = _db.Paginate(new Query("MyTable"), page, perPage); dbResult.Next(); return dbResult; — faujong, Jan 13 '22 at 13:47
Nop, just return the `PaginationResult` and use the `Next()` method in your controller — amd, Jan 13 '22 at 13:49
In my Controller, in GetNextSP_SMA_Reporting_Accts(), when I do var result = Result.Next().List; Result is also null, so I get an error also. I updated my original posting with this code — faujong, Jan 13 '22 at 14:09

score 0 · Answer 1 · answered Jan 13 '22 at 16:55

0

In short refactor your repository method GetAll to call the db.Query

use:

_db.Query("MyTable").Paginate<MyModel>(page, perPage);

instead of

_db.Paginate<MyModel>(new Query("MyTable"), page, perPage);

Explanation:
when calling the Next() method the PaginationResult delegate the pagination process to the holding query, in your case since your are passing new Query() thus no connection info, you are getting null.

answered Jan 13 '22 at 16:55

amd

20,637
6
49
67

I edited GetMyTable() in the Controller in the original post. So, GetMyTable() will return all the records from the table in the List AccumResult. Is this what you meant ? How about if I only want to return 25 records at a time (this is the Pagination perPage) in the List ? And, when the calling application call GetNext() it will return the next 25 records (.Next() function). Is that possible ? – faujong Jan 13 '22 at 22:44

How to page thru sqlkata PaginationResult?

1 Answers1