DAL Creation (TableAdapters)

Once you have a database ready to create connections to it and pull the data into your interface. Setting up a Data Access Layer (DAL) will allow you to establish how all the SQL commands will operate and separate this out from the interface, simplifying the code behind your forms and improving security.

A 'strongly typed' Data Set will be used as our DAL, providing a visual interface and wizards for the developer.

We will add Table Adapters to the Data Set with functions to return all the records in specified tables (SQL statements with 'SELECT * FROM ... ').

With the project open, navigate to the Solution Explorer and right-click on the project file, select 'Add', select 'New Item...'.

From the dialogue box that opens, use the left pane to select the 'Data' category under your programming language of choice (C# in the example below).

Select 'DataSet' from the list.

Name the data set (it will have a .xsd extension).

A new tab will open with the blank data set. Right-click in an empty area of the data set tab, select 'Add', then select 'TableAdapter...'.

If you have followed previous tutorials for creating the database in Visual Studio then you should be able to select the connection from the drop-down list (default name is 'DefaultConnection' though this can be changed. If it is not listed, you can use the 'New Connection' button, otherwise select 'Next'.

When you create a database in Visual Studio it saves in a system default location (such as Documents) and as such you could choose to copy the file to your project and modify the connection when prompted. Select 'Yes'.

If prompted that the database already exists in the project, and assuming it should be a fresh database anyway, click 'Yes' to overwrite.

Choose to use SQL statements to access the database. Stored procedures work in a similar manner but the SQL statements are stored with the database directly. Select 'Next'.

You can now enter a SQL statement to select information from a specified database table to store in a DataTable within the solution. It is advisable to use the 'Query Builder' to ensure SQL statements are free of syntax errors.

A simple SELECT statement is all that is needed to have INSERT, UPDATE and DELETE statements automatically generated.

Using the Query Builder rather than typing the SQL statement directly allows you to select a specific table from those available in the database. In the example below we have selected the 'Site' table (created in previous tutorials) and clicked 'Add' (or double-clicked table name) and then 'Close'.

By default, all columns will be pulled in and you can review, or amend directly, the SQL code below. It can be useful to select the fields directly in order to sort, filter or change names of individual items. 

Use the 'Alias' column to provide an alternative name for fields. Note when naming fields these are not the labels displayed to users, so we prefer to avoid spaces. The example below include two fields that use spaces so you can see the effect on the SQL statement. The use of square brackets, e.g. [field name], are used to encase all fields that use spaces but may be left out for continuous names.

We will visit filtering later.

If there is data in the database you can test the statement pulls in the right data by selecting 'Execute Query' and reviewing the result.

If you are happy with the data pulled in by the generated SQL statement then select 'OK'.

Following the Query Builder you will be brought back to the SQL Statement section of the Configuration Wizard. Of course you could have typed it directly yourself.

Select 'Next'.

Check 'Generate Insert, Update and Delete statements' to have these automatically generated and select 'OK'.

Select both 'Fill' and 'Return' functions. The former will default name to 'Fill' and can be left, but with the latter we changed the name simply to 'Get' instead of 'GetData'.

On the summary page, click 'Finish'.

Your TableAdapter will be added to your DataSet (DAL) with the functions listed below.

We can repeat this process for other tables. Right-click in a clear area within the DataSet, select 'Add' and 'TableAdapter...".

Select the data connection from the list and click 'Next'.

Choose 'Use SQL statements' and click 'Next'.

Either enter your SQL Statement or select 'Query Builder'.

Select a table from the list of those in the database. In our example we have selected 'LocationTypes' using 'Add' and then 'Close'.

We have selected each column individually as we plan to edit or filter some fields later. Select 'OK'.

Back in the Configuration Wizard, click 'Next'.

Ensure check-boxes are checked and names are as intended (we use 'Fill' and 'Get' respectively). Clic'Next'.

On summary page select 'Finish'.

You will notice foreign key relationships are shown in the DataSet (DAL).

If you are following this tutorial with the AssetManagementSystem solution used here, then also establish TableAdapters 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
SELECT    LocationTypeID, LocationTypeCaption
FROM      LocationTypes
SELECT    BuildingID, BuildingName, SiteID
FROM      Buildings
SELECT    LocationID, LocationEasyName, LocationTypeID, BuildingID
FROM      Locations

Well done on creating a DAL with your first Table Adapters and SELECT queries. These can be used to create lists of records to display to the user.

The next step for your DAL is to SELECT individual records based on their primary key(s).