Transactions in ADO.NET or SQL SP

[Don]

What is the best approach?

I've created a .NET 2.0 windows service which queries & updates data in SQL
using Stored Procedures.
Note, this windows service spawns a few worker threads which query/update
the database - thus ending up with multiple simultaneous database connections
(queries/updates).

Currently I've implemented transactions in the SQL SP (also using
XACT_ABORT, etc...), as WELL as the ADO.NET layer.

However, I've been told that its best to implement it just at the ADO.NET
layer.
The more I think of this, the more sense it makes.

Surely there's a best pratice for this!

 

[Cor Ligthert[MVP]]

Don,

There is no "Best" approach as I forever write, yesterday all my programs
are worse then today, and those will be worse from tomorrow.

I always liked your real multi Tier approach in mainframe (or Unix)
situations, however it does not make much sense in a Windows Client Server
situation or in ASP.NET.

But maybe you can tell us your idea about the benefits of it, in the latter
situations. In my idea it is a hell of a job to keep track of concurrency as
the user is starting more programs in the same time that are using that
service in the Windows forms situation (and there can in my idea be the only
benefit).

Cor