Save all changes at once - MS SQL-CodePudding

I have a scenario where I need to run multiple inserts but only save changes to database at the very end. If any exception happens for any of the inserts, the previous inserts should not be saved as well. It's like all or nothing transaction.

I know that we can achieve this using Entity Framework, by calling _context.SaveChanges() outside the foreach loop of inserts. But, is there any option to do this easily with native SQL queries?

This existing project I am working on doesn't have Entity Framework setup. So, before I go the route of adding Entity Framework to this project, I just want to know if there is any other easy option to solve this problem. Thanks!

CodePudding user response：

In a SQL Stored Procedure, assuming use of SQL server:

--@insertdata could be of type xml or json depending on your version
--of sql server and use it to do all inserts
--in any case, you can set as many parameters as you need to do the work
@insertdata xml = null

Begin Try
    --Begin Tran
    BEGIN TRAN

    --Do INSERTS

    --Commit the Transaction
    COMMIT TRAN
End Try

BEGIN Catch
    --If we have an exception
    IF @@TRANCOUNT > 0 ROLLBACK;

    --Return the ErrorMessage
    --SELECT ERROR_MESSAGE()

    --OR as suggested, THROW so the exception is raised
    THROW
END Catch

CodePudding user response：

what you are looking for is Transactions,

"A transaction is the logical work unit that performs a single activity or multiple activities in a database. Transactions may consist of a single read, write, delete, or update operations or a combination of these."

learn more here and here