ASP.NET Core | Datagrid server side paging

In this video we will discuss how to implement datagrid server side paging in ASP.NET Core Blazor Webassembly project. It's also called on-demand paging or database paging.

We discussed implementing Datagrid client-side paging in Part 8 of this video series. Click here to learn implementing client-side datagrid paging.

Two things for implementing server side datagrid paging

To implement server side paging, the datagrid needs to know 2 things.

1. The count of total rows and
2. The list of rows to display

The datagrid uses the total count of rows and the page size to calculate the number of pages to display in the Pager in the DataGrid footer.

Server side API must provide paged data

• Server side API or data access layer must be able to provide paged data.
• GetEmployees() method returns paged data.
• skip parameter specifies the number of parameters to skip and take parameter specifies the number of parameters to retrieve.

EmployeesController.cs

[Route("api/[controller]")]
[ApiController]
public class EmployeesController : ControllerBase
{
    private readonly IEmployeeRepository employeeRepository;

    public EmployeesController(IEmployeeRepository employeeRepository)
    {
        this.employeeRepository = employeeRepository;
    }

    [HttpGet]
    public async Task<ActionResult> GetEmployees(int skip = 0, int take = 5)
    {
        try
        {
            return Ok(await employeeRepository.GetEmployees(skip, take));
        }
        catch (Exception)
        {
            return StatusCode(StatusCodes.Status500InternalServerError,
                "Error retrieving data from the database");
        }
    }
}

IEmployeeRepository.cs

public interface IEmployeeRepository
{
    Task<EmployeeDataResult> GetEmployees(int skip, int take);
}

EmployeeRepository.cs

public class EmployeeRepository : IEmployeeRepository
{
    private readonly AppDbContext appDbContext;

    public EmployeeRepository(AppDbContext appDbContext)
    {
        this.appDbContext = appDbContext;
    }

    public async Task<EmployeeDataResult> GetEmployees(int skip = 0, int take = 5)
    {
        EmployeeDataResult result = new EmployeeDataResult()
        {
            Employees = appDbContext.Employees.Skip(skip).Take(take),
            Count = await appDbContext.Employees.CountAsync()
        };

        return result;
    }
}

EmployeeDataResult.cs

public class EmployeeDataResult
{
    public IEnumerable<Employee> Employees { get; set; }
    public int Count { get; set; }
}

Changes in Blazor Client Project

<SfGrid TValue="Employee" AllowPaging="true">
    <SfDataManager Adaptor="Adaptors.CustomAdaptor" AdaptorInstance="@typeof(EmployeeAdaptor)">
    </SfDataManager>
    <GridPageSettings PageSize="5" PageSizes="@pagerDropdown" PageCount="3"></GridPageSettings>
    <GridColumns>
        <GridColumn Field=@nameof(Employee.EmployeeId) HeaderText="ID"></GridColumn>
        <GridColumn Field=@nameof(Employee.FirstName) HeaderText="First Name"></GridColumn>
        <GridColumn Field=@nameof(Employee.LastName) HeaderText=" Last Name"></GridColumn>
        <GridColumn Field=@nameof(Employee.Email) HeaderText="Email"></GridColumn>
    </GridColumns>
</SfGrid>

@code{
    public string[] pagerDropdown { get; set; } = new string[] { "All", "5", "10", "15", "20" };
}

1. On the Syncfusion DataGrid set AllowPaging property to true.
2. To retrieve the server side data use SfDataManager with a Custom Adaptor.
3. It's the Custom Adaptor that calls server side REST API and provides data to the DataGrid.
4. Syncfusion DataAdaptor abstract class is the base class for our custom Adaptor (EmployeeAdaptor).
5. The DataAdaptor abstract class has both synchronous and asynchronous variations.
6. We are overriding ReadAsync() method to manually call the server side REST API and provide data to the DataGrid.

public class EmployeeAdaptor : DataAdaptor
{
    private readonly IEmployeeService employeeService;

    public EmployeeAdaptor(IEmployeeService employeeService)
    {
        this.employeeService = employeeService;
    }
        
    public async override Task<object> ReadAsync(DataManagerRequest dataManagerRequest, string key = null)
    {
        EmployeeDataResult result = 
            await employeeService.GetEmployees(dataManagerRequest.Skip, dataManagerRequest.Take);

        DataResult dataResult = new DataResult()
        {
            Result = result.Employees,
            Count = result.Count
        };

        return dataResult;
    }
}

Inject service into Custom Adaptor

Since we are injecting a service (IEmployeeService) into Custom Adaptor, we need to register Custom Adaptor using AddScoped() method in Program.cs file.

public class Program
{
    public static async Task Main(string[] args)
    {
        var builder = WebAssemblyHostBuilder.CreateDefault(args);
        builder.Services.AddSyncfusionBlazor();
        builder.Services.AddScoped<EmployeeAdaptor>();
        await builder.Build().RunAsync();
    }
}

Tying the Custom Adaptor and DataGrid together

<SfDataManager Adaptor="Adaptors.CustomAdaptor" AdaptorInstance="@typeof(EmployeeAdaptor)"></SfDataManager>

IEmployeeService.cs

public interface IEmployeeService
{
    Task<EmployeeDataResult> GetEmployees(int skip, int take);
}

EmployeeService.cs

public class EmployeeService : IEmployeeService
{
    private readonly HttpClient httpClient;

    public EmployeeService(HttpClient httpClient)
    {
        this.httpClient = httpClient;
    }

    public async Task<EmployeeDataResult> GetEmployees(int skip, int take)
    {
        return await httpClient.GetFromJsonAsync<EmployeeDataResult>($"/api/employees?skip={skip}&take={take}");
    }
}

Logging SQL Statements parameters in Entity Framework Core

In ConfigureServices() method of the Startup class EnableSensitiveDataLogging

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<AppDbContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("DBConnection"))
        .EnableSensitiveDataLogging());
}