I did a lot search already but couldn't find a straight anwser.
我已经做了很多搜索,但找不到直接的anwser。
I have two stored procedures and they both were function imported to the DBContext object
我有两个存储过程,它们都是导入到DBContext对象的函数
- InsertA()
- INSERTA()
- InsertB()
- InsertB()
I want to put them in a transaction. (i.e. if InsertB() failed, rolled back InsertA())
我想把它们放在交易中。 (即如果InsertB()失败,则回滚InsertA())
How do I do that? Can I just declare a TransactionScope object and wrap around the two stored procedures?
我怎么做?我可以只声明一个TransactionScope对象并包装两个存储过程吗?
Thanks
谢谢
2 个解决方案
#1
13
You need to enlist your operations in a transaction scope, as follows:
您需要在事务范围中登记您的操作,如下所示:
using(TransactionScope tranScope = new TransactionScope())
{
InsertA();
InsertB();
tranScope.Complete();
}
On error, the transaction scope will automatically be rolled back. Of course, you still need to handle exceptions and do whatever your exception handling design dictates (log, etc). But unless you manually call Complete()
, the transaction is rolled back when the using
scope ends.
出错时,将自动回滚事务范围。当然,您仍然需要处理异常并执行您的异常处理设计所指示的任何内容(日志等)。但除非您手动调用Complete(),否则当使用范围结束时,事务将回滚。
The transaction scope will not be promoted to a distributed transaction unless you open other database connections in the same transaction scope (see here).
除非您在同一事务范围中打开其他数据库连接,否则事务范围不会提升为分布式事务(请参阅此处)。
This is important to know because otherwise you would need to configure MSDTC on all your servers involved in this operation (web, middle tier eventually, sql server). So, as long as the transaction isn't promoted to a distributed one, you'll be fine.
这一点很重要,因为否则您需要在此操作中涉及的所有服务器上配置MSDTC(Web,最终中间层,sql server)。因此,只要交易不被提升为分布式交易,您就可以了。
Note: In order to fine-tune your transaction options, such as timeouts and isolation levels, have a look at this TransactionScope
constructor. Default isolation level is serializable.
注意:为了微调您的事务选项,例如超时和隔离级别,请查看此TransactionScope构造函数。默认隔离级别是可序列化的。
Additional sample: here.
附加示例:此处。
#2
4
You can use the TransactionScope object or you can use the SqlConnection.BeginTransaction Method. Be careful using TransactionScope, transactions can be esculated to distributed transactions when calling stored procedures in a different database. Distributed transactions can be resource intensive.
您可以使用TransactionScope对象,也可以使用SqlConnection.BeginTransaction方法。使用TransactionScope时要小心,在不同的数据库中调用存储过程时,可以将事务计算到分布式事务。分布式事务可能是资源密集的。
How to use sqlConnection.BeginTransaction...(http://msdn.microsoft.com/en-us/library/86773566.aspx)
如何使用sqlConnection.BeginTransaction ...(http://msdn.microsoft.com/en-us/library/86773566.aspx)
private static void ExecuteSqlTransaction(string connectionString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = connection.CreateCommand();
SqlTransaction transaction;
// Start a local transaction.
transaction = connection.BeginTransaction("SampleTransaction");
// Must assign both transaction object and connection
// to Command object for a pending local transaction
command.Connection = connection;
command.Transaction = transaction;
try
{
command.CommandText =
"Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
command.ExecuteNonQuery();
command.CommandText =
"Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
command.ExecuteNonQuery();
// Attempt to commit the transaction.
transaction.Commit();
Console.WriteLine("Both records are written to database.");
}
catch (Exception ex)
{
Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
}
}
}
How to use TransactionScope...(http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx)
如何使用TransactionScope ...(http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx)
// 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.
static 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 not 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;
}
#1
13
You need to enlist your operations in a transaction scope, as follows:
您需要在事务范围中登记您的操作,如下所示:
using(TransactionScope tranScope = new TransactionScope())
{
InsertA();
InsertB();
tranScope.Complete();
}
On error, the transaction scope will automatically be rolled back. Of course, you still need to handle exceptions and do whatever your exception handling design dictates (log, etc). But unless you manually call Complete()
, the transaction is rolled back when the using
scope ends.
出错时,将自动回滚事务范围。当然,您仍然需要处理异常并执行您的异常处理设计所指示的任何内容(日志等)。但除非您手动调用Complete(),否则当使用范围结束时,事务将回滚。
The transaction scope will not be promoted to a distributed transaction unless you open other database connections in the same transaction scope (see here).
除非您在同一事务范围中打开其他数据库连接,否则事务范围不会提升为分布式事务(请参阅此处)。
This is important to know because otherwise you would need to configure MSDTC on all your servers involved in this operation (web, middle tier eventually, sql server). So, as long as the transaction isn't promoted to a distributed one, you'll be fine.
这一点很重要,因为否则您需要在此操作中涉及的所有服务器上配置MSDTC(Web,最终中间层,sql server)。因此,只要交易不被提升为分布式交易,您就可以了。
Note: In order to fine-tune your transaction options, such as timeouts and isolation levels, have a look at this TransactionScope
constructor. Default isolation level is serializable.
注意:为了微调您的事务选项,例如超时和隔离级别,请查看此TransactionScope构造函数。默认隔离级别是可序列化的。
Additional sample: here.
附加示例:此处。
#2
4
You can use the TransactionScope object or you can use the SqlConnection.BeginTransaction Method. Be careful using TransactionScope, transactions can be esculated to distributed transactions when calling stored procedures in a different database. Distributed transactions can be resource intensive.
您可以使用TransactionScope对象,也可以使用SqlConnection.BeginTransaction方法。使用TransactionScope时要小心,在不同的数据库中调用存储过程时,可以将事务计算到分布式事务。分布式事务可能是资源密集的。
How to use sqlConnection.BeginTransaction...(http://msdn.microsoft.com/en-us/library/86773566.aspx)
如何使用sqlConnection.BeginTransaction ...(http://msdn.microsoft.com/en-us/library/86773566.aspx)
private static void ExecuteSqlTransaction(string connectionString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = connection.CreateCommand();
SqlTransaction transaction;
// Start a local transaction.
transaction = connection.BeginTransaction("SampleTransaction");
// Must assign both transaction object and connection
// to Command object for a pending local transaction
command.Connection = connection;
command.Transaction = transaction;
try
{
command.CommandText =
"Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
command.ExecuteNonQuery();
command.CommandText =
"Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
command.ExecuteNonQuery();
// Attempt to commit the transaction.
transaction.Commit();
Console.WriteLine("Both records are written to database.");
}
catch (Exception ex)
{
Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
}
}
}
How to use TransactionScope...(http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx)
如何使用TransactionScope ...(http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx)
// 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.
static 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 not 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;
}