Integrity with multiple commands

How can I make sure that a couple of commands are either all executed on the database or none of them. For example right now I have an insert, update and delete command. I'm calling each of them with a SqlCommand. So I am afraid that that one of them might be executed, then there's a bad connection and the other two are not. How can I prevent this so that only all commands or nothing is executed on the database?

Write them in a stored procedure~

Stored procedures are a precompiled collection of SQL statements and optional control-of-flow statements stored under a name and processed as a unit.

In short, it's something atomic, a lot of database use some mechanism such as rolling back when a sp is terminated unexpectedly, so either none or all of your command in a sp will be executed~

|||

A stored procedure has absolutely nothing to do with it. You want to place your code within a transaction. You can do this manaully by specifying a BEGIN TRANSACTION (your statements here) and then either ROLLBACK TRANSACTION or COMMIT TRANSACTION, *OR* you can use the sqltransaction object to put multiple sqlcommands within the same transaction.

You can of course put the whole thing in a stored procedure as well for convience, but putting things in a sp won't guarantee they will either all commit or rollback.

This would be an example of what you can place within the commandtext of the sqlcommand property:

dim cmd as new sqlcommand("SET @.RetVal=0; BEGIN TRANSACTION; INSERT INTO Table1(col1) VALUES (@.col1); IF @.@.ERROR=0 BEGIN INSERT INTO Table2(idcol,col2) VALUES (SCOPE_IDENTITY(),@.col2) IF @.@.ERROR=0 BEGIN COMMIT TRANSACTION SET @.RetVal=1 END END IF @.RetVal=0 ROLLBACK TRANSACTION")

cmd.Parameters.Add("@.col1",varchar).value=something

cmd.Parameters.Add("@.col2",varchar).value=something

cmd.Parameters.Add("@.retval",int).Direction=output

cmd.executenonquery()

if cmd.Parameters("@.retval").Value=0 then

' It failed

end if

You can also do it this way:

dim cmd as new sqlcommand("SET @.RetVal=0; SET XACT_ABORT ON; BEGIN TRANSACTION; INSERT INTO Table1(col1) VALUES (@.col1); INSERT INTO Table2(idcol,col2) VALUES (SCOPE_IDENTITY(),@.col2); COMMIT TRANSACTION; SET @.RetVal=1")

That should also work as well.