During program development, transactions are usually used to add, delete, and modify databases for all updates and rollback. A single database is relatively easy to do. If you need to operate multiple databases at the same time, you can use the following method
I. TransactionScope class
The TransactionScope class is a new class in framework2.0. in the Transactions namespace, you must first add System. transactions references. In addition, you need to go to the windows Control Panel and choose management tools> services> Distributed Transaction Coordinator> Properties> start to start the service. the sample code is as follows:
Code
Try
{
Using (TransactionScope scope = new TransactionScope ())
{
// Update the Employees table of the northwind database
Using (SqlConnection conOne = new SqlConnection ("server =.; uid = sa; pwd = 123; database = northwind "))
{
ConOne. Open ();
SqlCommand command = new SqlCommand ("update Employees set lastname = 'chen' where employeeid = '1'", conOne );
Int I = command. ExecuteNonQuery ();
}
// Update the jobs table of the pubs Database
Using (SqlConnection conTwo = new SqlConnection ("server =.; uid = sa; pwd = 123; database = pubs "))
{
ConTwo. Open ();
SqlCommand command = new SqlCommand ("update jobs set job_desc = 'chen' where job_id = '1'", conTwo );
Int I = command. ExecuteNonQuery ();
}
Scope. Complete (); // submit a transaction
}
}
Catch (Exception ex) // automatically rolls back when an Exception occurs
{
// Throw;
}
II. Another method is to establish two transactions and two connections. The Code is as follows;
Code
SqlConnection conNorthwind = new SqlConnection ("server =.; uid = sa; pwd = 123; database = northwind ");
SqlConnection conPubs = new SqlConnection ("server =.; uid = sa; pwd = 123; database = pubs ");
SqlCommand commandNorthwind = new SqlCommand ();
SqlCommand commandPubs = new SqlCommand ();
Try
{
ConNorthwind. Open ();
ConPubs. Open ();
// Update the Employees table of the northwind database
SqlTransaction tranNorthwind = conNorthwind. BeginTransaction ();
CommandNorthwind. Connection = conNorthwind;
CommandNorthwind. Transaction = tranNorthwind;
CommandNorthwind. CommandText = "update Employees set lastname = 'chen' where employeeid = '1 '";
Int I = commandNorthwind. ExecuteNonQuery ();
// Update the jobs table of the pubs Database
SqlTransaction tranPubs = conPubs. BeginTransaction ();
CommandPubs. Connection = conPubs;
CommandPubs. Transaction = tranPubs;
CommandPubs. CommandText = "update jobs set job_desc = 'chen' where job_id = '1 '";
Int k = commandPubs. ExecuteNonQuery ();
// Throw new Exception ();
// Submit the transaction
CommandNorthwind. Transaction. Commit ();
ConNorthwind. Close ();
CommandPubs. Transaction. Commit ();
ConPubs. Close ();
}
Catch (Exception ex)
{
// Roll back the transaction
If (commandNorthwind. Transaction! = Null & conNorthwind! = Null)
{
CommandNorthwind. Transaction. Rollback ();
ConNorthwind. Close ();
}
If (commandPubs. Transaction! = Null & conPubs! = Null)
{
CommandPubs. Transaction. Rollback ();
ConPubs. Close ();
}
// Throw;
}