Moving Data from one table to another

Question

Hie frnds,

I have two tables say "Orders" and "OrdersXML"

now I wish to remove orders which are more than 7 days old to OrdersXML table which i have done using sql adapter update function over dataset, which do this job taking 100 rows at a time. I want to delete the rows from the orders table which have been moved to OrdersXML table How can i Achieve this?? I want to ensure that a row in Orders is only deleted after it has been inserted into OrdersXML. I don't want to loose any of the data.. not even by accident..

Should I use trigger? or should i code it in C# itself?

as m using data-adapter I cant get the ids which have been inserted if any exception comes in between.. can i??

Answer 1

If you want to write the SQL with a script, use a SqlCommand with a SQL transaction:

BEGIN TRANSACTION

-- Copy rows from Orders to OrdersXML

-- Delete rows from Orders that were copied

COMMIT TRANSACTION

If you want to do this with objects and code, use a SqlTransaction object:

// code sample adapted from MSDN
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    SqlTransaction transaction = connection.BeginTransaction("SampleTransaction");
    SqlCommand command = connection.CreateCommand();
    command.Transaction = transaction;

    try
    {
        command.CommandText = "TODO"; // Copy rows from Orders to OrdersXML
        command.ExecuteNonQuery();
        command.CommandText = "TODO"; // Delete copied rows from Orders
        command.ExecuteNonQuery();

        // Attempt to commit the transaction.
        transaction.Commit();
    }
    catch (Exception ex)
    {
        try
        {
            // Attempt to roll back the transaction.
            transaction.Rollback();
        }
        catch (Exception ex2)
        {
            // This catch block will handle any errors that may have occurred
            // on the server that would cause the rollback to fail, such as
            // a closed connection.
        }
    }