How to page thru sqlkata PaginationResult?

Viewed 226

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();
}
1 Answers

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.

Related