Я так понимаю, что Rollback берем в try catch, т.к. он может сам привести к исключительной ситуации? Тогда абсолютно безопасного элегантного решения нет. Могу только предложить включить всю нужную логику в хранимую процедуру и в ней пользоватся конструкциями begin tran/commit/rollback с правильной обработкой @@ERROR или BEGIN TRY/BEGIN CATCH блоков (только для 2005 версии)
Если СУБД SQL Server 2005, то можно использовать TransactionScope объект с конструкцией using. Если СУБД SQL Server 2000, то использовать этот вариант нельзя, т.к. в случае SQL Server 2000, транзакция будет распределенной.