If insert fails for one record delete all previously inserted row, is is 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:

Insert all record only in case that whole data is valid, and if one error occurred in inserting.
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;

        }

What is the condition for validation? Could you share some of your code please? — shree.pat18, Nov 30 '13 at 15:34
Please show your code because, if I understand you correctly, the right approach is exactly using SqlTransaction or TransactionScope classes. — Steve, Nov 30 '13 at 15:34
You are creating separate transactions for every item - you need to wrap the loop in a transaction or send a collection of items to the `Ramiz_SavePack` method. If you rollback you are only rolling back a single transaction. You need to do all your database work within the same transaction — Charleh, Nov 30 '13 at 15:52
@Charleh. Do you mean that I should pass List to Ramiz_SavePack method and inside try close doing my foreach looping? — coder, Nov 30 '13 at 15:56
Yes - you need to loop through the WHOLE list with only 1 `SqlTransaction` object and rollback that single object if there is an error. You need ONE transaction... at the moment you have many — Charleh, Nov 30 '13 at 15:57

score 1 · Accepted Answer · answered Nov 30 '13 at 15:55

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>
}

answered Nov 30 '13 at 15:55

Charleh

13,749
3
37
57

It should work - try modifying your code and if it doesn't work update your question with the new code. If you did it as above and it didn't work then you are probably doing something wrong (probably something tiny too!) – Charleh Nov 30 '13 at 16:13
Right now I am trying to modify it. If not succes I will post my new code. Thanks – coder Nov 30 '13 at 16:22
Thank you a lot. I finally done it in right way. Also, thank you all guys for help. I decide to chose this as correct answer as it best fit to my code. Also @Steve provided answer with code, but that requeire many changes in my code so I decide for this one. – coder Nov 30 '13 at 16:50
I respect your decision, but I like to point out that in this way you loop two times on the objects to execute the inserts. It isn't the most efficient way to go. (Of course neither mine answer is the most efficient way, a [Common Table Expression](http://www.functionx.com/vcsharp/adonet/cte.htm) could offer the best way, but this is really a huge code difference) – Steve Nov 30 '13 at 17:59

Steve · Answer 2 · 2013-11-30T16:08:26.737

Your problem lies in the fact that you open and close the transaction for every object that you write in the database. Of course this means that when you hit a problem with your data, the previous correct one has already written to the database and committed. So it cannot be rolled back.

The solution is to open the connection and the transaction outside your data insert method and pass these object instances inside the method.

public int Ramiz_SavePack(IPacking pack, SqlConnection conn, SqlTransaction transaction)
{
    var comm = (SqlCommand)connector.GetCommand("Ramiz_Pack_Save");
    comm.Connection = conn;
    comm.CommandType = CommandType.StoredProcedure;
    comm.Transaction = transaction;
    ....
}


.....
try
{    
    using (var conn = (SqlConnection)connector.GetConnection())
    {
        conn.Open();
        SqlTransaction transaction = conn.BeginTransaction();
        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)
           }, conn, transaction);
        }
        pnlMessageSuccess.Visible = true;
     }
   }
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
    transaction.Rollback();
}

In this way the method that inserts data, when it fails, will raise an exception trapped by the calling code that has opened the connection and the transaction. This calling code could then call the rollback method to undo every object inserted so far.

score 0 · Answer 3 · answered Nov 30 '13 at 15:41

It is easier to make constraints on the data entry objects like constraining a certain Textbox to accept only numbers or whatever you would like . And as another idea .. try checking the data Accuracy before Querying it to the database

score 0 · Answer 4 · edited May 23 '17 at 10:31

0

One way is to create a DataTable from your list by mapping each object in the list to a row of the DataTable. Then you can loop through the DataTable rows to perform the insert operation, and include this code block inside a Transaction. This will allow you to prevent entering partial data.

You may refer to this link to get an idea how to do the list to DataTable conversion.

edited May 23 '17 at 10:31

Community

1
1

answered Nov 30 '13 at 15:51

shree.pat18

21,449
3
43
63

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

4 Answers4