Hey guys, I am Venkat, and this is part 25 of web development with Blazor video series. In
this video we'll discuss how to retrieve and display data from two or more related tables
in a data grid, so here is what we want to be able to do, notice, all these columns id, first name,
last name, date of birth, email and gender, these are coming from Employees table, and
department name is coming from Departments table, so basically you want to be able to retrieve data
from Employees and Departments table and display it in the data grid. In this example we're only
working with two database tables - Employees and Departments, but the concepts that we'll
discuss here can be used to retrieve and display data from three or even more database tables.
Now, the following are the three different types of relationships between database tables - one to
one, one to many, and many to many, consider these three database tables - Employees,
Salaries and Departments, we have a one-to-one relationship between Employees and Salaries and
Employees and Departments table, DepartmentId in Employees table is a foreign key referencing Id
column in Departments table, similarly SalaryId in Employees table is a foreign key referencing
Id column in Salaries table, so here's what we want to be able to do, display department name
in the data grid along with employee first name, last name etc, department name is present in
the Departments table, and it is linked to the Employees table through the foreign key DeptId,
so to display department name in the data grid we use component, we'll see
this in action in just a bit. In the interest of time here's what I have already done, in the pages
folder included a new component DataGridFK.razor which is right here, and within this component
we have a Syncfusion DataGrid, and these are the columns that we are displaying at the moment,
employee Id, first name, last name etc, and here we have gender column, remember gender is an enum,
and to be able to edit gender we are using a drop-down list, we discussed binding an
enum to a drop-down list in detail in part 18 of this video series, and then finally here
we have department id, remember department id is a foreign key referencing id column
from the Departments table, so if we take a look at this component in the web page,
as expected we see all the columns id, first name, last name, date of birth etc, notice for
department we see the integer values, but instead we want to display department name, and we know
department name is present in the Departments table, i.e the foreign key table, so to be able to
easily achieve this instead of using component, we are going to use
component, Field is still going to be DepartmentId column in the Employees table because it is this
column that we want the grid to bind to, but what we want to display is the department name,
and we know the list of all departments are coming from this Departments database table, and we
already have a REST API service that provides this Departments data to our web application, if you're
new to building RESTful services, we discussed them in detail in part 5 of this video series,
now, just like how we have these two properties Employees and EmployeeService,
we need two similar properties for departments, so let me paste the two lines of code that we need,
nothing really new here, we're going to use this DepartmentService to retrieve the
list of all departments and store them in this property - Departments, so in
OnInitializedAsync() method Departments property = await DepartmentService.GetAllDepartments()
and then convert that to the list, now, here's the important bit, on this
component we're going to set this Departments property as the ForeignDataSource because this
property contains a list of all departments, so this component can take this
integer DepartmentId value, look up the respective department in this property and then use the
department name for display purpose, so the first order of business here is to set this Departments
property as the ForeignDataSource on this component,
and for that, on this component we have ForeignDataSource and the value for this
property is Departments property, finally we need to tell which property in this Department class
we want to use for display purpose, in real world a Department class may contain several properties
like DepartmentId, Name, Location, Head etc, so which of these properties we want to use for
display purpose? and to tell that we have another property on this component
called ForeignKeyValue, and in our case the foreign key value is DepartmentName
There we go, we see the department name as expected, it's that simple, so to display foreign
key data in a data grid use component and set these three properties - Field,
ForeignDataSource and ForeignKeyValue, Field specifies the field that we want to bind to,
in our case it is DepartmentId, ForeignDataSource contains the list of all departments in our case,
and ForeignKeValue is the column that we want to use for display purpose, in our case
its DepartmentName, now, all the CRUD operations, that is create, read, update and delete are also
seamlessly supported by this component, let's actually implement them,
first we need to enable adding, editing and deleting, for that we use
component, and set these properties AllowAdding, editing and deleting to true,
to be able to perform CRUD operations we need the data grid toolbar with add, edit, update,
delete and cancel buttons, for that on the data grid we set Toolbar property,
with these two changes the data grid is enabled for CRUD operations, now, when we add a new row,
notice we have a drop down list for the department column and the same is true even when we try to
edit an existing employee row, at the moment we don't have the code to handle CRUD operations
and save changes in the underlying database table, so let's implement that code now,
if you're new to implementing CRUD operations in a data grid, we discussed them in detail in part 19
of this video series, to be able to save changes in the underlying Employees database table we need
to handle grid events like add, update, delete etc, so let's include component,
the type of value our grid is working with is Employee because the data source for a grid
is this Employees property which is a List and here is the
even handler method, we don't have this method yet, so let's create it in the code block here
Again, we discussed this piece of code in detail in part 19 of this video series, so
if you're new to implementing CRUD operations in a data grid please check out part 19, in a nutshell
when we perform CRUD operations on the data grid this is the even handler that is called, so when
we are adding a new employee we are calling AddEmployee() method on our EmployeeService
and passing it the incoming data, if it is update then we are calling UpdateEmployee(),
similarly if it is delete we are calling DeleteEmployee() method, and when we add a
new employee we have to do slightly more work, we have to reload the data and refresh the data grid,
we are already reloading data into the Employees property to which our data grid is binding to,
and all that is left to do is refresh the grid, and for that we need another property,
so let me paste that property right here, and then on the data grid itself we need @ref
and the value for this is the property that we have just created.
Finally, refresh the grid.
Let's add a new employee, provide first and last names, date of birth,
email,
gender, and finally, department, click Update. Notice, we have our newly
inserted employee record here, we selected HR and it is correctly stored as expected,
and if we take a look at the database table, we have that same record right here, and look at the
department id value, it is 2 which corresponds to HR, now, let's edit the same record in the grid,
let's change the Department to Admin, Gender to Female, and let's also change his last name to
Mason1, update, take a look at the database table, data updated correctly LastName, Gender
and DepartmentId, finally, let's test delete operation, let's delete this record with id 96,
click the delete button, record is gone, and let's confirm that from the database table, there we go,
we don't have that record with id 96 anymore, that's it in this video, thank you for listening.