Save data with the TableAdapter DBDirect methods

 

For the latest documentation on Visual Studio 2017, see Save data with the TableAdapter DBDirect methods on docs.microsoft.com. 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:

In order to complete this walkthrough, you will need:

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 thenselectOK. For more information, see Client Applications.

    The TableAdapterDbDirectMethodsWalkthrough project is created and added to Solution Explorer.

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 about setting up the Northwind sample database, see How to: Install Sample Databases.

To create the data source

  1. On the Data menu, selectShow 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 Typescreen, select Database, and then selectNext.

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

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

  7. On the Choose your Database Objectsscreen, expand the Tables node.

  8. Select the Region table, and then selectFinish.

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

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.

    NameText
    InsertButtonInsert
    UpdateButtonUpdate
    DeleteButtonDelete

To add code to insert new records into the database

  1. SelectInsertButton 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);
            }
    

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

To add code to delete records from the database

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

To run the application

  • SelectF5 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.

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:

Save data back to the database

Show: