Need help in using MySqlTransaction

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

  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
 }

My sample code

if (findMaxPayPeriodID(lstPayPeriodID, iEmpIDs)) //Assume for the first time maxpayperiod exists and for the second time it fails how to rollback then
  {
    if (findSequence(lstPayPeriodID)) // Assume this is also true for first time
    {
      for (int ilstPayperiodID = 0; ilstPayperiodID < lstPayPeriodID1.Count; ilstPayperiodID++)
  {
      oAdmin.Payperiodnumber = (int)lstPayPeriodID1[ilstPayperiodID];
     for (int ilistPayYear = iPayYearcnt; ilistPayYear < lstPayYear1.Count; ilistPayYear++)
  {
      oAdmin.PayYear = (int)lstPayYear1[ilistPayYear];
      iPayYearcnt++;
      break;
   }
   for (int ilistDateTime = idtcnt; ilistDateTime < lstDateTime1.Count; ilistDateTime++)
  {
    idtcnt++;
    oAdmin.PaymentDate = lstDateTime1[ilistDateTime];
    break;
   }
       }
    if (oAdmin.deletePayRoll(oSqlTran))
    {
   oMsg.Message = "Deleted Sucessfully";
   oMsg.AlertMessageBox(out m_locallblMessage);
   Page.Controls.Add(m_locallblMessage);
   oAdmin.FedTaxID = ddlFedTaxID.SelectedValue;
   oAdmin.PayFrequency = ddlPaymentType.SelectedValue.ToString();
   mlocal_strStoredProcName = "uspSearchPayRoll";
   oAdmin.getPayRollDetails(out mlocal_ds, mlocal_strStoredProcName);
   //grdPayroll.Visible = true;
   grdPayroll.DataSource = mlocal_ds;
   grdPayroll.DataBind();
   if (mlocal_ds != null开发者_StackOverflow中文版)
    {
       btnDelete.Visible = true;
     }
    else
    btnDelete.Visible = false;
    }
  lstPayPeriodID.Clear();
  lstDateTime.Clear();
  lstPayYear.Clear();
  iPayIDcnt = 0;
  iPayYearcnt = 0;
  idtcnt = 0;
   }
 else
   {
 rollback should be done
}

You don't provide enough information - esp. since it seems that you will use a Stored Procedure for the delete operation all bets are off...

The only option I can think of is to make sure that you find first the maximum EmpId not from one list BUT from all lists first... then just check that against the DB and act accordingly...

This way the DB will only be hit twice (for the check and for the delete/Stored Procedure)... which is definetely better in terms of scaling etc.