Rollback INSERT Command in C#.NET

Question

I had some confusion which I want to clear it - I am inserting values into database using ADO.NET. Let say I want to insert 10 item if I encounter error while inserting data of 5th item it should roll back whatever I had inserted into the database.

I just read the concept of Transaction and Rollback method and also tried to implement it in the program but still it insert 4 item and give me error message of 5th item. It doesn't roll back insert query.

Does transaction and roll back method solved my issue or I need to used other alternative.

here is my code,

for (int i = 0; i < itemLength - 1; i++)
            {
                //--- Start local transaction ---
                myTrans = Class1.conn.BeginTransaction();

                //--- Assign transaction object and connection to command object for a pending local transaction ---
                _insertQry = Class1.conn.CreateCommand();
                _insertQry.Connection = Class1.conn;
                _insertQry.Transaction = myTrans;

                _insertQry.CommandText = "INSERT INTO Product_PropertyValue(ItemNo, PropertyNo, ValueNo) VALUES (@ItemNo, @PropertyNo, @ValueNo)";

                //_insertQry = new SqlCommand("INSERT INTO Product_PropertyValue(ItemNo, PropertyNo, ValueNo) VALUES (@ItemNo, @PropertyNo, @ValueNo)", Class1.conn);

                _insertQry.Parameters.AddWithValue("@ItemNo", _itemNo[i]);
                _insertQry.Parameters.AddWithValue("@PropertyNo", _propNo);
                _insertQry.Parameters.AddWithValue("@ValueNo", _propValue);

                _insertQry.ExecuteNonQuery();
                myTrans.Commit();
            }

Can anyone help me?

Answer 1

I did 2 modification to your code

1) Move the BeginTransaction() outside the for loop, So that all your 10 INSERt statements are in a single transaction, that is what you want if you want them to be atomic

2) added a TRY/CATCH block, so that you can roll back in case of errors.

        //--- Start local transaction ---
        myTrans = Class1.conn.BeginTransaction();
        bool success = true;

        try
        {
            for (int i = 0; i < itemLength - 1; i++)
            {
                //--- Assign transaction object and connection to command object for a pending local transaction ---
                _insertQry = Class1.conn.CreateCommand();
                _insertQry.Connection = Class1.conn;
                _insertQry.Transaction = myTrans;

                _insertQry.CommandText = "INSERT INTO Product_PropertyValue(ItemNo, PropertyNo, ValueNo) VALUES (@ItemNo, @PropertyNo, @ValueNo)";

                //_insertQry = new SqlCommand("INSERT INTO Product_PropertyValue(ItemNo, PropertyNo, ValueNo) VALUES (@ItemNo, @PropertyNo, @ValueNo)", Class1.conn);

                _insertQry.Parameters.AddWithValue("@ItemNo", _itemNo[i]);
                _insertQry.Parameters.AddWithValue("@PropertyNo", _propNo);
                _insertQry.Parameters.AddWithValue("@ValueNo", _propValue);

                _insertQry.ExecuteNonQuery();
            }
        }
        catch (Exception ex)
        {
            success = false;
            myTrans.Rollback();
        }

        if (success)
        {
            myTrans.Commit();
        }

let me know if this doesn't works.

Answer 2

You are on the right path, ADO.NET supports transactions so you will be able to rollback on errors.

Posting your your code here would get you more specific guidance; However since your question is very generic, I will encourage you to follow the template provided by MSDN

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    // Start a local transaction.
    SqlTransaction sqlTran = connection.BeginTransaction();

    // Enlist a command in the current transaction.
    SqlCommand command = connection.CreateCommand();
    command.Transaction = sqlTran;

    try
    {
        // Execute two separate commands.
        command.CommandText =
          "INSERT INTO Production.ScrapReason(Name) VALUES('Wrong size')";
        command.ExecuteNonQuery();
        command.CommandText =
          "INSERT INTO Production.ScrapReason(Name) VALUES('Wrong color')";
        command.ExecuteNonQuery();

        // Commit the transaction.
        sqlTran.Commit();
        Console.WriteLine("Both records were written to database.");
    }
    catch (Exception ex)
    {
        // Handle the exception if the transaction fails to commit.
        Console.WriteLine(ex.Message);

        try
        {
            // Attempt to roll back the transaction.
            sqlTran.Rollback();
        }
        catch (Exception exRollback)
        {
            // Throws an InvalidOperationException if the connection 
            // is closed or the transaction has already been rolled 
            // back on the server.
            Console.WriteLine(exRollback.Message);
        }
    }
} 

Answer 3

It sounds like you are trying to achieve an atomic commit. It either inserts completely or doesn't insert at all.

Try something like the following

SqlTransaction objTrans = null;
        using (SqlConnection objConn = new SqlConnection(strConnString))
        {
            objConn.Open();
            objTrans = objConn.BeginTransaction();
            SqlCommand objCmd1 = new SqlCommand("insert into tbExample values(1)", objConn);
            SqlCommand objCmd2 = new SqlCommand("insert into tbExample values(2)", objConn);
            try
            {
                objCmd1.ExecuteNonQuery();
                objCmd2.ExecuteNonQuery();
                objTrans.Commit();
            }
            catch (Exception)
            {
                objTrans.Rollback();
            }
            finally
            {
                objConn.Close();
            }

Also take a look at http://www.codeproject.com/Articles/10223/Using-Transactions-in-ADO-NET