استفاده از رول بک

[MahmoodSafaeiMojarad]

با سلام - من یک برنامه دارم که با استفاده از table adaptor و stored procedures ها به بانک sql متصل می شوم
چگونه می توانم از rollback در این برنامه استفاده کنم ؟
tbl_OTC_TR_RequestsTableAdapter.DELETE_Request(use rid, date);
for (int i = 0; i < chklstShifts.CheckedItems.Count; i++)
{
string strShiftName = chklstShifts.CheckedItems[i].ToString();
strShiftName = strShiftName.Substring(0, strShiftName.IndexOf("-") - 1);
WindowsFormsApplications.DAL.SchemaOverTimeControl.tbl_OTC_BS_ShiftsDataTable dtShift =
tbl_OTC_BS_ShiftsTableAdapter.GetDataByShiftName(s trShiftName);
tbl_OTC_TR_RequestsTableAdapter.INSERT_Request(int.Parse(txtUserId.Text), dtShift[0].ShiftId, cmbDate.Text, txtAccount.Text, chkIsActive.Checked);
}
در کد با می خواهم اگر نتوانست insert کند عمل delete در این کد roolback شود

[tooraj_azizi_1035]

سلام،
دوست من باید از کلاس SqlTransaction استفاده کنی:

SqlTransaction tn ;  //declare a transaction

conststring sql = "INSERT INTO Employees1(EmpID) VALUES (@UserID)";

SqlConnection cn = new SqlConnection("data source=AUG-SQLSRV;initial catalog=HumanResources;integrated security=SSPI");

         

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

//If we throw an exception on Open, which is a 'risky' operation

//manually make the assertino fail by setting it to false and use

//ex.ToString() to get the information about the exception.

catch (SqlException ex){Debug.Assert(false, ex.ToString());}

  //Instantiate command with CommandText and Connection and t       //transaction

   tn = cn.BeginTransaction();

    SqlCommand cmd = new SqlCommand(sql, cn,tn);

    cmd.Parameters.Clear();

    cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = 314;



   try

  {            

   //You can test for records affected, in this case we know it 

//would be at most one record.

int i = cmd.ExecuteNonQuery();

   //If successful, commit the transaction

//Loop 5 times and just add the id's incremented each time

for(int x=0; x<5; x++)<BR>       {

          cmd.Parameters["@UserID"].Value = (315 + x);

          cmd.ExecuteNonQuery();

      }

       cmd.Parameters["@UserID"].Value = (325);

       cmd.ExecuteNonQuery();



       tn.Commit();

   }

      catch(SqlException ex){

          Debug.Assert(false, ex.ToString());

//If it failed for whatever reason, rollback the //transaction

         tn.Rollback();

//No need to throw because we are at a top level call and //nothing is handling exceptions

     }

      finally{

         //Check for close and respond accordingly

if(cn.State != ConnectionState.Closed){cn.Close();}

         //Clean up my mess

         cn.Dispose();

          cmd.Dispose();

          tn.Dispose();

     }

[MahmoodSafaeiMojarad]

سلام - از راهنمائیت ممنونم ولی من باید از table adaptor استفاده کنم !
هیچ راهی برای این مورد نیست ؟

[tooraj_azizi_1035]

این کد:

public void ExecuteTransactionRetrieveAndUpdate(){   CustomersTableAdapter adapter = new CustomersTableAdapter();   SqlConnection connection =      new SqlConnection(Settings.Default.ConnectionString);   using (connection)   {      connection.Open();      SqlTransaction transaction =         adapter.BeginTransaction(connection);      CustomersDataSet.CustomersDataTable customers =         adapter.GetData();      customers[0].Phone = "030-0074321";      adapter.Update(customers);      transaction.Commit();   }}

قسمت Commit رو در یک بلوک try/catch قرار بده و در قسمت catch رول بک کن.