Csharp/C Sharp/Database ADO.net/Transactions — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 14:42, 26 мая 2010
Содержание
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>