TransactionScope not rolling back with SqlDataAdapter.Update
I'm using SqlDataAdapter.Update with DataTables to update two SQL tables in a single transaction. If either insert fails I want to roll back all data. This is my code:
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
using (var scope = new TransactionScope())
{
// Insert first table
using (var command = conn.CreateCommand())
{
command.CommandText =
@"INSERT INTO TableA(Id, Data)
VALUES(@id, @data)";
command.CommandType = CommandType.Text;
command.Parameters.Add(new SqlParameter("@id", SqlDbType.Int) { SourceColumn = "Id" });
command.Parameters.Add(new SqlParameter("@data", SqlDbType.Char) { SourceColumn = "Data" });
var adapter = new SqlDataAdapter();
adapter.InsertCommand = command;
adapter.Update(tableADataTable);
}
// Insert second table
using (var command = conn.CreateCommand())
{
command.CommandText =
@"INSERT INTO TableB(Id, Data)
VALUES(@id, @data)";
command.CommandType = CommandType.Text;
command.Parameters.Add(new SqlParameter("@id", SqlDbType.Int) { SourceColumn = "Id" });
command.Parameters.Add(new SqlParameter("@data", SqlDbType.Char) { SourceColumn = "Data" });
var adapter = new SqlDataAdapter();
adapter.InsertCommand = command;
adapter.Update(tableBDataTable);
}
scope.Complete();
}
}
The problem I'm having is that if an exception is thrown during the second command execution, data from the first command is still commited. Do I开发者_开发知识库 need to explicitly roll back? Or is how TransactionScope should behave when using SqlDataAdapter.Update?
Something to note is that originally I had the SqlConnection creation within the TransactionScope using statement, but I moved it out as I was receiving errors that my DB server hadn't been configured correctly for distributed transactions. Is the fact that my SqlConnection creation is outside TransactionScope related?
I think you need to move your conn.Open()
call inside the transaction scope so it will enlist itself in the transaction. Also, make sure you do not have enslist=false;
in your connection string.
Try placing your SqlConnection inside the TransactionScope, it should then automatically enlist in the transaction.
I think in your code, you need to manually enlist the connection into the transaction... review the examples in these links.
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.enlistdistributedtransaction(v=VS.71).aspx
http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx
Sorry just caught your OP - perhaps it's because the connection wasn't configured to automatically enlist into existing transactions (a member of the connection string I think).
If you don't call Complete (or Commit on a SqlTransaction) it will automatically rollback.
Of course, in your current code sample - you can safely use a SqlTransaction object as you aren't involving multiple connections/databases.
try inserting this code
command.CommandText = @"SET autocommit = 0";
command.ExecuteNonQuery();
command.CommandText = @"SET sql_mode=TRADITIONAL";
command.ExecuteNonQuery();
精彩评论