CODEWITHSUNDEEP
c#asp.netsql-servertransactions
1110
1110

Reputation: 6829

Is there any difference between transactions in asp.net and stored procedure

If I have 3 sql queries and all three must be executed or none. Is there any difference if I write this query in asp.net code or stored procedure?

Upvotes: 1

Views: 1783

Answers (5)

Ray
Ray

Reputation: 396

Both are valid ways for managine transactions. Using a stored procedure has the advantage of less network traffic, probably faster execution, better encapsulation of the database stuff etc, while using asp.net allows you to involve some high-level logic or using parts of your other programming which may influence what your queries do.

Upvotes: 2

Dennis Traub
Dennis Traub

Reputation: 51634

When using SqlConnection.BeginTransaction() there is no difference.

This question, though, is a possible duplicate of What's the difference between an SQL transaction at the stored procedure level and one at the SqlConnection level?

Upvotes: 0

Joe Ratzer
Joe Ratzer

Reputation: 18549

You can use the TransctionScope in .NET in the same way as you would use a transaction in the database.

Be careful to choose the right isolation level though.

By default, TransactionScope executes with the Serializable isolation level. This is probably not what you want.

The database default isolation level will more likely be Read committed.

Upvotes: 1

Joachim VR
Joachim VR

Reputation: 2340

For efficiency reasons you'd better write a SP with a transaction that rolls back on error. If only for simplicity, direct SQL, and the fact that SP get kind of compiled once, for faster execution in the future.

Upvotes: 0

Incognito
Incognito

Reputation: 16577

If you are doing everything for current connection on one DB instance basically you will see no difference to use T-SQL transactions (BEGIN TRAN/COMMIT TRAN) or ADO.NET transactions(TransactionScope, BeginTransaction...)

But note that you can group multiple connections (requests to several DB instances) in one transaction using transaction scope.

Upvotes: 2

Related Questions