mysql transaction (commit and rollback)

Question

below is the code i am using for MySqlTransaction and it is running perfectly.... but problem is as i am using single instance of mysqlcommand i have to use unique PARAMETER in it. which is ruining my query. is there any other way around to do....

i tried to dispose cmd after each query but of no use.. :(

          con.Open()
            Dim sqlTran As MySqlTransaction = con.BeginTransaction()
            Dim cmd As MySqlCommand = con.CreateCommand()
            cmd.Transaction = sqlTran
            Dim str As String = Nothing
            Try
                cmd.CommandText = "SELECT myid FROM memaster where    Adate=@adate and ANo=@ano and ASource=@asrc"
                cmd.Parameters.AddWithValue("@adate", txt_bpass_adate.Text)
                cmd.Parameters.AddWithValue("@ano", txt_bpass_af.Text)
                cmd.Parameters.AddWithValue("@asrc", txt_bpass_asource.Text)
                str = cmd.ExecuteScalar()
                'cmd.Dispose()'
                If str Is Nothing Then

                    cmd.CommandText = "Insert into memaster (ADate,ANo,ASource) values (@aDate,@aNo,@aSRC)"
                    cmd.Parameters.AddWithValue("@aDate", txt_bpass_adate.Text)
                    cmd.Parameters.AddWithValue("@aNo", txt_bpass_af.Text)
                    cmd.Parameters.AddWithValue("@aSRC", txt_bpass_asource.Text)

     cmd.ExecuteNonQuery()
    End If
     sqlTran.Commit()

            Catch ex As Exception
                Try
                    sqlTran.Rollback()
                Catch ex1 As Exception

                End Try
            End Try

i actually want to fire more then 4 queries in single transaction so that if anything go wrong i can rollback it...

if any onebody have any other method of it kindly share the concept wid me...

     For index As Integer = 1 To 5
                    cmd.CommandText = "Insert into detail (ID,BNos,SNo) values (@FID1,@BNo,@SeqN1)"
                    cmd.Parameters.AddWithValue("@FID1", str)
                    cmd.Parameters.AddWithValue("@BNo", str1)
                    cmd.Parameters.AddWithValue("@SeqN1", txt_bpass_sqn1.Text)
                    cmd.ExecuteNonQuery()


                Next

when you dispose of an instance you cannot use it any more - connection is closed and it is prepared for garbage collection. Why you have to use single command instance? I just don't understand your problem try to explain better. — Rafal, Jan 10 '13 at 07:16
this.Cmd.Dispose(); would hold good.whats happening when you give dispose()? — MahaSwetha, Jan 10 '13 at 07:19
You specify the problem yourself: you're using one `MySqlCommand` when you should be using two. — C.Evenhuis, Jan 10 '13 at 07:33
@MahaSwetha : actually nothing is happening after disposing it is not even giving an error and i can use the same instance without any problem. — neerajMAX, Jan 10 '13 at 10:26
@C.Evenhuis yes but i have to use only one.... is there any way actually i have to fire more then 3 queries and have to maintain all in sigle transaction so that if any thing go wrong i can rollback — neerajMAX, Jan 10 '13 at 10:26
@Rafal : hmmm as i said i tried to dispose it but it did not worked... anyways Why i am using single instance because i have to maintained the whole transaction till end so that i can rollback it if any thing go wrong........ Hope you got it now — neerajMAX, Jan 10 '13 at 10:31
@neerajMAX You can execute multiple queries - on separate commands or on the same command - within the same transaction. Why on earth would you be limited to using one `MySqlCommand` instance? And as others have mentioned, you cannot use an object after `Dispose()`-ing it. — C.Evenhuis, Jan 10 '13 at 10:32
@C.Evenhuis: would you share it with me that how i can do it ??? i am actually confused with how will i maintain the "sqlTran" (mentioned in my code) in multiple comands.. — neerajMAX, Jan 10 '13 at 10:34

score 5 · Accepted Answer · answered Jan 10 '13 at 10:42

To execute multiple commands within the same transaction, ensure that you assign the transaction object to each command individually:

Dim selectCmd As MySqlCommand = con.CreateCommand()
Dim insertCmd As MySqlCommand = con.CreateCommand()

selectCmd.CommandText = "SELECT ..."
insertCmd.CommandText = "INSERT ..."

Dim sqlTran As MySqlTransaction = con.BeginTransaction()
Try
  selectCmd.Transaction = sqlTran
  insertCmd.Transaction = sqlTran

  ...selectCmd.ExecuteScalar()...
  ...insertCmd.ExecuteNonQuery()...

  sqlTran.Commit()
Catch
  sqlTran.Rollback()
End Try

As others have mentioned, it is generally a good idea to Dispose() objects (that are IDisposable) as soon as you're done working with them. After disposing objects, they can no longer be used.

@C Evenhuis : okh got it... but have a look on the for loop in my ques what should we have to do for that??? any suggestion??? — neerajMAX, Jan 10 '13 at 11:34

score 1 · Answer 2 · answered May 29 '15 at 10:01

You can use Using keyword for auto dispose objects. i don't know VB but I know C#. Please convert the code into VB.

using(MySqlConnection con= new MySqlConnection("connectionString"))
{
    con.Open();
    using(MysqlTransaction trans=con.BeginTransaction())
    {
        try
        {
            //command to executive query
            using(MysqlCommand cmd= new MySqlCommand("query", con, trans))
            {
                cmd.Parameters.AddWithValue("@parameter1", parametervalue1);
                cmd.Parameters.AddWithValue("@parameter2", parametervalue2);
                cmd.ExecutenonQuery();
                cmd.Parameters.Clear();
            }
            //command to execute query
            using(MysqlCommand cmd= new MySqlCommand("query", con, trans))
            {
                cmd.Parameters.AddWithValue("@parameter1", parametervalue1);
                cmd.Parameters.AddWithValue("@parameter2", parametervalue2);
                cmd.ExecutenonQuery();
                cmd.Parameters.Clear();
            }
            //command to execute query
            using(MysqlCommand cmd= new MySqlCommand("query", con, trans))
            {
                cmd.Parameters.AddWithValue("@parameter1", parametervalue1);
                cmd.Parameters.AddWithValue("@parameter2", parametervalue2);
                cmd.ExecutenonQuery();
                cmd.Parameters.Clear();
            }
            trans.Commit();
        }
        catch(Exception ex)
        {
            trans.Rollback();
        }
    }
}

If the failure in insertion is closed for connection purposes, the rollback will not work , will generate exception , have any other way to give Rollback ? — Andrew Alex, Feb 10 '16 at 18:44
This is a poor solution - a 'using' statement in c# does a try catch for you, and if anything bad happens MySqlTransaction.cs.Dispose() is called, which rolls back the transaction — Mario, Nov 03 '16 at 21:34
Mario. does MySqlTransaction.cs.Dispose() really roll back the transaction? If that so no need for trans.Rollback() after catch exception, and will the best solution. right? — user2241289, Jan 28 '17 at 06:19
this answered me: http://stackoverflow.com/questions/641660/will-a-using-statement-rollback-a-database-transaction-if-an-error-occurs?rq=1 — user2241289, Jan 28 '17 at 06:30

mysql transaction (commit and rollback)

2 Answers2

Linked