// This function takes arguments for 2 connection strings and commands to create a transaction // involving two SQL Servers. It returns a value > 0 if the transaction is committed, 0 if the // transaction is rolled back. To test this code, you can connect to two different databases // on the same server by altering the connection string, or to another 3rd party RDBMS // by altering the code in the connection2 code block.public int CreateTransactionScope(string connectString1, string connectString2, string commandText1, string commandText2) { // Initialize the return value to zero and create a StringWriter to display results. int returnValue = 0; System.IO.StringWriter writer = new System.IO.StringWriter(); try { // Create the TransactionScope to execute the commands, guaranteeing // that both commands can commit or roll back as a single unit of work. using (TransactionScope scope = new TransactionScope()) { using (SqlConnection connection1 = new SqlConnection(connectString1)) { // Opening the connection automatically enlists it in the // TransactionScope as a lightweight transaction. connection1.Open(); // Create the SqlCommand object and execute the first command. SqlCommand command1 = new SqlCommand(commandText1, connection1); returnValue = command1.ExecuteNonQuery(); writer.WriteLine("Rows to be affected by command1: {0}", returnValue); // If you get here, this means that command1 succeeded. By nesting // the using block for connection2 inside that of connection1, you // conserve server and network resources as connection2 is opened // only when there is a chance that the transaction can commit. using (SqlConnection connection2 = new SqlConnection(connectString2)) { // The transaction is escalated to a full distributed // transaction when connection2 is opened. connection2.Open(); // Execute the second command in the second database. returnValue = 0; SqlCommand command2 = new SqlCommand(commandText2, connection2); returnValue = command2.ExecuteNonQuery(); writer.WriteLine("Rows to be affected by command2: {0}", returnValue); } } // The Complete method commits the transaction. If an exception has been thrown, // Complete is called and the transaction is rolled back. scope.Complete(); } } catch (TransactionAbortedException ex) { writer.WriteLine("TransactionAbortedException Message: {0}", ex.Message); } catch (ApplicationException ex) { writer.WriteLine("ApplicationException Message: {0}", ex.Message); } // Display messages. Console.WriteLine(writer.ToString()); return returnValue; }
create proc proc_name
...--参数
as
begin tran
update...
select...
delete...
commit tran
参数
AS
(
DECLARE @TranStarted bit
SET @TranStarted = 0 IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
)SqlConnection conn=new .........;
using(SqlTransacation tran=conn.BeginTransaction(Isolevel.....))
{
//do
}
//使用 TransScope 没开IDE 自己去命名空间下面找TransScope ,这个要 Commit 提交才能处理事务
提供了一个静态的DBprovider,connection是静态的,保持一份。
但是却出现了transaction失效的提示
// involving two SQL Servers. It returns a value > 0 if the transaction is committed, 0 if the
// transaction is rolled back. To test this code, you can connect to two different databases
// on the same server by altering the connection string, or to another 3rd party RDBMS
// by altering the code in the connection2 code block.public int CreateTransactionScope(string connectString1, string connectString2, string
commandText1, string commandText2)
{ // Initialize the return value to zero and create a StringWriter to display results.
int returnValue = 0;
System.IO.StringWriter writer = new System.IO.StringWriter(); try
{
// Create the TransactionScope to execute the commands, guaranteeing
// that both commands can commit or roll back as a single unit of work.
using (TransactionScope scope = new TransactionScope())
{
using (SqlConnection connection1 = new SqlConnection(connectString1))
{
// Opening the connection automatically enlists it in the
// TransactionScope as a lightweight transaction.
connection1.Open(); // Create the SqlCommand object and execute the first command.
SqlCommand command1 = new SqlCommand(commandText1, connection1);
returnValue = command1.ExecuteNonQuery();
writer.WriteLine("Rows to be affected by command1: {0}", returnValue); // If you get here, this means that command1 succeeded. By nesting
// the using block for connection2 inside that of connection1, you
// conserve server and network resources as connection2 is opened
// only when there is a chance that the transaction can commit.
using (SqlConnection connection2 = new SqlConnection(connectString2))
{
// The transaction is escalated to a full distributed
// transaction when connection2 is opened.
connection2.Open(); // Execute the second command in the second database.
returnValue = 0;
SqlCommand command2 = new SqlCommand(commandText2, connection2);
returnValue = command2.ExecuteNonQuery();
writer.WriteLine("Rows to be affected by command2: {0}", returnValue);
}
} // The Complete method commits the transaction. If an exception has been thrown,
// Complete is called and the transaction is rolled back.
scope.Complete();
}
}
catch (TransactionAbortedException ex)
{
writer.WriteLine("TransactionAbortedException Message: {0}", ex.Message);
}
catch (ApplicationException ex)
{
writer.WriteLine("ApplicationException Message: {0}", ex.Message);
} // Display messages.
Console.WriteLine(writer.ToString()); return returnValue;
}
http://topic.csdn.net/u/20070317/07/a0afad5f-6401-4bdf-a5bd-9158aff1f571.html
比如
transactionScope = new ******
conn.open();update()delete()---------------------异常发生了add()transactionScope.Complete()结果update()没有回滚