Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All
Important This document may not represent best practices for current development, links to downloads and other resources may no longer be valid. Current recommended version can be found here.

Walkthrough: Saving Data in a Transaction 

This walkthrough demonstrates how to save data in a transaction using the System.Transactions namespace. This example uses the Customers and Orders tables from the Northwind sample database.

This walkthrough requires access to the Northwind sample database. For information on setting up the Northwind sample database, see How to: Install Sample Databases.

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

  3. Select Windows Application and click OK. For more information, see Creating Windows-based Applications.

    The SavingDataInATransactionWalkthrough project is created and added to Solution Explorer.

This step uses the Data Source Configuration Wizard to create a data source based on the Customers and Orders tables in the Northwind sample database.

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 and create a connection to the Northwind database. 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 Customers and Orders tables, and then click Finish.

    The NorthwindDataSet is added to your project and the Customers and Orders tables appear in the Data Sources window.

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

  • Expand the Customers node in the Data Sources window.

  • Drag the main Customers node from the Data Sources window onto Form1.

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

  • Drag the related Orders node (the related child-table node below the Fax column, not the main Orders node) onto the form below the CustomersDataGridView.

    A DataGridView appears on the form. An OrdersTableAdapter and BindingSource appear in the component tray.

Transactions use the System.Transactions namespace. A project reference to the system.transactions assembly is not added by default, so you need to manually add it.

To add a reference to the System.Transactions DLL file

  1. From the Project menu, choose Add Reference.

  2. Select System.Transactions (on the .NET tab) and click OK.

    A reference to System.Transactions is added to the project.

By default, for the first table dropped onto your form, code is added to the click event of the save button on the BindingNavigator. You need to manually add code to update any additional tables. For this walkthrough, we refactor the existing save code out of the save button's click event handler and create a few more methods to provide specific update functionality based on whether the row needs to be added or deleted.

To modify the auto-generated save code

  1. Double-click the Save button on the CustomersBindingNavigator (the button with the floppy disk icon).

  2. Replace the CustomersBindingNavigatorSaveItem_Click method with the following code:

    private void customersBindingNavigatorSaveItem_Click(object sender, EventArgs e)
    {
        UpdateData();
    }
    
    private void UpdateData()
    {
        this.Validate();
        this.customersBindingSource.EndEdit();
        this.ordersBindingSource.EndEdit();
    
        using (System.Transactions.TransactionScope updateTransaction = 
            new System.Transactions.TransactionScope())
        {
            DeleteOrders();
            DeleteCustomers();
            AddNewCustomers();
            AddNewOrders();
    
            updateTransaction.Complete();
            northwindDataSet.AcceptChanges();
        }
    }
    
    
    private void customersBindingNavigatorSaveItem_Click(Object sender, System.EventArgs e)
    {
        UpdateData();
    }
    
    private void UpdateData()
    {
        this.Validate();
        this.customersBindingSource.EndEdit();
        this.ordersBindingSource.EndEdit();
    
        System.Transactions.TransactionScope updateTransaction = 
            new System.Transactions.TransactionScope();
        {
            DeleteOrders();
            DeleteCustomers();
            AddNewCustomers();
            AddNewOrders();
    
            updateTransaction.Complete();
            northwindDataSet.AcceptChanges();
        }
    }
    
    

The order for reconciling changes to related data is as follows:

  • Delete child records (in this case, delete records from the Orders table)

  • Delete parent records (in this case, delete records from the Customers table)

  • Insert parent records (in this case, insert records in the Customers table)

  • Insert child records (in this case, insert records in the Orders table)

To delete existing orders

  • Add the following DeleteOrders method to Form1:

    private void DeleteOrders()
    {
        NorthwindDataSet.OrdersDataTable deletedOrders;
        deletedOrders = (NorthwindDataSet.OrdersDataTable)
            northwindDataSet.Orders.GetChanges(DataRowState.Deleted);
    
        if (deletedOrders != null)
        {
            try
            {
                ordersTableAdapter.Update(deletedOrders);
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("DeleteOrders Failed");
            }
        }
    }
    
    
    private void DeleteOrders()
    {
        NorthwindDataSet.OrdersDataTable deletedOrders;
        deletedOrders = (NorthwindDataSet.OrdersDataTable)
            northwindDataSet.get_Orders().GetChanges(DataRowState.Deleted);
    
        if (deletedOrders != null)
        {
            try
            {
                northwindDataSetOrdersTableAdapter.Update(deletedOrders);
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("DeleteOrders Failed");
            }
        }
    }
    
    

To delete existing customers

  • Add the following DeleteCustomers method to Form1:

    private void DeleteCustomers()
    {
        NorthwindDataSet.CustomersDataTable deletedCustomers;
        deletedCustomers = (NorthwindDataSet.CustomersDataTable)
            northwindDataSet.Customers.GetChanges(DataRowState.Deleted);
    
        if (deletedCustomers != null)
        {
            try
            {
                customersTableAdapter.Update(deletedCustomers);
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("DeleteCustomers Failed");
            }
        }
    }
    
    
    private void DeleteCustomers()
    {
        NorthwindDataSet.CustomersDataTable deletedCustomers;
        deletedCustomers = (NorthwindDataSet.CustomersDataTable)
            northwindDataSet.get_Customers().GetChanges(DataRowState.Deleted);
    
        if (deletedCustomers != null)
        {
            try
            {
                northwindDataSetCustomersTableAdapter.Update(deletedCustomers);
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("DeleteCustomers Failed");
            }
        }
    }
    
    

To add new customers

  • Add the following AddNewCustomers method to Form1:

    private void AddNewCustomers()
    {
        NorthwindDataSet.CustomersDataTable newCustomers;
        newCustomers = (NorthwindDataSet.CustomersDataTable)
            northwindDataSet.Customers.GetChanges(DataRowState.Added);
    
        if (newCustomers != null)
        {
            try
            {
                customersTableAdapter.Update(newCustomers);
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("AddNewCustomers Failed");
            }
        }
    }
    
    
    private void AddNewCustomers()
    {
        NorthwindDataSet.CustomersDataTable newCustomers;
        newCustomers = (NorthwindDataSet.CustomersDataTable)
            northwindDataSet.get_Customers().GetChanges(DataRowState.Added);
    
        if (newCustomers != null)
        {
            try
            {
                northwindDataSetCustomersTableAdapter.Update(newCustomers);
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("AddNewCustomers Failed");
            }
        }
    }
    
    

To add new orders

  • Add the following AddNewOrders method to Form1:

    private void AddNewOrders()
    {
        NorthwindDataSet.OrdersDataTable newOrders;
        newOrders = (NorthwindDataSet.OrdersDataTable)
            northwindDataSet.Orders.GetChanges(DataRowState.Added);
    
        if (newOrders != null)
        {
            try
            {
                ordersTableAdapter.Update(newOrders);
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("AddNewOrders Failed");
            }
        }
    }
    
    
    private void AddNewOrders()
    {
        NorthwindDataSet.OrdersDataTable newOrders;
        newOrders = (NorthwindDataSet.OrdersDataTable)
            northwindDataSet.get_Orders().GetChanges(DataRowState.Added);
    
        if (newOrders != null)
        {
            try
            {
                northwindDataSetOrdersTableAdapter.Update(newOrders);
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("AddNewOrders Failed");
            }
        }
    }
    
    

To run the application

  • Press F5 to run the application.

Community Additions

ADD
Show:
© 2015 Microsoft