DAL Filter Parameters

To allow for search filters for the user we will have to adapt the 'Get' function, or create a new function, to allow for parameterised querying of the tables.

This tutorial will customise the TableAdapter to allow for these user-defined queries..

Start by accessing our Data Access Layer (DAL). We will follow the same procedures we did to create the original 'Get' function, but this time we will add parameters to the query for user-defined filters.

In the DAL, from the 'Site' table, right-click on the 'SiteTableAdapter' and select 'Add Query'.

Assuming you establish the same SELECT FROM statement created in the 'Get()' function we can now add a WHERE command to parameterise the query to only select those with a 'SiteID' that matches a user-defined parameter that we will call '@SiteID'.

WHERE - filters the results of the query based on conditions that follow.

[SiteID] - the name of the field that is the focus of our filter. Square brackets are only necessary if spaces appear in the name, but you can get in the habit of always using them if you wish.

LIKE - commands the WHERE statement to compare for an exact match (subject to the use of wildcards)

'%' - The single quotations allow contents to be interpreted as a string. The percent-sign is a wildcard that matches zero to many characters. Conversely an underscore '_' matches a single (one) character. We use the % wildcard either side of the parameter to return any field that contains the values searched for. Without them, an exact match would have to be found.

+ - the plus-sign is used to append two strings together, in this case a parameter is attached to preceding and succeeding wildcards.

@SiteID - this is the name of the parameter (often the same as the column/field name with a @ at the beginning.

OR - allows for either side of the operator to produce positive results

[SiteID] IS NULL - checks if the stated column/field contains no value, returning TRUE if it is empty.

Every field that is to be parameterised needs adding to the WHERE statement through the use of 'AND' operators.

You should have something like this:

Name the function appropriately, such as 'FillByParameters' and 'GetByParameters'.

There we have it, a new function to allow for parameterised querying of our Site table. Consider adapting all tables where you may like the user to be able to filter and search tables of records.