DAL Filter Functions by Foreign Key

Database Connections

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...'.

Lorem ipsum

Lorem ipsum

Lorem ipsum

Lorem ipsum

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)

Well done.

Database Connections