DAL Filter Functions by Foreign Key

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 to 'Use SQL Statements' and click 'Next'.

Choose 'SELECT which returns rows' and click 'Next'.

Either type the SQL statement (SELECT...FROM...WHERE...) or use the 'Query Builder'. The WHERE statement will look at the foreign key, not the primary key. In the example below, of the 'Buildings' table, we would add a filter to the Query Builder against 'SideID' ( ' = @siteid ' ).


SELECT    BuildingID, BuildingName, SiteID
FROM      Buildings
WHERE     (SiteID = @siteid)

Name the Fill and Return functions, "FillBySiteID" and "GetBySiteID".

Click 'Finish'.

If using the worked example, complete the two foreign key functions for the 'Locations' table. You can skip the Query Builder and type the SQL statements in as shown below.

Name the functions appropriately ( "FillByBuildingID" and "GetByBuildingID""FillByLocationTypeID" and "GetByLocationTypeID".

SELECT    LocationID, LocationEasyName, LocationTypeID, BuildingID
FROM      Locations
WHERE     (BuildingID = @buildingid)
SELECT    LocationID, LocationEasyName, LocationTypeID, BuildingID
FROM      Locations
WHERE     (LocationTypeID = @locationtypeid)

The DataSet / DAL below shows which properties have foreign key constraints and underlines the functions created to filter by these fields.

Well done, you should now have a Data Set established as our Data Access Layer (DAL) and Table Adapters for each table that exists. The current setup will allow for the creation of basic list views (tables), form views with related lists (sub-tables), and the ability to add, delete and edit records from within the interface.

You are now ready to create an interface, such as a web page or Windows form, with dynamic data.