Hey guys, I am Venkat and this is part 10 of Blazor web assembly video series.
In this video we'll discuss how to implement data grid server side paging. At the moment
we are on the first page of the data grid and we see the first five rows and in the footer,
we also see the total count of rows 34, and if we take a look at visual studio output window,
notice two queries are issued to the database, select count(*) from employees, basically this
is retrieving the total number of rows that we have in the "Employees" database table
which is then used by the grid to display the total count here in the footer and also compute
the number of pages to display, and the second query retrieves the actual five rows that we
are displaying. Notice, we are selecting all the columns from Employees table and then we have two
parameters @p0 and @p1, the value for p0 as you can see right here is 0 and the value for p1 is 5,
so basically these two parameters are telling, skip the first zero rows and retrieve five rows,
basically retrieve the first five rows because you're still on the first page. Now, let me click
on the third page here and see what's going to happen. So, on the third page we're able to see
rows from 11 to 15 and the total count is still 34. So, if we take a look at visual studio again,
notice another set of two queries are issued again, select count(*) to retrieve the total
number of employees and then if we take a look at this query, notice it's the same query,
the only difference here is the parameter values okay p0 is now 10 and p1 is 5, so basically,
it's telling the sql query to skip the first 10 rows and then retrieve the next five rows
okay so that's why it's able to return us rows from 11 to 15. So, this is server side paging,
only retrieving the rows that we are displaying to the user. This is much better from a performance
standpoint, because we're not unnecessarily retrieving data that we are not displaying to the
user. Especially if you have millions and millions of rows in the database this is the sort of paging
that we want to use. Server-side paging is also commonly called on-demand paging or database
paging. We discussed implementing client-side paging in part eight of this video series.
I'll include the link to the complete course page in the description box below this video.
Now, here's the important point to keep in mind, to implement server-side paging the data grid
needs to know two things - the count of total rows and the list of rows to display. The data
grid uses the total count of rows and the page size to calculate the number of pages to display
in the pager in the data grid footer. So our first step is to modify our server side rest
api so it's able to return pageed data and not all the rows. We want to be able to pass these
two parameters to our api "skip" and "take", so the api knows how many rows to skip and how
many rows to actually retrieve. At the moment, when we navigate to this URI - "api/employees"
we see the list of all employees and that's not what we want, we want to be able to pass in the
URI two query string parameters skip and take. So skip 10 rows and take 20 rows. At the moment,
our API is not making use of these two parameters. So, let's modify it so the API returns paged data.
Our EmployeesController is in this "Controllers" folder in BlazorProject.Server. If we scroll
a bit down, it is this GetEmployees() method that returns us the list of all
employees and it's calling GetEmployees() method on our employee repository. Employee
repository is present in this "Models" folder. So, let's open both EmployeeRepository class and
the interface IEmployeeRepository. The first thing that we need to do here is modify GetEmployees()
method. We want to introduce those two parameters - skip and take. The data type
for both the parameters is integer. Next, let's modify the implementation. Implementation is
in this file "EmployeeRepository.cs" and the name of the method is GetEmployees(). First,
we need to introduce those two parameters skip and take, so both of them are of type integer.
I'm also going to specify default values just in case if we don't provide explicitly any values for
these two parameters. So, by default we want to skip zero rows and take five rows. Notice,
when I hover the mouse over this "Employees" property, from the intellisense you can see it
is of type DbSet and on this property we have skip and take extension methods, so all we do
here is pass the incoming parameters, skip to the skip method and take to the take method. Remember
we want to return two things from this method, the total number of employee rows that we have
and the five rows that we want to display in the data grid, so for that, we need a new class. So,
what i'm going to do is, in our shared project, add a new class, name it EmployeeDataResult
First, let's make this class public and we need two properties. Employees which is of
type IEnumerable objects, this is going to hold the list of five employees that we want
to display in the data grid. Count is going to hold the total count of employees and then we're
going to specify this class as the return type both in our interface and implementation. So,
for GetEmployees() method, instead of returning IEnnumerable objects,
we're going to return Task. Let's do the same change in the implementation
class as well. In the method body, let's create a new instance of EmployeeDataResult. Let's call
the variable "result" and populate both these properties - Employees
and Count. To get the count, on AppDbContext.Employees property,
we have CountAsync(), let's await its execution and finally return this EmployeeDataResult.
Next, in EmployeesController, on this GetEmployees() method,
we need to include the same set of two parameters - skip and take. So, let me actually copy them
from our EmployeeRepository and paste them right here. All that is left to do is, pass
these two incoming parameters to GetEmployees() method on our EmployeeRepository. With all these
changes in place, let's run our project and quickly test. Our page fails to load data,
that's because we have an unhandled exception and we'll fix that in just a bit. At the moment,
when we navigate to our api url "api/employees", notice we're not explicitly specifying any values
for skip and take parameters, so it's using the default values, 0 for skip, 5 for take, so we see
the first set of 5 rows, and we also have the total count of employees - 34, and if
we explicitly provide values for skip and take, 10 for skip, 24 take, so it's skipping the first
10 rows and retrieving the next 20 rows, so we see rows from 11 to 20. Again, we still have the total
count of employees which is 34. Next, let's take a look at the changes that we have to make in this
BlazorProject.Client. Remember, our Syncfusion data grid is present on this page "index.razor".
We're going to do a lot of cleanup on this page, first of all we don't need the spinner component
because the data grid is smart enough to display its own spinner if it's waiting for data. So,
let's delete the style and the spinner component itself. We also don't need this "if" check.
In the code block, except this pagerDropDown property, we can delete everything else. Now,
to keep this example focused on paging, let's remove all these properties related to
sorting. In our upcoming videos, we'll discuss implementing custom sorting as well. As you can
see, paging is already turned on, oh we have another property here related to sorting,
so let's get rid of this as well. Now, to retrieve and bind server-side page data, we're going to use
custom data binding along with Syncfusion DataManager component. So, inside this grid,
let's include Syncfusion data manager component, and the tag for that is .
To keep the code simple, let's move the namespace Syncfusion.Blazor.Data to imports.razor file.
@using Syncfusion.Blazor.Data. Remember, with this grid we want to use custom data binding
and for that, we need a custom data adaptor. So, let me add a new class to
the "Services" folder in our client project. Name it EmployeeAdaptor. For this class to behave like
custom data adaptor, it has to derive from the built-in Syncfusion abstract class - DataAdapter.
This class is present in Syncfusion.Blazor namespace, and we want to override Read() method,
because it is this method that provides the data to the grid, and notice there are
two variations - Read() and ReadAsync(). We can use either of them. Let's override ReadAsync().
On this incoming dataManagerRequest object, notice from the intellisense, we have skip and take
properties, so we can use these properties to retrieve the page data from our server-side rest
api, we'll do that in just a bit, but before that, we need to modify our client-side EmployeeService.
So, let's open both the EmployeeService class and its interface IEmployeeService. On this
GetEmployees() method include the two parameters - skip and take. We also need to change the return
type to EmployeeDataResult. We need to make similar changes in the implementation class
EmployeeService. Our GetEmployees() method is right here, first we need to pass the two
parameters skip and take and change the return type to EmployeeDataResult. Even in this "return"
statement, we need to change the data type to EmployeeDataResult and in the rest api url,
we need to include the two query string parameters - skip and take. Query string parameters
start with question mark (?), skip equals the incoming skip parameter and similarly take
equals the incoming take parameter. Next, in our custom employee data adaptor,
we need to inject this IEmployeeService, so we need a constructor for that
Inject IEmployeeService
Let's call the parameter "employeeService" and then generate the required private field.
Inside this ReadAsync() method, let's use the injected employee service and on that, we have
GetEmployees() method, we need to pass values for both the parameters - skip and take, and we know
we have the values for these two parameters on the incoming data manager request object
This is an async method, so let's "await" its execution and let's not forget to turn this method
into "async" and whatever result we get back, let's store that in EmployeeDataResult object.
Let's bring in the required namespace and let's call the variable "result".
Now, here's the important point to keep in mind, the Syncfusion datagrid component
expects this ReadAsync() method to return an object of type DataResult,
so let's create a new instance of this class and this class is present in
Syncfusion.Blazor.Data namespace, let's call the variable dataResult = new DataResult.
This class has two properties "Result" and it should contain the list of employees that we
want to display and we know that list of employees are present in this variable "result.Employees",
as you can see from the intellisense, it returns IEnumerable objects, that is the list of
employees that we want to display in the grid, and we should also let the grid know the total
count of employees. Again, we have that in the "Count" property of our "result" object. Finally,
return the dataResult object. Next, we need to register our custom employee adapter
with the asp.net core dependency injection system. In a blazor webassembly project
we do that in the Main() method and the Main() method is present in this file - program.cs. So,
builder.Services.AddScoped(). Finally, we need to tell the data grid about
this custom employee adapter. Our data grid is in index.razor page. So, let's open that. With
this syncfusion data manager component we want to use a custom adapter and we tell that by setting
"Adaptor = Adaptors.CustomAdaptor". Finally, we need to tell this data manager to use an instance
of our custom employee adapter class, we specify that by using "AdapterInstance" property and then,
we use "typeof" keyword typeof(EmployeeAdaptor). Now, here's another important point to keep in
mind, since we are using a custom adaptor to provide the data to the grid, we no longer need
this "DataSource" property, instead we need TValue, basically this property tells the type of
object the grid is working with, in our case it's working with "Employee" objects. With all these
changes in place, let's run our project. There we go, we see the first five employees as expected
and if we take a look at the "output" window in visual studio, notice we see both the queries
as expected, select count(*) which retrieves the total count of employees and this query right here
retrieves the first five employees and one interesting thing to note here is, the parameter
values are not displayed, instead a question mark is displayed next to both the parameters
p0 and p1. To see the parameter values we have to enable sensitive data logging and we do that in
startup.cs file. So in this "BlazorProject.Server" we have our startup.cs file, let's open that. In
ConfigureServices() method, on this line where we are configuring our sql server instance, we need
to chain EnableSensitiveDataLogging() method. With this change in place let's run our project again.
As expected we see the first five rows and if we take a look at visual studio, we see the parameter
values as well, p0 is 0, p1 is 5. Remember, the first parameter is skip and the second parameter
is take, since we are on the first page we want to skip zero rows and take the first five rows. Now,
let's see what's going to happen when we navigate to page three. If we take a look at visual studio,
notice the second query right here, p0 value is 10 and p1 value is 5. So, basically we are
skipping the first 10 rows, that's why when we are on the third page, we see the rows from 11 to 15
and these are five rows so the second parameter value p1 is 5. Notice, when I reload this page,
the data grid is smart enough to display its own inbuilt spinner
while waiting for server-side data. That's it in this video, thank you for listening.