Sending several ExecuteNonQuery() to same database, which transaction type and error checking to use? (.NET)

Question

I have an asp.net web site which will send several ExecuteNonQuery() statements (stored proc) to the same database (Sql server 2005). MSDTC service is turned off on both web and database servers.

These statements need to run all successfully, all or none. So I need to wrap them in transaction. Is the best way to use SQLTranscation? Attach the Command object to a transaction and then execute these statements through the command object? What kind of error checking needs to be done in the stored procs so that successful commits or rollbacks occur in the code behind?

Answer 1

You could just the System.Transactions class in .NET. You can see some quickstart examples of how to use them here. You can then wrap your ExecuteNonQuery() elements in a TransactionScope:

using (TransactionScope ts = new TransactionScope())
{
    // Your DB inserts

    ts.Complete();
}

Answer 2

See this blog for an example of stored procedure template designed to handle errors and transactions atomically, if possible: Exception handling and nested transactions.

In the client you need to either start a SqlTransaction and attach it to each SqlCommand:

using (SqlTransaction tn = conn.BeginTransaction())
{
    cmd1 = new SqlCommand("...", conn, trn);
    cmd1.ExecuteNonQuery();
    cmd2 = new SqlCommand("...", conn, trn);
    cmd2.ExecuteNonQuery();
    ...
    trn.Commit();
}

or you can use the CLR transactions, since ADO.Net is aware of them and will enroll your SqlCommands in the transaction.

No mater what, you'll have to execute all your commands on the same connection, otherwise will require a distributed transaction (even if is on the same server).

Answer 3

You would be a lot safer if you could make the transaction a single database call--say call a stored procedure that calls all the other procedures in turn. That way the transaction is managed entirely on the database, and is not "spread" across the database and web server. If the transaction is managed by the webserver and has to be "held" across X stored procedures, and any one of them fails, then the top-level process -- your website--has to somehow manage the rollback (not too hard) and any necessary cleanup (could be darn hard).