ADO.NET 3.5 Cookbook, 2nd Edition

7.2. Using Manual Transactions

Problem

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

Solution

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

The solution uses a table named ManualTransaction in the AdoDotNet35Cookbook database. Execute the T-SQL in Example 7-2 to create the table.

Example 7-2. Create table ManualTransaction

USE AdoDotNet35Cookbook
GO
CREATE TABLE ManualTransaction (
    Id int NOT NULL PRIMARY KEY,
    Field1 nvarchar(50) NULL,
    Field2 nvarchar(50) NULL )

The solution starts a transaction, executes two commands that successfully insert records into the ManualTransaction table, and commits the transaction within a try block. Next, the solution starts a transaction and executes two commands that insert records into the ManualTransaction table. The second insert fails because the Id value is null and the transaction is rolled back within a catch block. The contents of the ManualTransaction table are output to the console after both transactions are completed.

The C# code in Program.cs in the project ManualTransaction is shown in Example 7-3.

Example 7-3. File: Program.cs for ManualTransaction solution

using System; using System.Data; using System.Data.SqlClient; namespace ManualTransaction { class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdoDotNet35Cookbook;"; string sqlSelect = "SELECT * FROM ManualTransaction"; string ...