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

Posted on 2011-05-04
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

    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:
    LVL 9

    Assisted Solution

    Yes, you can use OleDbCommand.Transaction  object to handle your more than one sql statement as one transaction
    LVL 21

    Accepted Solution

    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

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

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now