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?