DAL Filter Functions by Primary Key (ID)

In the previous tutorial we created a Data Set to act as a Data Access Layer and created Table Adapters to return a full list of records for our specified tables.

This is useful for lists of records, but we will also need to produce forms that display a single record.

Here we will add functions to our Table Adapters to allow for a single record to be selected form a table based on its primary key (SQL statements with 'SELECT ... FROM ... WHERE ...').

These functions will be useful for displaying individual records on a page or form.

Opening your Data Set, created in the prior tutorial, we should see all the Table Adapters we created. Where we see the functions, "Fill, Get ()", we can right-click and select 'Add Query...'.

As with previous TableAdapters we can select to 'Use SQL Statements' and select 'Next'.

We have the additional option now to choose a query type. A simple 'SELECT which returns rows' is required as we will add filters to select only one row.

You can type your SQL statement directly, and can even choose to copy and paste the statement from the original function to start from (don't click 'Next' yet).

Alternatively, use the 'Query Builder'.

If using the Query Builder, ensure you have the correct table added and fields selected. Select each individual field to have them show in the table below.

In the primary key row, under the heading 'Filter' type, "= @siteid" (all lowercase - its useful to simply use the field name but entirely in lowercase).

The @ symbol denotes the use of a parameter and what follows is your chosen identifier for that parameter. For this function to work, the value of 'siteid' will have to be passed in and programmers will be prompted by Visual Studio's Intellisense. 

If you have data to test with, you may 'Execute Query' to check it pulls in one row.

Click 'OK'.

If you used the Query Builder you will have your SQL adapted automatically. Alternatively you can simple add a 'WHERE' statement yourself to a copy of the SELECT...FROM statements used in the queries of the previous tutorial.

Click 'Next'.

With both 'Fill a DataTable' and 'Return a DataTable' selected, change the method names to be 'FillByID' and 'GetByID' respectively.

Click 'Next' or 'Finish'.

If you clicked 'Next' on the last page, review the results and click 'Finish'.

Your new functions should be added below the previous ones for that Table Adapter.

Moving on to another table, 'LocationTypes' in our example, repeat the same process.

Navigate to the TableAdapter functions, right-click, 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'.

If using the Query Builder, select each individual field (instead of checking '* (All Columns)' .)

Type a filter next to the primary key, in this example '= @locationtypeid'.

Check using 'Execute Query' or otherwise click 'OK'.

Name both the Fill and Return functions as earlier, 'FillByID' and 'GetByID'. Click 'Next'.

Review the results and click 'Finish'.

Continue to add functions to each of your tables where you may need to return individual records. If following the tutorial with the 'AssetManagementSystem' example, then try to create the functions for 'Buildings' and 'Locations'.

A full list of the SQL statements for the worked example are provided below. 

SELECT    SiteID, [Address 1], SiteName, [Address 2], City, Status, PostCode
FROM      Site
WHERE     (SiteID = @siteid)
SELECT    LocationTypeID, LocationTypeCaption
FROM      LocationTypes
WHERE    (LocationTypeID = @locationtypeid)
SELECT    BuildingID, BuildingName, SiteID
FROM      Buildings
WHERE    (BuildingID = @buildingid)
SELECT    LocationID, LocationEasyName, LocationTypeID, BuildingID
FROM      Locations
WHERE    (LocationID = @locationid)

Well done on editing your Table Adapters to include SELECT...FROM...WHERE... queries to produce individual records based on the primary key.

The next step for your DAL is to SELECT records based on a foreign key. This is useful for pulling in lists of related records in a different table, for a given record (such as all the patients for a given doctor, or all the buildings at a given site)