ASP.NET & SQL SERVER 2005 - Transaction on the client level and not database level

Posted on 2011-05-04
Medium Priority
Last Modified: 2012-05-11
Hi Experts,
I have two SQL Server 2005 tables, Orders and Order_Details.
I am using ASP.NET and dealing with the tables with Stored Procedures.
I want to save an order and its details in a transaction, that if a failure happens, the whole transaction rolls back and not to save part of the orders.
My problem is that since the database calls are happening individually, ie. Each row of the order details grid is read and sent at once, how can I do a transaction on that level?

Thanks in advance,
Question by:feesu
LVL 21

Assisted Solution

by:Alfred A.
Alfred A. earned 600 total points
ID: 35687617
If you are using ADO.NET, you can use SQLConnection.BeginTransaction to start a database transaction.  Then, you can either Commit or Rollback depending on your logic.

Check the link below:


Assisted Solution

sarabhai earned 600 total points
ID: 35688280
Yes, you can use OleDbCommand.Transaction  object to handle your more than one sql statement as one transaction

LVL 21

Accepted Solution

Alpesh Patel earned 800 total points
ID: 35688696
You can achieve at SQL level and application level.

1. For SQl level, Create one SP in that use all SP /SQL command to save Order abd Order details
Start Transaction
Commit Transaction

2. In Application use TransactionBlock. But make sure DTS service is enabled on Application server.
Function InserORder {...}
Function InserORderDetails {...}
Function InserWholeORder {
TRnsactionblock {
call InserORder {...}
call InserORderDetails {...}


Author Comment

ID: 35713522
Thanks experts for your replies. I am going to try them out and get back to you shortly!

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question