In the previous tutorials we have created a Data Set to act as a Data Access Layer, established Table Adapters to represent each table and setup functions to return a list of all records and a single record based on a specific primary key.
These functions will allow us to display lists of records or individual/single records for each table.
For some of these individual records we may wish to provide a list of related records, such as viewing a single Class and listing all Students, viewing a Category and listing all its Items, or viewing a Building and listing all its locations.
To prepare us for these situations we will create functions to filter by foreign key.
Using a TableAdapter that has foreign keys within it (such as 'Buildings' in our worked example), right-click near the functions and select 'Add Query...'.
SELECT BuildingID, BuildingName, SiteID FROM Buildings WHERE (SiteID = @siteid)
SELECT LocationID, LocationEasyName, LocationTypeID, BuildingID FROM Locations WHERE (BuildingID = @buildingid)
SELECT LocationID, LocationEasyName, LocationTypeID, BuildingID FROM Locations WHERE (LocationTypeID = @locationtypeid)