CODEWITHSUNDEEP
sqlatomic
duncanportelli
duncanportelli

Reputation: 3229

SQL Multiple Commands

I am writing a method in C# which executes multiple SQL commands and returns a boolean value which determine if the insertions were successful or not. My problem is that I want to execute this command as an "all or nothing" command. This means that if I want to execute 3 different commands and the third command isn't executed correctly, the changes of the first two commands aren't committed. How can I do this in SQLServer please?

Upvotes: 1

Views: 1276

Answers (2)

Zoran Causev
Zoran Causev

Reputation: 360

If you want to execute the three commands from C#: 

SqlTransaction tran = connection.BeginTransaction();
try
{
   ...exec first command and get result...
   ...exec second commmand and get result...
   ...exec third command and get result...
   if (!firstResult || !secondResult || !thirdResult)
      throw new Exception();

   tran.Commit();
}
catch
{
   tran.Rollback();
}

If you want to execute the three commands in SQL server by calling a stored procedure, then use:

BEGIN TRANSACTION

cmd1
cmd2
cmd3

IF @Success = 1 THEN
  COMMIT TRANSATION
ELSE
  ROLLBACK TRANSACTION

You can also use try..catch blocks in SQL to catch exceptions and rollback the transaction if an error occurs.

Upvotes: 1

CarlosJavier
CarlosJavier

Reputation: 1045

You are searching for a Transaction, which group many SQL queries (often INSERTS, UPDATES or DELETES) into one single unit of work, you should surround your querys with BEGIN TRAN (at the beginning) and COMMIT TRAN (at the end). SQL server transaction

Upvotes: 0

Related Questions