title | description | ms.date | ms.topic | dev_langs | helpviewer_keywords | author | ms.author | manager | ms.subservice | |||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Walkthrough: Save data in a transaction in an ADO.NET application (.NET Framework) |
In this walkthrough about .NET Framework application development in Visual Studio, see how to save data in a transaction by using the System.Transactions namespace. |
09/08/2017 |
how-to |
|
|
ghogen |
ghogen |
mijacobs |
data-tools |
[!INCLUDE Data access tech note]
This walkthrough demonstrates how to save data in a transaction by using the xref:System.Transactions namespace. In this walkthrough, you'll create a Windows Forms application. You'll use the Data Source Configuration Wizard to create a dataset for two tables in the Northwind sample database. You'll add data bound controls to a Windows form, and you'll modify the code for the BindingNavigator's save button to update the database inside a TransactionScope.
To complete this tutorial, you need the .NET desktop development and Data storage and processing workloads installed in Visual Studio. To install them, open Visual Studio Installer and choose Modify (or More > Modify) next to the version of Visual Studio you want to modify. See Modify Visual Studio.
This walkthrough uses SQL Server Express LocalDB and the Northwind sample database.
-
If you don't have SQL Server Express LocalDB, install it either from the SQL Server Express download page, or through the Visual Studio Installer. In the Visual Studio Installer, SQL Server Express LocalDB can be installed as part of the .NET desktop development workload, or as an individual component.
-
Install the Northwind sample database by following these steps:
-
In Visual Studio, open the SQL Server Object Explorer window. (SQL Server Object Explorer is installed as part of the Data storage and processing workload in the Visual Studio Installer.) Expand the SQL Server node. Right-click on your LocalDB instance and select New Query.
A query editor window opens.
-
Copy the Northwind Transact-SQL script to your clipboard. This T-SQL script creates the Northwind database from scratch and populates it with data.
-
Paste the T-SQL script into the query editor, and then choose the Execute button.
After a short time, the query finishes running and the Northwind database is created.
-
The first step is to create a Windows Forms App (.NET Framework).
-
In Visual Studio, on the File menu, select New > Project.
-
Expand either Visual C# or Visual Basic in the left-hand pane, then select Windows Desktop.
-
In the middle pane, select the Windows Forms App project type.
-
Name the project SavingDataInATransactionWalkthrough, and then choose OK.
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 open the Data Sources window, on the Data menu, select Show Data Sources.
-
In the Data Sources window, select Add New Data Source to start the Data Source Configuration Wizard.
-
On the Choose a Data Source Type screen, select Database, and then select Next.
-
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 dropdown list, select it.
-or-
- Select New Connection to launch the Add/Modify Connection dialog box and create a connection to the Northwind database.
-
If your database requires a password, select the option to include sensitive data, and then select Next.
-
On the Save connection string to the Application Configuration file screen, select Next.
-
On the Choose your Database Objects screen, expand the Tables node.
-
Select the
Customers
andOrders
tables, and then select Finish.The NorthwindDataSet is added to your project and the
Customers
andOrders
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.
-
In the Data Sources window, expand the Customers node.
-
Drag the main Customers node from the Data Sources window onto Form1.
A xref:System.Windows.Forms.DataGridView control and a tool strip (xref:System.Windows.Forms.BindingNavigator) for navigating records appear on the form. A NorthwindDataSet,
CustomersTableAdapter
, xref:System.Windows.Forms.BindingSource, and xref:System.Windows.Forms.BindingNavigator appear in the component tray. -
Drag the related Orders node (not the main Orders node, but the related child-table node below the Fax column) onto the form below the CustomersDataGridView.
A xref:System.Windows.Forms.DataGridView appears on the form. An
OrdersTableAdapter
and xref:System.Windows.Forms.BindingSource appear in the component tray.
Transactions use the xref:System.Transactions namespace. A project reference to the system.transactions assembly is not added by default, so you need to manually add it.
-
On the Project menu, select Add Reference.
-
Select System.Transactions (on the
.NET
tab), and then select OK.A reference to System.Transactions is added to the project.
For the first table dropped onto your form, code is added by default to the click
event of the save button on the xref:System.Windows.Forms.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. We also create a few more methods to provide specific update functionality based on whether the row needs to be added or deleted.
-
Select the Save button on the CustomersBindingNavigator (the button with the floppy disk icon).
-
Replace the
CustomersBindingNavigatorSaveItem_Click
method with the following code::::code language="csharp" source="../snippets/csharp/VS_Snippets_VBCSharp/VbRaddataSaving/CS/Form2.cs" id="Snippet4":::
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.)
-
Add the following
DeleteOrders
method to Form1::::code language="csharp" source="../snippets/csharp/VS_Snippets_VBCSharp/VbRaddataSaving/CS/Form2.cs" id="Snippet5":::
-
Add the following
DeleteCustomers
method to Form1::::code language="csharp" source="../snippets/csharp/VS_Snippets_VBCSharp/VbRaddataSaving/CS/Form2.cs" id="Snippet6":::
-
Add the following
AddNewCustomers
method to Form1::::code language="csharp" source="../snippets/csharp/VS_Snippets_VBCSharp/VbRaddataSaving/CS/Form2.cs" id="Snippet7":::
-
Add the following
AddNewOrders
method to Form1::::code language="csharp" source="../snippets/csharp/VS_Snippets_VBCSharp/VbRaddataSaving/CS/Form2.cs" id="Snippet8":::
Press F5 to run the application.