`SqlTopicRepository.Save()`: Wrap recursive in a single transaction

[JeremyCaney]

Currently, a recursive Save() issues a separate SqlConnection/SqlCommand per topic via the CreateTopic/UpdateTopic stored procedures, with no transaction spanning the entire operation. If persistence fails partway through a large recursive save, the topics processed so far are left committed in Topics while the remainder are not, leaving the tree in a partially-saved state with no way to roll back or safely retry.

Implementation Notes

SqlTopicRepository.Save() (and its recursion) should open a single SqlTransaction for the full recursive operation—covering every CreateTopic/UpdateTopic/MoveTopic call issued during that save—and commit only once every topic in the tree has been persisted successfully. On any failure, the transaction should roll back so the database is left exactly as it was before the save began, rather than in a partial state.

Given the existing CreateTopic/MoveTopic stored procedures already use SERIALIZABLE/TABLOCK for their own nested-set arithmetic, the transaction should be scoped to avoid holding that lock for the full duration of a very large recursive save where avoidable; this will require some care.

Affected Files

• OnTopic.Data.Sql/SqlTopicRepository.cs

Tasks

• Wrap the recursive Save() persistence loop in a single SqlTransaction
• Roll back the transaction on any exception during the recursive save
• Add a test that simulates a failure partway through a multi-topic recursive save and asserts no rows were committed
• Document the transactional guarantee on Save() in its XML doc comments