Is a simple question... In this chapter you talk about ADO.NET transactions, but using transactions inside SQL Server is not the better choice?
I'm in chapter 3 and I'm very happy with your book. It's my 4 book in ASP.NET 2.0 of WROX taht i'm reading.
Cheers!!!
Thanks!

Keeping transactions in the DB is the best choice, but requires a lot of stored procs, and can make certain kinds of processing more difficult since the C# code would have less control. You couldn't easily call 3 procs in a row on the same transaction. You could have a 4th proc that calls the other 3, or you can roll all the functionality into one proc. But regardless of how you do it, it can become messy.

Allowing C# to control transactions also makes it easier to include other non-SQL processing in the mix. You might do some SQL updates and then call a web service. If something goes wrong with the web service call, you can easily roll back the SQL updates.

For a small site like TBH, it's probably fine to keep most transactions in the DB. But as it scales up in size it would become a lot more difficult. I know of one company with over 1500 stored procedures because they require their coders to do everything with stored procs. This kind of thing almost demands a fulltime stored proc librarian who can coordinate the use of all of them. Maintenance also becomes extremely complex because their are a lot of different procs tied to various tables.

Eric

Englere,
When you refer to C# is the same for VB.NET, write?
You opened my eyes to another way to do the thigs, and I agree 100%with you.
Thanks!!

yes, of course.

The question shouldn't be "where should the transaction be", but rather "how will I manage transactions - should it be .NET code on application server, or stored procedures in the DB".

And then you have to consider the long term and how big a given system is likely to get, and what kind of distributed transactions might be needed over time.

The only wrong choice is the one you arrive at quickly! This is an important subject that deserves some thought. This is not a "one size fits all" kind of thing.

Eric