Csharp/C Sharp/Database ADO.net/Transactions

Материал из .Net Framework эксперт
Перейти к: навигация, поиск

Commit two delete sql command

<source lang="csharp"> using System; using System.Data; using System.Data.SqlClient;

  class SqlDemo {
     static void Main(){
        string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
        SqlConnection cn = new SqlConnection(connString);
        cn.Open();
        SqlTransaction objTrans = cn.BeginTransaction();
        try{
           SqlCommand cmd = cn.CreateCommand();
           cmd.rumandType = CommandType.Text;
           cmd.Transaction = objTrans;
           cmd.rumandText = "DELETE Employee WHERE Id = 222";
           cmd.ExecuteNonQuery();
           cmd.rumandText="DELETE Employee WHERE Id = 333";
           cmd.ExecuteNonQuery();
           objTrans.rumit();
           Console.WriteLine("Transaction Committed\n");
        }
        catch (System.Data.SqlClient.SqlException  ex)
        {
           objTrans.Rollback();  
           Console.WriteLine("Error - TRANSACTION ROLLED BACK\n" + ex.Message);
        }
        catch (System.Exception ex)
        {
           Console.WriteLine("System Error\n" + ex.Message);
        }
        finally
        {
           cn.Close();  
        }
     }
  }
          
      </source>


illustrates the use of transactions

<source lang="csharp"> /* 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.rumandText = 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.rumandText = 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.rumit();
   // close the database connection using the Close() method
   // of the SqlConnection object
   mySqlConnection.Close();
 }

}


      </source>


Set a savepoint in a transaction

<source lang="csharp"> using System; using System.Data; using System.Data.SqlClient; class Savepoint {

   public static void Main() {
       SqlConnection mySqlConnection =
         new SqlConnection(
           "server=localhost;database=Northwind;uid=sa;pwd=sa"
         );
       mySqlConnection.Open();
       SqlTransaction mySqlTransaction = mySqlConnection.BeginTransaction();
       SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
       mySqlCommand.Transaction = mySqlTransaction;
       mySqlCommand.rumandText =
         "INSERT INTO Customers ( " +
         "  CustomerID, CompanyName " +
         ") VALUES ( " +
         "  "J8COM", "J8 Company" " +
         ")";
       int numberOfRows = mySqlCommand.ExecuteNonQuery();
       Console.WriteLine("Number of rows inserted = " + numberOfRows);
       mySqlTransaction.Save("SaveCustomer");
       mySqlCommand.rumandText = "INSERT INTO Orders (CustomerID ) VALUES ( "J8COM" )";
       numberOfRows = mySqlCommand.ExecuteNonQuery();
       Console.WriteLine("Number of rows inserted = " + numberOfRows);
       mySqlTransaction.Rollback("SaveCustomer");
       mySqlCommand.rumandText =
             "SELECT CustomerID, CompanyName " +
             "FROM Customers " +
             "WHERE CustomerID = "J8COM"";
       SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
       while (mySqlDataReader.Read()) {
           Console.WriteLine("mySqlDataReader[\" CustomerID\"] = " +
             mySqlDataReader["CustomerID"]);
           Console.WriteLine("mySqlDataReader[\" CompanyName\"] = " +
             mySqlDataReader["CompanyName"]);
       }
       mySqlDataReader.Close();
       mySqlCommand.rumandText = "DELETE FROM Customers WHERE CustomerID = "J8COM"";
       numberOfRows = mySqlCommand.ExecuteNonQuery();
       Console.WriteLine("Number of rows deleted = " + numberOfRows);
       mySqlTransaction.rumit();
       mySqlConnection.Close();
   }

}

</source>


Transaction roll back and commit

<source lang="csharp"> using System; using System.Data; using System.Data.SqlClient;

  class SqlDemo {
     static void Main(){
        string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
        SqlConnection cn = new SqlConnection(connString);
        cn.Open();
        SqlTransaction objTrans = cn.BeginTransaction();
        try 
        {      
           SqlCommand cmd = cn.CreateCommand();
           cmd.rumandType = CommandType.Text;
           cmd.Transaction = objTrans;
           cmd.rumandText = "DELETE Employee WHERE Id = 222";
           cmd.ExecuteNonQuery();
           objTrans.rumit();
       
           Console.WriteLine("Transaction Committed\n" );
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
           objTrans.Rollback();
           Console.WriteLine("Error - TRANSACTION ROLLED BACK\n" + ex.Message);
        }
        catch (System.Exception ex)
        {
           Console.WriteLine("System Error\n" + ex.Message);
        }
        finally
        {
           cn.Close();
        }
     }
  }
          
      </source>


Update Data Using Transactions

<source lang="csharp"> /*

* C# Programmers Pocket Consultant
* Author: Gregory S. MacBeth
* Email: gmacbeth@comporium.net
* Create Date: June 27, 2003
* Last Modified Date:
* Version: 1
*/

using System; using System.Data; using System.Data.SqlClient; namespace Client.Chapter_13___ADO.NET {

   public class UpdatingDataUsingTransactions
   {
       static void Main(string[] args)
       {
           SqlConnection MyConnection = new SqlConnection(@"Data Source=(local); Initial Catalog = CaseManager; Integrated Security=true");
           MyConnection.Open();
           String MyString = "INSERT INTO Test(ID, Contact, Email)VALUES(1, "Greg", "Mac")";
           SqlTransaction MyTransaction = MyConnection.BeginTransaction();
           SqlCommand MyCmd = new SqlCommand(MyString, MyConnection, MyTransaction);
           MyCmd.ExecuteScalar();
           MyTransaction.rumit();
           MyConnection.Close();
       }
   }

}

      </source>


Use of a transaction

<source lang="csharp"> using System; using System.Data; using System.Data.SqlClient; class ExecuteTransaction {

 public static void Main()
 {
   SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
   mySqlConnection.Open();
   SqlTransaction mySqlTransaction = mySqlConnection.BeginTransaction();
   SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
   mySqlCommand.Transaction = mySqlTransaction;
   mySqlCommand.rumandText =
     "INSERT INTO Employee (" +
     "  ID, FirstName" +
     ") VALUES (" +
     "  10, "Jason"" +
     ")";
   Console.WriteLine("Running first INSERT statement");
   mySqlCommand.ExecuteNonQuery();
   mySqlCommand.rumandText =
     "INSERT INTO Employee (" +
     "  ID, FirstName" +
     ") VALUES (" +
     "  11, "Jason"" +
     ")";
   Console.WriteLine("Running second INSERT statement");
   mySqlCommand.ExecuteNonQuery();
   Console.WriteLine("Committing transaction");
   mySqlTransaction.rumit();
   mySqlConnection.Close();
 }

}


      </source>