Re: transaction with unknown nbr of commands

For maximum efficiency and scalability, you should implement your
explicit transaction inside of a stored procedure, not from client
code. The basic idea is you collect *all* input from the user first,
then call your wrapper stored procedure, passing it all of the user
input as input parameters. That stored procedure then initiates an
explicit transaction with a BEGIN TRAN statement, calling the other
stored procedures as required, passing each one the information it
needs. Each stored procedure returns success/failure information in
output parameters, passing back error information to the calling
sproc, which then commits if there are no errors, or rolls back if any
single operation fails. The wrapper sproc then passes success/failure
information back to the client. This requires a single SqlCommand and
1 round trip (assuming you remember to SET NOCOUNT ON in the sproc).

The idea is that you do not want to extend the boundaries of your
transaction outside of the server. By using
SqlConnection/SqlTransaction and passing separate commands inside of
an explicit transaction, you are multiplying the number of round trips
required by the number of commands you use if you go the
one-command-per-sproc route. Once you initiate a transaction, you are
holding exclusive locks on the resources involved for the duration of
that transaction, potentially blocking other processes attempting to
access the same data, and increasing the likelihood of deadlocks. This
applies to other processes that are only selecting data since SQL
Server's default isolation level of READ COMMITTED prevents data from
being read when it is locked.

--Mary

On Mon, 29 Aug 2005 09:15:17 -0700, Alex S Moore <Alex S
Moore@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

>Using vb.net for code behind on a asp.net application.
>
>I need to run a number of sqlcommands, based on user input. The commands
>need to run as a single transaction. The commands are all stored procedures.
>
>How do I do this? I was thinking that I could build an array of the
>sqlcommands, but I do not see how to do it. I looked at arraylist, but still
>not sure.
>
>Maybe the question could be stated as:
>When I run a sqlcommand in a transaction, does the sqlcommand need to
>persist, or can I just reuse it to run another stored procedure within the
>same transaction?
.

Relevant Pages

  • RE: Database updating issue
    ... I started a transaction on the connection class, passed this to the dataadapter, invoked the Update method on the dataadapter and finally commit the transaction. ... try threading the these calls to the stored procedure. ... To accomplish this task, the inserting of all rows, at the moment, I'm using ...
    (microsoft.public.dotnet.framework)
  • Re: SQLServerException: The server failed to resume the transactio
    ... Are you doing transaction management in your stored procedure? ... Can you post your stored procedure defintion, ... If I remove the transaction management code from the proc then everything ... I'm preparing a CallableStatement at the start of the loop and I'm reusing ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Error Handling.
    ... BEGIN TRANSACTION statement inside of a stored procedure, ... server is less efficient and more prone to problems. ... Error handling in your sprocs is necessary for performance, ...
    (microsoft.public.sqlserver.clients)
  • RE: Really tough ADO Stored Procedure Question. Please Help!!!
    ... I'm more PowerBuilder and/or ADO .Net than straight ADO, ... Because, if you have start 2 transaction, effectively nesting them, then you ... And the SET before the CREATE of the Stored Procedure will not ...
    (microsoft.public.sqlserver.server)
  • Transaction im SqlCommand und in der Stored Procedure
    ... wenn ich einem SqlCommand eine Transaction zuweise und der SqlCommand eine ... Stored Procedure aufruft, die ebenfalls eine Transaction öffnet (BEGIN ... was macht der Sql Server dann? ...
    (microsoft.public.de.german.entwickler.dotnet.datenbank)