Click here to Skip to main content
15,867,994 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi all i would like to use MySqlTransaction in my requirement. Actually i am having a doubt regarding that i.e as per my requirement i will have to delete different values from database.

The process i am doing is as follows. Assume that i am having 2 EmpIDs where this EmpID will hold different values which may be multiple. I will store the corresponding values for that particular EmpID using Dictionary and then i will save them to a list corresponding to the EmpID.

Assume that i am having list element as follows

For EmpID 1 i will have 1,2. I will check for the maximum value from the datbase in this list if exists i would like to delete this EmpID from the database.

For EmpID 2 i will have 1,2. But in my database i will have 3 as maximum values. So this one fails . I would like to rollback the previously deleted item .

Is it possible to do with a transaction if so can any one help me in solving this

Sample i code

C#
if(findMax(lst,iEmpID)
  {
       obj.delete("storeprocname");   // this will occur when my list has maximum value
  }
 else
 {
     //Here i would like to rollback my previous one referring to the delete method in class file
 }



C#
public bool deletePayRoll(string storeproc,bool result)
        {
if(result==true)
{
            m_bFlag = false;
            this.m_oConn = Utilities.GetConnection();
            m_oCmd = new MySqlCommand(storeproc, m_oConn);
            m_oCmd.CommandType = CommandType.StoredProcedure;

            m_oCmd.Parameters.AddWithValue("_EmpId", EmpID);
            m_oCmd.Parameters.AddWithValue("_FedTaxID", FedTaxID);
            m_oCmd.Parameters.AddWithValue("_payperiodnumber", Payperiodnumber);
            m_oCmd.Parameters.AddWithValue("_payyear", PayYear);
            m_oCmd.Parameters.AddWithValue("_paymentdate", PaymentDate);

            try
            {
                if (m_oConn.State != ConnectionState.Open)
                {
                    m_oConn.Open();

                }
                if (m_oCmd.ExecuteNonQuery() > 0)
                {
                    m_bFlag = true;
                }
            }
            catch (MySqlException oSqlEx)
            {
                m_sbErrMsg.Length = 0;
                m_sbErrMsg = Utilities.SqlErrorMessage(oSqlEx);
                //DB write the Error Log
                m_oErrlog.Add(m_sbErrMsg.ToString(), DateTime.Now);
            }
            catch (Exception oEx)
            {

                m_sbErrMsg = Utilities.ErrorMessage(oEx);
                //DB write the Error Log
                m_oErrlog.Add(m_sbErrMsg.ToString(), DateTime.Now);
            }

            finally
            {
                m_oConn.Close();
            }
}else
{
// Here i would like to rollback is it correct process 
}
            return m_bFlag;
        }


Hmm i think no one understand my question clearly so in simple scenario i am saying

i am having a form which will have 2 text boxes and button when i click on button i would like to insert the text entered in the text boxes to database as separate rows.

Assume i will have a bool value to pass while inserting if i give true for the first item it will get inserted, i will give false to the second one which i would like to insert. As i am having false i don't want to insert this so i have to roll back the previous one too

a sample code

C#
button click
{
string str="Dora";
string str1="Babu";
bool flag=true;
i=1,j=1;
if(i==1 && flag==true)
inser str;
flag=false;
if(j==1 && flag==false)
rollback 
}
Posted
Updated 10-Sep-11 1:17am
v4

Yes - and transactions are exactly what you need. Once you start a transaction, no changes are permanent until you commit them, unlike normal operations where they happen irrevocably as soon as the command is executed. With a transaction, you can "change your mind" and rollback or cancel the actions you have taken.

There is a guide to transactions in MySql here: http://www.devshed.com/c/a/MySQL/Using-Transactions-In-MySQL-Part-1/[^]
 
Share this answer
 
Comments
demouser743 10-Sep-11 2:16am    
Hi Can you check my update and tell what should i do
OriginalGriff 10-Sep-11 2:20am    
Read the link I gave you? It will show you what to do - If you are up to complicated things that need transactions, then you shouldn't need your hand holding through it all!
Ger Hayden 10-Sep-11 7:58am    
I am using MySqlTransaction very successfully, in scenarios where a sale might hit 5 or 6 tables but if it doesnt go through fully then everything has to be rolled back. Its an excellent approach. I have also included commit and rollback buttons on my forms where users are updating information.
OriginalGriff 10-Sep-11 8:27am    
Glad to hear it! The only comment I would make (and it is probably Teaching-your-granny-to-suck-eggs) is that you need to handle the Form.Closing event to ensure that the user is reminded if any transactions have not been completed.
(I only mention it so that novices won't forget!) :laugh:
As OriginalGriff pointed out, transaction is the correct way to do this. Also have a look at the documentation: MySqlTransaction[^].

Another thing, if I understood your scenario correctly, are you calculating maximum values and perhaps inserting data so that you use MAX+1 is something similar. If you have that kind of structure, it's not advisable to maintain such order numbers in the database. Each row should have a unique key, but you shouldn't set it in the program. Instead use AUTO_INCREMENT[^]
 
Share this answer
 
Comments
OriginalGriff 10-Sep-11 8:28am    
Or better still, use a Guid - I dislike autoincrements except for log files when they will never actually be referred to.
Wendelius 10-Sep-11 10:58am    
Guid is also just fine. Why do you dislike autoincrements?
OriginalGriff 10-Sep-11 11:57am    
Because if (as I normally do) you want to refer to the row you just inserted, you have to play silly buggers to get the autoincrement ID back into your code from the Database - it isn't as simple as just saying "SELECT MAX(iD) FROM myTable" in the SQL command, because in a multiuser environment it can cause intermittent faults. And the situation gets worse if you insert more than one row in a single command - you have to search for other unique columns to identify the correct rows to reference. Guid gets rid of all that, because you assign the ID in your code, and don't need to ask the database what value you gave it. In addition, you can use the Guid ID for replication: if you use an autoincremented field, then the database will also create a Guid column you can't read to ensure that new rows are indeed new when created in two separate databases.
And (surprisingly) they don't take much extra space, or any real overhead! Have a Google - there is a good amount of research on the subject.
Wendelius 11-Sep-11 3:19am    
Yes, you're right about the replication, it's a PITA. However, that restriction applies to SQL Server. Also getting the inserted surrogates reliably is quite easy these days because of the OUTPUT clause. Earlier it was quite a problem especially if several rows were inserted at a time, but in my opinion that has chanfed.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900