ADO.NET Cookbook

6.2. Using Manual Transactions

Problem

You need to explicitly begin, control, and end a transaction within a .NET application.

Solution

Use the Connection object with structured exceptions (try . . . catch . . . finally).

The sample code contains two event handlers:

Form.Load

Sets up the sample by filling a DataTable with the Categories table from the Northwind sample database. The default view of the table is bound to a data grid on the form.

Insert Button.Click

Inserts user-entered data for two Categories records into the Northwind database within a manual transaction. If either record insert fails, both inserts are rolled back; otherwise, both record inserts are committed.

The C# code is shown in Example 6-3.

Example 6-3. File: ManualTransactionForm.cs

// Namespaces, variables, and constants using System; using System.Configuration; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; private const String CATEGORIES_TABLE = "Categories"; private DataTable dt; private SqlDataAdapter da; // . . . private void ManualTransactionForm_Load(object sender, System.EventArgs e) { // Fill the categories table. String sqlText = "SELECT CategoryID, CategoryName, " + "Description FROM Categories"; da = new SqlDataAdapter(sqlText, ConfigurationSettings.AppSettings["Sql_ConnectString"]); dt = new DataTable(CATEGORIES_TABLE); da.FillSchema(dt, SchemaType.Source); da.Fill(dt); // Bind the default view of the table to the grid. dataGrid.DataSource = dt.DefaultView; } private void ...