DAL Insert & Update Functions

Here we will create the more straightforward INSERT and UPDATE methods for our data access layer to add and edit record.

Firstly navigate to your typed dataset, our data access layer. 

Whilst its not something we are using in this series of tutorials, you can use the 'Generate Insert, Update and Delete statements' option to create some generic commands.

To do this, right-click on the TableAdapter of choice ('SiteTableAdapter' shown below) and select 'Configure...'.

On the 'Enter a SQL Statement' screen select 'Advanced Options'.

Choose 'Generate Insert, Update and Delete statements'.

Select 'OK'.

Select 'Finish':

You should notice the TableAdapter's properties now have items under 'DeleteCommand', 'InsertCommand' and 'UpdateCommand'. These can be opened to view the SQL statements themselves:

The series of tutorials here make use of our own methods as they give us greater flexibility.

To start, right-click on the TableAdapter of choice ('SiteTableAdapter' shown below) and select 'Add Query...'.

Through the wizard select:

'Use SQL Statements', then click 'Next',

'INSERT', then click 'Next',

And write your INSERT statement or use the Query Builder, then click 'Next'.

Its important to note the SQL statement we use here assumes the primary key (SiteID in the example) is determined by the application, perhaps by the user. It is known and set in this statement, causing an error if the ID is already in use. 

Alternatively you may have auto-generating unique identifiers, in which case, do NOT include the ID in the statement and let the database take care of that for you. In this situation your application may need the newly generated ID to redirect the user to the new record's page or to implement follow-up SQL statements. This is covered in a later tutorial where we create an INSERT statement that returns the unique ID.

The SQL statement used for the 'Site' Table is shown below:

INSERT INTO [Site] ([SiteID], [SiteName], [Status], [Address 1], [Address 2], [City], [PostCode]) 
VALUES (@SiteID, @SiteName, @Status, @Address_1, @Address_2, @City, @PostCode)

Name it appropriately, we have called it 'InsertRecord'.

Repeat the process for an UPDATE statement, adding a new query to the table adapter:

'Use SQL Statements', then click 'Next',

'UPDATE', then click 'Next',

And write your UPDATE statement or use the Query Builder, then click 'Next'.

Again we assume the primary key was not created by the database and is something we can edit. If instead the unique identifier is automatically generated, then remove it from the UPDATE statement.

The SQL statement used for the 'Site' Table is shown below:

UPDATE [Site] 
  SET 
    [SiteID] = @SiteID, 
    [SiteName] = @SiteName, 
    [Address 1] = @Address_1, 
    [Address 2] = @Address_2, 
    [City] = @City, 
    [PostCode] = @PostCode, 
    [Status] = @Status 
  WHERE ([SiteID] = @Original_SiteID)

Name it appropriately, we have called it 'UpdateRecord'.

There we have InsertRecord and UpdateRecord methods, but please do remember to make appropriate adjustments for auto-generated primary keys.

A later tutorial covers another version of an Insert method that returns the ID of an auto-generated primary key.

Only include the primary key in the UPDATE statement if it is editable, otherwise (and preferably) leave it out.