Transactions Tutorial

This tutorial shows in a simple Cω program how to work with SQL transactions.

Sample Files

To run this tutorial, you may use the following project and source files:

Transaction.cwproj
main.cw
Transaction.exe.config

These files are located in the \samples\SQL\Transaction subdirectory under the path where you installed Cω, which by default is C:\Program Files\Microsoft Research\Comega.

To run this sample, you need to have a SQL configuration file in the same directory as the Select.exe you make in Visual Studio. Here are the contents of the version of this file that Cω provided you as a starting point:

<configuration>
  <appSettings>
    <add key="Northwind" value="Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI" />
  </appSettings>
</configuration>

Verify that this is the correct SQL connection string for connecting to the Northwind database on your SQL Server 2000 installation. If necessary, you can modify the value attribute in the <add> element

When you have verified it is correct, you can open the Cω project (.cwproj) file in Visual Studio and then press F5 to compile and run the sample code.

Example

The following is a complete program that demonstrates transaction support in Cω.

using System;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Northwind;

/// <summary>
/// This sample illustrates how to use the database transact syntax
///
///   transact( database [, isolation-level] ) {
///      ...
///      [commit]    // commit keyword commits transaction and jump to commit block.
///      [rollback]  // rollback keyword rolls back transaction and jumps to rollback block.
///      ...
///   }
///   [commit {statements}]   // optional
///   [rollback {statements}] // optional
///
/// To run this sample, check the Transactions.exe.config file to make sure
/// the Northwind database connection string is correct then hit F5.
/// </summary>

public class Test {
  public static void Main( string[] args ) {
    int id = (args.Length > 0) ? Int32.Parse(args[0]) : 7;

    // create a new instance of a northwind database object.
    Database dbo = new Database();

    // the transaction context is tied to the instance of the database object
    transact(dbo) {
      Console.WriteLine("Before Insert");
      foreach (r in select RegionID, RegionDescription from dbo.Region order by RegionID) {
        Console.WriteLine("ID: {0}, description: {1} " , r.RegionID, r.RegionDescription);
      }

      insert [RegionID=id, RegionDescription="test-region"] into dbo.Region;

      Console.WriteLine("\nAfter Insert");
      foreach (r in select RegionID, RegionDescription from dbo.Region order by RegionID) {
        Console.WriteLine("ID: {0}, description: {1} " , r.RegionID, r.RegionDescription);
      }
      // don't commit these sample changes!
      rollback;
    }
    commit {
      Console.WriteLine("\nTransaction committed");
    }
    rollback {
      Console.WriteLine("\nTransaction rolled back");
    }

    Console.WriteLine("\nAfter transaction");
    foreach (r in select RegionID, RegionDescription from dbo.Region order by RegionID) {
      Console.WriteLine("ID: {0}, description: {1} " , r.RegionID, r.RegionDescription);
    }

    Console.WriteLine("\nPress ENTER To continue...");
    Console.ReadLine();
   }
}

Output

Before Insert
ID: 1, description: Eastern
ID: 2, description: Western
ID: 3, description: Northern
ID: 4, description: Southern
ID: 5, description: Northeast

After Insert
ID: 1, description: Eastern
ID: 2, description: Western
ID: 3, description: Northern
ID: 4, description: Southern
ID: 5, description: Northeast
ID: 7, description: test-region

Transaction rolled back

After transaction
ID: 1, description: Eastern
ID: 2, description: Western
ID: 3, description: Northern
ID: 4, description: Southern
ID: 5, description: Northeast

Press ENTER To continue...

Transactions Tutorial

Sample Files

Further Reading

Example

Output