/* 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(); } }