Several database operations that behave as one

Question

Several database operations that behave as one

Navigation

#1 by (3 votes)
#2 by (3 votes)

0

I am doing a system in which, on occasion, I must perform 3 or more movements in the database: read a field, insert in another table, and update some tables; the situation here is that, sometimes, the insertion or update is not done due to some unexpected error, in those cases I require that all the operations carried out until the error, are undone. Is there a way to let C # or SQL know that you should undo them?

To give an example, when capturing the invoice of a supplier, the following operations are carried out:

It reads, from a table, the consecutive one for the insertion in CxP (accounts payable)

The consecutive mentioned in the previous point increases

The document data is written in the CxP table

The existence in the product table is modified

A record is added to the Kardex table

What I want to know is if there is any way of, if there should be an error, for example in point 4, that I made a "rollback" all the previous points.

Thank you very much for your attention and your time.

sql c# sql-server

asked by antonio_veneroso 03.03.2017 в 20:14

source

2 answers

3

It is possible to manage a transaction in conjunction with a TRY CATCH in addition to displaying the message of the exception that was generated, for example:

BEGIN TRY
    BEGIN TRANSACTION
    -- Aquí van todas las operaciones en base de datos que desees realizar:

    SELECT Id
    FROM Tabla

    UPDATE Tabla2
    SET Valor = 'ValorNuevo'

    DELETE
    FROM Tabla3
    WHERE Id = IdRecuperado

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION

    SELECT ERROR_MESSAGE()
END CATCH

answered by 03.03.2017 в 20:18

Extract part of a text that is in the middle of a string Doubt ajax in data

score 3 · Accepted Answer

As Flxtr says or you can also do it from C # with SqlTransaction.

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

The same would be if you wanted to replace the queries embedded by stored procedures, you would only have to create a command for each of them on the same connection.

link

Greetings