Save data with the TableAdapter DBDirect methods

Note

This article applies to Visual Studio 2015. If you're looking for the latest Visual Studio documentation, see Visual Studio documentation. We recommend upgrading to Visual Studio 2019. Download it here

This walkthrough provides detailed instructions for running SQL statements directly against a database by using the DBDirect methods of a TableAdapter. The DBDirect methods of a TableAdapter provide a fine level of control over your database updates. You can use them to run 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:

  • Access to the Northwind sample database.

Create a Windows application

The first step is to create a Windows Application.

To create the new Windows project

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

  2. Name the project TableAdapterDbDirectMethodsWalkthrough.

  3. Select Windows Application, and then select OK. For more information, see Client Applications.

    The TableAdapterDbDirectMethodsWalkthrough project is created and added to Solution Explorer.

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

To create the data source

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

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

  3. On the Choose a Data Source Type screen, select Database, and then select Next.

  4. On the Choose your Data Connection screen, 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.

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

  6. On the Save connection string to the Application Configuration file screen, select Next.

  7. On the Choose your Database Objects screen, expand the Tables node.

  8. Select the Region table, and then select Finish.

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

Addcontrols 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. Select 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 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);
    }
    
    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
    

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 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();
    }
    
    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
    

To add code to delete records from the database

  1. Select 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 void DeleteButton_Click(object sender, EventArgs e)
    {
        try
        {
            regionTableAdapter1.Delete(5, "Updated Region Description");
        }
        catch (Exception ex)
        {
            MessageBox.Show("Delete Failed");
        }
        RefreshDataset();
    }
    
    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
    

Run the application

To run the application

  • Select F5 to run the application.

  • Select the Insert button, and verify that the new record appears in the grid.

  • Select the Update button, and verify that the record is updated in the grid.

  • Select 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 might 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.

See Also

Save data back to the database