how to rollback table if any error occur while executing multiple insert query simultaneously

Question

i want to execute 3 insert query in 3 different Tables. But these all 3 query are dependent to each other means when 1st query is executed successfully then there should be 2nd query execute and when 2nd query is executed successfully then there should be 3rd query is execute.

And if there is any Server Error or any Other type Error is occurred while executing the 2nd query then it must be delete all the records from database which is inserted by 1st query.

And if error occurred at the time of executing 3rd query (After successfully executed 1st and 2nd query) then there must be all records delete from database which is inserted by 1st and 2nd query.

Can i achieve this by execute delete query in Catch Block. Is it good idea ? or please give me a suggestion how i can achieve this because I am working in a live project so i can not take any risk. please response me soon.

Yeah. That is "databases for beginners that already know a totally simple select and now should read the basics what a db is". Transactions to the recue. — TomTom, Feb 09 '14 at 08:14

score 4 · Accepted Answer · answered Feb 09 '14 at 06:28

transactions made for you . look at this example:

    SqlConnection myConnection = new SqlConnection("Data Source=localhost;Initial Catalog=Northwind;uid=sa;pwd=sa;");
  myConnection.Open();

  // Start a local transaction
  SqlTransaction myTrans = myConnection.BeginTransaction();

  SqlCommand myCommand = new SqlCommand();
  myCommand.Connection = myConnection;
  myCommand.Transaction = myTrans;
  try
  {
    myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
    myCommand.ExecuteNonQuery();
    myCommand.CommandText = "delete * from Region where RegionID=101";

    // Attempt to commit the transaction. 
    myCommand.ExecuteNonQuery();
    myTrans.Commit();
    Response.Write("Both records are written to database.");
  }
  catch (Exception ep)
  {
    // Attempt to roll back the transaction. 
    myTrans.Rollback();
    Response.Write(ep.ToString());
    Response.Write("Neither record was written to database.");
  }
  finally
  {
    myConnection.Close();
  }

That is ok. I would not use dispose calls either. I would be using - using statements ;) This is totally ok for code to demonstrate a concept. — TomTom, Feb 09 '14 at 08:15

how to rollback table if any error occur while executing multiple insert query simultaneously

1 Answers1