Walkthrough: Saving Data with the TableAdapter DBDirect Methods

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:

Prerequisites

In order to complete this walkthrough, you will need:

Creating a Windows Application

The first step is to create a Windows Application.

To create the new Windows project

  1. In Visual Studio, from the File menu, create a new Project.

  2. Name the project TableAdapterDbDirectMethodsWalkthrough.

  3. Select Windows Application and click OK. For more information, see Developing Client Applications.

    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

  1. On the Data menu, click Show Data Sources.

  2. In the Data Sources window, select Add New Data Source to start the Data Source Configuration Wizard.

  3. Select Database on the Choose a Data Source Type page, and then click Next.

  4. 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. For more information, see Add/Modify Connection Dialog Box (General).

  5. If your database requires a password, select the option to include sensitive data, and then click Next.

  6. Click Next on the Save connection string to the Application Configuration file page.

  7. Expand the Tables node on the Choose your Database Objects page.

  8. 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

To add buttons that will call the individual TableAdapter DbDirect methods

  1. Drag three Button controls from the Toolbox onto Form1 (below the RegionDataGridView).

  2. Set the following Name and Text properties on each button.

    Name

    Text

    InsertButton

    Insert

    UpdateButton

    Update

    DeleteButton

    Delete

To add code to insert new records into the database

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

  2. 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

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

  2. 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

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

  2. 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:

See Also

Tasks

How to: Directly Access the Database with a TableAdapter

How to: Create TableAdapter Queries

How to: Save Data from an Object to a Database

Concepts

TableAdapter Overview

Binding Windows Forms Controls to Data in Visual Studio

Preparing Your Application to Receive Data

Fetching Data into Your Application

Binding Controls to Data in Visual Studio

Editing Data in Your Application

Validating Data

Saving Data

Other Resources

Connecting to Data in Visual Studio