Nested transactions using C#

Mandar Shinde

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.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jenith Thakkar6y ago
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 Beg6y ago
Hi. Probably you are doing something wrong in the coding. Follow this link for example, https://www.c-sharpcorner.com/article/transaction-in-net/