Transaction not returning in C #

Question

private void btnConfigure_Click(object sender, EventArgs e) { try { dbConfigure dc = new dbConfigure(); SqlTransaction tr = conn.BeginTransaction(); cmd.Transaction = tr; if (dc.configuration(cmd, ps.tableNames)) tr.Commit(); else { tr.Rollback(); mesg.show("Transaction is Rolled back"); } } catch (Exception ex) { mesg.show(ex.Message); } } 

If I get the problem somewhere in the configuration method, then it returns me false, and I see the Transaction is Rolled Back message. But in reality, the transaction is not rolled back completely, and some changes in the database structure created by this function remain rollback there, which is completely undesirable. My question is What could be the possibility of a failure in a transaction?

I have no more transactions in my project, except for the general (above) method

Small details

I call the very long / complex configuration function of my dbConfigure class. It makes some necessary changes to the database structure. for example This

• Drops foriegnKeys
• Drops Primary Keys

• Drops auto-increment fields

  It saves these keys before discarding and re-creating in the desired order / position

conn is a SqlConnection that is already open, I do not use anywhere else but this

cmd is conn.CreateCommand() I do not use the command anywhere except this

I never close the connection in this whole process, however, SqlDataReader closes in the configuration function when they do their job.

Answer 1

Changes to the database structure are not transactional, so you cannot roll back the creation of a new table, for example

BS. Most DDLs are transactional and can be thrown back. Only changes that are associated with interaction with non-transactional components (for example, with the file system, for example with adding a new file to the database) cannot be undone. Any DDL that is not transactional also throws an exception very explicitly if it is called in an active transaction.

Adding and modifying tables is very explicitly transactional, and it can be easily represented using an example:

 begin transaction; create table foo (a int); select * from sys.tables where object_id = object_id('foo'); rollback; select * from sys.tables where object_id = object_id('foo'); 

Therefore, the problem is the missing OP code, parts not placed.

System.Transactions should be used as a general comment whenever possible (given that the default constructor is corrupted ). If you use SqlConnection.BeginTransaction, it is even better to rely on IDisposable:

 using (SqlTransaction trn = conn.BeginTransaction()) { ... trn.Commit (); } 

System.Transactions should be encouraged, although since they are independent of code discipline, any SqlClient code in the transaction area will be automatically registered.

And btw has a raise configuration function on error, and does not return false.

And on the main real problem: how to cope with a long complex migration that cannot be registered in one transaction (for example, it simply can generate too much log). The answer is that the only possible option is to take a backup of the database at the beginning of the migration and restore from this backup if the migration fails. The alternative of providing a manual, verified, and reliable compensatory action for each migration operation to cancel the migration is incredibly complex, erroneous, and ultimately unnecessary, since restoring from a backup is much simpler and provably correct.

Answer 2

This can be done instead of using TransactionScope, and not to create SqlTransaction in your code. Something like that:

 using (TransactionScope transaction = new TransactionScope()) { ...perform your database action here scope.Complete() }