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.
