0

I have C# code (wcf) connects to a DB and inserts an entire "report" including address table, person table, etc. which all must be done by calling stored procedures. On a side note, some stored procedures may be called multiple times (as we may have multiple people and multiple addresses, etc in a report)

Insert address (by calling the spInsertAddress) Insert person (by calling the spInsertPerson) Insert Item (calling another stored procedure) throws an error (maybe too many characters, or inserting wrong value for a date/time field)

How do I undo all the changes the stored procedures made since they have already been made?

Found this article on MSDN explaining how you can add a transaction via C# and using a try/catch you can either commit or rollback the transaction

Kairan
  • 5,342
  • 27
  • 65
  • 104
  • Write a stored procedure that calls the other ones inside a transaction. – Dan Bracuk May 22 '13 at 22:51
  • @DanBracuk Is it possible from C# that I can send a query to the DB to say Begin Transaction, then run all the calls the stored proc, then make another query to the Db to say Commit or Rollback? – Kairan May 22 '13 at 23:10
  • It's possible from c# to prevent errors such as too many characters. – Dan Bracuk May 23 '13 at 00:09

2 Answers2

1

You can call BeginTransaction from C# (MSDN) and then Commit or Rollback the transaction as needed. But you have to make sure that the stored procedures do not themselves execute a COMMIT or ROLLBACK statement. Be advised that Sql Server does not support nested transactions the way you might expect.

Marc Shapiro
  • 571
  • 2
  • 2
  • I think this solution would work well for my particular needs. To add on I found MSDN article with example: http://msdn.microsoft.com/en-us/library/86773566.aspx – Kairan May 23 '13 at 00:56
  • @Kairan - Some additional information on the joys of nested transactions may be found [here](http://sqlserverpedia.com/wiki/Nesting_Transactions). – HABO May 23 '13 at 01:57
0

From my experience, after a transaction commits there is no rollback. I'd suggest you rewrite the code that calls the inserts ensuring you manage the values sent so errors are not an issue. It is best practice to validate all data prior to saving to the DB. You didn't mention the DB in use, some DB have different options available.

Woltz
  • 1
  • 1
  • I am using SQL 2008 R2, but since I am calling multiple stored procedures having a rollback in a each individual stored procedure would only roll back what that stored procedure did, and not its previous called stored procedures – Kairan May 23 '13 at 00:51