If insert fails for one record delete all previously inserted row, is it possible in a transaction?

Question

I have method in my code like SaveListOfObjects which I execute inside foreach loop and then insert records to SQL Server.

It works great when there is no error in data I am inserting. But if error occured then only valid data is inserted in SQL.

I want to do following:

1. Insert all record only in case that whole data is valid, and if one error occurred in inserting.
2. Delete all previously saved data in SQL.

So, I already tried with TransactionScope and SqlTransaction classes and even with SQL TRANSACTION but only thing I could manage is insert valid data and non-valid data was omitted.

Now, as far as I search on web, I found that parallel transaction is not possible. Also, SQL has Isolation Level which prohibited parallel tasks.

Is there any possible way to accomplish insert in SQL like ALL or NOTHING?

UPDATE:

My code is as following:

public int Ramiz_SavePack(IPacking pack)
    {
        using (var conn = (SqlConnection)connector.GetConnection())
        {
            conn.Open();
            SqlTransaction transaction;
            var comm = (SqlCommand)connector.GetCommand("Ramiz_Pack_Save");
            comm.CommandType = CommandType.StoredProcedure;
            transaction = conn.BeginTransaction();
            comm.Transaction = transaction;
            int rowNum = 0;


            try
            {
                if (!string.IsNullOrEmpty(pack.BrojKolete))
                    comm.Parameters.Add("@BrojKolete", SqlDbType.NVarChar).Value = pack.BrojKolete;
                else
                    comm.Parameters.Add("@BrojKolete", SqlDbType.NVarChar).Value = DBNull.Value;
                comm.Parameters.Add("@Bosanski", SqlDbType.NVarChar).Value = pack.Bosanski;
                comm.Parameters.Add("@Kom", SqlDbType.Float).Value = pack.Kom;
                comm.Parameters.Add("@Vrsta", SqlDbType.NVarChar).Value = pack.Vrsta;
                comm.Parameters.Add("@Datum", SqlDbType.Date).Value = pack.Datum;
                comm.Parameters.Add("@BrojKamiona", SqlDbType.Int).Value = pack.BrojKamiona;

                 rowNum = comm.ExecuteNonQuery();
                transaction.Commit();

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                try
                {
                    conn.Close();
                    transaction.Rollback();
                }
                catch (Exception ex2)
                {
                    Console.WriteLine(ex2.Message);
                }

            }
            return rowNum;

        }
    }

and calling this method inside this:

 var pack = new Pack();
        for (int i = 1; i < lastRow; i++)
        {
            pack.Ramiz_SavePack(new Packing
            {
                BrojKolete = Convert.ToString(brojKoleteRange.Offset[i, 0].Value2),
                Bosanski = Convert.ToString(nazivArtiklaRange.Offset[i, 0].Value2),
                Kom = Convert.ToDouble(komRange.Offset[i, 0].Value2),
                Vrsta = Convert.ToString(vrstaRange.Offset[i, 0].Value2),
                BrojKamiona = int.Parse(ddlBrojKamiona.SelectedItem.Value),
                Datum = Convert.ToDateTime(txtDate.Text)
            });
            pnlMessageSuccess.Visible = true;

        }

Answer 1

It looks to me like you are looping and calling the save method for each object. This isn't a problem if the transaction exists around that loop, but it doesn't. You are rollback/committing each object separately

You need to either create a list of objects to save and send it into the save method, or create a transaction that wraps the loop e.g.:

var list = new List<Pack>();

foreach(<your loop>)
{
   list.Add(new Pack(<some values>);
}

SavePacks(list);

void SavePacks(IList<Pack> items) 
{
    <create connection and transaction here and loop through inserting each item, rollback on error>
}

or

using(var tran = new SqlTransaction()) 
{
    <Do save logic here for all items and rollback if something went wrong>
}