Calling Transaction Safe Stored Proceedures

[Stedo]

hi,

I am currently working in C# with set of stored procedures in an MS SQL database. When I do an update in the MS SQL database there is no problem as I create an SqlTransaction and call the commit method to run the command. This works find until I need to call a stored procedure which uses both Updates and Selects, i.e. I want to have a transaction safe command but I also want to store the results of the command in a SqlDataReader after the transaction has been completed! Is there any way to do this?

Thanks in advance
Steve

 

[RiverGuy]

How about coding the transaction into the stored procedure instead? If your logic rolls it back, then your stored procedure can return a code letting you know this.

 

[Stedo]

hi,

Yup, I'm looking at that option however it would be nicer if I could rollback in the same way I do when I perform updates.

Thanks
Steve