2

I want to use Transaction and rollback first time but I am confused as if how to in my case?

I want to insert records in multiple tables and I need to use for loop to insert more than one record in one table. And also I want to delete all records from all tables if some error occurred.

Let's take 3 tables as an example:

   Insert into table1 values (a, b, c);

   for(int i = 0; i < gridview1.rows.count; i++)
   {
      Insert into table 2 values (i, b, c);
   }

   Insert into table 3 values (a, b, c);

So this is just a short example of what I want. I tried few tutorials but those seems to be for different cases and pretty easy.

I have to use SQL Server 2005 and cannot go to 2008 or above..

Thanks in advance

Edit

Currently I am doing this using multiple stored procedure (one for each table) And I want to implement a transaction in it. Using Asp.net if possible will also be ok for me.

Improve this question

1 Answer 1

Reset to default
3

This is very basic ADO.NET - you need to set up your connection and transaction, and then you need three commands that "participate" in that transaction. You execute your commands, and if all goes well, you commit the transaction. If anything fails, a try...catch will kick in and roll back the transaction to the state before this all started.

Code will look something like this:

// set up your connection
using (SqlConnection conn = new SqlConnection("--your-connection-string-here--"))
{
    // start a transaction
    using (SqlTransaction transaction = conn.BeginTransaction())
    {
        try
        {
            conn.Open();

            // create a command for your first stored procedure
            // and make sure it uses the transaction from above
            using (SqlCommand cmdProc1 = new SqlCommand("dbo.Procedure1Name", conn, transaction))
            {
                // set the parameters
                cmdProc1.Parameters.Add("@ParamA", SqlDbType.Int).Value = a;
                cmdProc1.Parameters.Add("@ParamB", SqlDbType.Int).Value = b;
                cmdProc1.Parameters.Add("@ParamC", SqlDbType.Int).Value = c;

                // execute stored procedure
                cmdProc1.ExecuteNonQuery();
            }

            // create a command for your second stored procedure
            // and make sure it uses the transaction from above
            using (SqlCommand cmdProc2 = new SqlCommand("dbo.Procedure2Name", conn, transaction))
            {
                // set the parameters
                cmdProc2.Parameters.Add("@ParamI", SqlDbType.Int);
                cmdProc2.Parameters.Add("@ParamB", SqlDbType.Int);
                cmdProc2.Parameters.Add("@ParamC", SqlDbType.Int);

                // loop and set parameter values 
                for (int i = 0; i < gridview1.rows.count; i++)
                {
                    cmdProc2.Parameters["@ParamI"].Value = i;
                    cmdProc2.Parameters["@ParamB"].Value = b;
                    cmdProc2.Parameters["@ParamC"].Value = c;

                    cmdProc2.ExecuteNonQuery();
                }
            }

            // create a command for your third stored procedure
            // and make sure it uses the transaction from above
            using (SqlCommand cmdProc3 = new SqlCommand("dbo.Procedure3Name", conn, transaction))
            {
                // set the parameters
                cmdProc3.Parameters.Add("@ParamA", SqlDbType.Int).Value = a;
                cmdProc3.Parameters.Add("@ParamB", SqlDbType.Int).Value = b;
                cmdProc3.Parameters.Add("@ParamC", SqlDbType.Int).Value = c;

                // execute stored procedure
                cmdProc3.ExecuteNonQuery();
           }

           // if everything went well - commit the transaction!
           transaction.Commit();
       }
       catch (Exception exc)
       {
           // log the exception, rollback the transaction
           transaction.Rollback();
       }
    }
}
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

That looks good and pretty easy to..i am trying to implement it. just for my knowledge can you explain me how those it rolls back it? i mean once inserted then how does it deletes that record as we have not mentioned delete query...
And other one is Does using these makes any impact on pages performance?
@ALOK: the rollback returns the database to the state before the transaction started - that's all you need to know. How this is done in detail is of no importance, and is dependent on the concrete database and many other factors. Just be assured: the rollback goes back to the state as it was, before the transaction started
@ALOK: no, this probably doesn't have any measurable impact on performance. Any SQL statement in SQL Server is always executed in the context of its own transaction anyway - so it's really not doing anything extraordinary
Thanks....Its good to get answers from developers like you as we don't just get executable code to use it and forget it but also understand what's going on.....Thanks...it will be great to be connected with you.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.