illustrates the use of transactions : Transactions : Database C# Source Code

    

/*
Mastering Visual C# .NET
by Jason Price, Mike Gunderloy

Publisher: Sybex;
ISBN: 0782129110
*/

/*
  Example23_4.cs illustrates the use of transactions
*/

using System;
using System.Data;
using System.Data.SqlClient;

public class Example23_4
{

  public static void Main()
  {

    // formulate a string containing the details of the
    // database connection
    string connectionString =
      "server=localhost;database=Northwind;uid=sa;pwd=sa";

    // create a SqlConnection object to connect to the
    // database, passing the connection string to the constructor
    SqlConnection mySqlConnection =
      new SqlConnection(connectionString);

    // open the database connection using the
    // Open() method of the SqlConnection object
    mySqlConnection.Open();

    // step 1: create a SqlTransaction object and start the transaction
    // by calling the BeginTransaction() method of the SqlConnection
    // object
    SqlTransaction mySqlTransaction =
      mySqlConnection.BeginTransaction();

    // step 2: create a SqlCommand object to hold a SQL statement
    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();

    // step 3: set the Transaction property for the SqlCommand object
    mySqlCommand.Transaction = mySqlTransaction;

    // step 4: formulate a string containing the first INSERT statement
    string insertString =
      "INSERT INTO Customers (" +
      "  CustomerID, CompanyName, ContactName, Address" +
      ") VALUES (" +
      "  'T2COM', 'T2 Company', 'Jason Price', '1 Main Street'" +
      ")";

    // step 5: set the CommandText property of the SqlCommand object to
    // the INSERT string
    mySqlCommand.CommandText = insertString;

    // step 6: run the first INSERT statement
    Console.WriteLine("Running first INSERT statement");
    mySqlCommand.ExecuteNonQuery();

    // step 7: formulate a second INSERT statement
    insertString =
      "INSERT INTO Orders (" +
      "  CustomerID" +
      ") VALUES (" +
      "  'T2COM'" +
      ")";

    // step 8: set the CommandText property of the SqlCommand object to
    // the second INSERT string
    mySqlCommand.CommandText = insertString;

    // step 9: run the second INSERT statement
    Console.WriteLine("Running second INSERT statement");
    mySqlCommand.ExecuteNonQuery();

    // step 10: commit the transaction using the Commit() method
    // of the SqlTransaction object
    Console.WriteLine("Committing transaction");
    mySqlTransaction.Commit();

    // close the database connection using the Close() method
    // of the SqlConnection object
    mySqlConnection.Close();

  }

}