Nested transactions using C#

Question

Dear All, I hope you are doing well. I am facing problem which is related to nested trasactions. eg. :- MySqlConnection connection= new MySqlConnection(ConnectionString); MySqlTransaction myTrans = null ; connection.Open(); myTrans = connEpion.BeginTransaction(); try { InsertOperation .... string result = InsertOperation2 .... myTrans.Committ(); } catch (Exception ex) { Console.Write(ex.Message); } finally { connection.Close(); } InsertOperation2 is a function which is returning string result either exception or anything. If I got any error then all insert operations should get rollback including operation which is performed in a method. If I won't committ transaction then it will not save any data and Once if I commiitt any transaction then I am not able to rollback to it. Thanking you in advance. Regards.

Jenith Thakkar · Answer

You can use rollback transaction in this way 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); } } } }

Salman Beg · Answer

Hi. Probably you are doing something wrong in the coding. Follow this link for example, https://www.c-sharpcorner.com/article/transaction-in-net/

Nested transactions using C#

Answers (2)