Export (0) Print
Expand All

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:

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, from the File menu, create a new Project.

  2. Name the project TableAdapterDbDirectMethodsWalkthrough.

  3. Select Windows Application and click OK. For more information, see Creating Windows-Based 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 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.

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

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

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:

Community Additions

Show:
© 2014 Microsoft