Walkthrough: Saving Data with the TableAdapter DBDirect Methods

Article
04/28/2015

This walkthrough provides detailed instructions for executing SQL statements directly against a database using the DBDirect methods of a TableAdapter. DBDirect methods of a TableAdapter provide a fine level of control over your database updates. With them you can execute specific SQL statements and stored procedures by calling the individual Insert, Update, and Delete methods as needed by your application (as opposed to the overloaded Update method that performs the UPDATE, INSERT, and DELETE statements all in one call).

During this walkthrough, you will learn how to:

Create a new Windows Application.
Create and configure a dataset with the Data Source Configuration Wizard.
Select the control to be created on the form when dragging items from the Data Sources window. For more information, see How to: Set the Control to be Created when Dragging from the Data Sources Window.
Create a data-bound form by dragging items from the Data Sources window onto the form.
Add methods to directly access the database and perform inserts, updates, and deletes directly against the database.

Prerequisites

In order to complete this walkthrough, you will need:

Access to the Northwind sample database. For more information, see How to: Install Sample Databases.

Creating a Windows Application

The first step is to create a Windows Application.

To create the new Windows project

In Visual Studio, from the File menu, create a new Project.
Name the project TableAdapterDbDirectMethodsWalkthrough.
Select Windows Application and click OK. For more information, see Developing Client Applications with the .NET Framework.

The TableAdapterDbDirectMethodsWalkthrough project is created and added to Solution Explorer.

Creating a Data Source from Your Database

This step uses the Data Source Configuration Wizard to create a data source based on the Region table in the Northwind sample database. You must have access to the Northwind sample database to create the connection. For information on setting up the Northwind sample database, see How to: Install Sample Databases.

To create the data source

On the Data menu, click Show Data Sources.
In the Data Sources window, select Add New Data Source to start the Data Source Configuration Wizard.
Select Database on the Choose a Data Source Type page, and then click Next.
On the Choose your Data Connection page do one of the following:
- If a data connection to the Northwind sample database is available in the drop-down list, select it.
  
  -or-
- Select New Connection to launch the Add/Modify Connection dialog box.
If your database requires a password, select the option to include sensitive data, and then click Next.
Click Next on the Save connection string to the Application Configuration file page.
Expand the Tables node on the Choose your Database Objects page.
Select the Region table, and then click Finish.

The NorthwindDataSet is added to your project and the Region table appears in the Data Sources window.

Adding Controls to the Form to Display the Data

Create the data-bound controls by dragging items from the Data Sources window onto your form.

To create data bound controls on the Windows Form

Drag the main Region node from the Data Sources window onto the form.

A DataGridView control and a tool strip (BindingNavigator) for navigating records appear on the form. A NorthwindDataSet, RegionTableAdapter, BindingSource, and BindingNavigator appear in the component tray.

To add buttons that will call the individual TableAdapter DbDirect methods

Drag three Button controls from the Toolbox onto Form1 (below the RegionDataGridView).
Set the following Name and Text properties on each button.

Name

Text

InsertButton

Insert

UpdateButton

Update

DeleteButton

Delete

Name	Text
InsertButton	Insert
UpdateButton	Update
DeleteButton	Delete

To add code to insert new records into the database

Double-click the InsertButton to create an event handler for the click event and open your form in the code editor.

Replace the InsertButton_Click event handler with the following code:

Private Sub InsertButton_Click() Handles InsertButton.Click

    Dim newRegionID As Integer = 5
    Dim newRegionDescription As String = "NorthEastern" 

    Try
        RegionTableAdapter1.Insert(newRegionID, newRegionDescription)

    Catch ex As Exception
        MessageBox.Show("Insert Failed")
    End Try

    RefreshDataset()
End Sub 


Private Sub RefreshDataset()
    Me.RegionTableAdapter1.Fill(Me.NorthwindDataSet1._Region)
End Sub

private void InsertButton_Click(object sender, EventArgs e)
{
    Int32 newRegionID = 5;
    String newRegionDescription = "NorthEastern";

    try
    {
        regionTableAdapter1.Insert(newRegionID, newRegionDescription);
    }
    catch (Exception ex)
    {
        MessageBox.Show("Insert Failed");
    }
    RefreshDataset();
}


private void RefreshDataset()
{
    this.regionTableAdapter1.Fill(this.northwindDataSet1.Region);
}

To add code to update records in the database

Double-click the UpdateButton to create an event handler for the click event and open your form in the code editor.

Replace the UpdateButton_Click event handler with the following code:

Private Sub UpdateButton_Click() Handles UpdateButton.Click

    Dim newRegionID As Integer = 5

    Try
        RegionTableAdapter1.Update(newRegionID, "Updated Region Description", 5, "NorthEastern")

    Catch ex As Exception
        MessageBox.Show("Update Failed")
    End Try

    RefreshDataset()
End Sub

private void UpdateButton_Click(object sender, EventArgs e)
{
    Int32 newRegionID = 5;

    try
    {
        regionTableAdapter1.Update(newRegionID, "Updated Region Description", 5, "NorthEastern");
    }
    catch (Exception ex)
    {
        MessageBox.Show("Update Failed");
    }
    RefreshDataset();
}

To add code delete records from the database

Double-click the DeleteButton to create an event handler for the click event and open your form in the code editor.

Replace the DeleteButton_Click event handler with the following code:

Private Sub DeleteButton_Click() Handles DeleteButton.Click

    Try
        RegionTableAdapter1.Delete(5, "Updated Region Description")

    Catch ex As Exception
        MessageBox.Show("Delete Failed")
    End Try

    RefreshDataset()
End Sub

private void DeleteButton_Click(object sender, EventArgs e)
{
    try
    {
        regionTableAdapter1.Delete(5, "Updated Region Description");
    }
    catch (Exception ex)
    {
        MessageBox.Show("Delete Failed");
    }
    RefreshDataset();
}

Running the Application

To run the application

Press F5 to run the application.
Click the Insert button and verify that the new record appears in the grid.
Click the Update button and verify that the record is updated in the grid.
Click the Delete button and verify that the record is removed from the grid.

Next Steps

Depending on your application requirements, there are several steps you may want to perform after creating a data-bound form. Some enhancements you could make to this walkthrough include:

Adding search functionality to the form. For more information, see How to: Add a Parameterized Query to a Windows Forms Application.
Adding additional tables to the dataset by selecting Configure DataSet with Wizard from within the Data Sources window. You can add controls that display related data by dragging the related nodes onto the form. For more information, see How to: Display Related Data in a Windows Forms Application.

Share via