Transaction with stored procedure

[ucasesoftware]

i need to use this :

Private Shared Sub Demo1()
Dim db As SqlConnection = New SqlConnection(" connstringhere" )
Dim transaction As SqlTransaction
db.Open
transaction = db.BeginTransac tion
Try
call (New SqlCommand("INS ERT INTO TransactionDemo " + "(Text)
VALUES ('Row1');", db, transaction)).E xecuteNonQuery
call (New SqlCommand("INS ERT INTO TransactionDemo " + "(Text)
VALUES ('Row2');", db, transaction)).E xecuteNonQuery
call (New SqlCommand("INS ERT INTO CrashMeNow VALUES " + "('Die',
'Die', 'Die');", db, transaction)).E xecuteNonQuery
transaction.Com mit
Catch sqlError As SqlException
transaction.Rol lback
End Try
db.Close
End Sub


+++>>>> But how can i do this with a stored procedure each time ?

Call (New SqlCommand("myS P", myConnexion,
myTransaction)) .ExecuteNonQuer y()

but i need the command type : stored procedure and the parameters ????

how can i do ?

[Chris]

Re: Transaction with stored procedure

ucasesoftware wrote:[color=blue]
> i need to use this :
>
> Private Shared Sub Demo1()
> Dim db As SqlConnection = New SqlConnection(" connstringhere" )
> Dim transaction As SqlTransaction
> db.Open
> transaction = db.BeginTransac tion
> Try
> call (New SqlCommand("INS ERT INTO TransactionDemo " + "(Text)
> VALUES ('Row1');", db, transaction)).E xecuteNonQuery
> call (New SqlCommand("INS ERT INTO TransactionDemo " + "(Text)
> VALUES ('Row2');", db, transaction)).E xecuteNonQuery
> call (New SqlCommand("INS ERT INTO CrashMeNow VALUES " + "('Die',
> 'Die', 'Die');", db, transaction)).E xecuteNonQuery
> transaction.Com mit
> Catch sqlError As SqlException
> transaction.Rol lback
> End Try
> db.Close
> End Sub
>
>
> +++>>>> But how can i do this with a stored procedure each time ?
>
> Call (New SqlCommand("myS P", myConnexion,
> myTransaction)) .ExecuteNonQuer y()
>
> but i need the command type : stored procedure and the parameters ????
>
> how can i do ?
>[/color]

To call a stored proc do this: (note: I just typed this in here, there
are errors but it should give you the idea)

Dim db As SqlConnection = New SqlConnection(" connstringhere" )
Dim transaction As SqlTransaction
dim cmd as new sqlcommand
db.Open
transaction = db.BeginTransac tion
cmd.transaction = transaction
cmd.commandtype = storedprocedure
cmd.parameter.a dd(...)
Try
cmd.parameter(x ).value = ....
cmd.executenonq uery
'note that you don't have to make a new command object every time.
cmd.parameter(x ).value = ....
cmd.executenonq uery

Catch sqlError As SqlException
transaction.Rol lback
End Try
db.Close
End Sub

[ucasesoftware]

Re: Transaction with stored procedure

but i have to do 3 stored procedure in 1 transaction...

it's possible with this exemple ?

[Jim Underwood]

Re: Transaction with stored procedure

You can do all the SQL statements in a single stored procedure, and handle
the transaction within the stored procedure instead of in the .net code.
Just pass in all of the necessary parameters to process all 3 statements.


"ucasesoftw are" <ucasesoftware@ hotmail.fr> wrote in message
news:1131046697 .598296.289210@ z14g2000cwz.goo glegroups.com.. .[color=blue]
> but i have to do 3 stored procedure in 1 transaction...
>
> it's possible with this exemple ?
>[/color]

[ucasesoftware]

Re: Transaction with stored procedure

negative

cause one of the 3 are in another database

[Jim Underwood]

Re: Transaction with stored procedure

I see...

You could try using linked servers/remote servers to do the updates from one
stored proc, but that can cause more problems.

Here is one approach I used when trying to update two databases, one oracle
and one SQL Server...

I started a transaction (oracle stored procedure) against the one database.
If the transaction succeeded, I executed the (SQL) stored procedure against
the second database. If the second (SQL) transaction succeeded, I commited
the first (oracle) transaction. If it failed I rolled back the transaction.

not sure if this is at all helpful. Unfortunately I no longer have the code
that I used for this either.

"ucasesoftw are" <ucasesoftware@ hotmail.fr> wrote in message
news:1131048761 .767849.204250@ g43g2000cwa.goo glegroups.com.. .[color=blue]
> negative
>
> cause one of the 3 are in another database
>[/color]

[ucasesoftware]

Re: Transaction with stored procedure

If the 3rnd don't succes how you rolled back the 1rst one ?

[Chris]

Re: Transaction with stored procedure

ucasesoftware wrote:[color=blue]
> but i have to do 3 stored procedure in 1 transaction...
>
> it's possible with this exemple ?
>[/color]

Yes, as long as you use the same transaction object, they are all
included in the same transaction. You can even use the same
commandojbect. Just change the commandtext property.

Something like

try

Dim Cmd as new SqlCommand
cmd.connection = connection
cmd.transaction = transaction
cmd.commandtype = storedproecdure

cmd.parameters. add(..)
cmd.parameters. add(..)
cmd.parameters. value = ...
cmd.parameters. value = ...
cmd.commandtext = "Proc1"
cmd.executenonq uery

cmd.parameters. clear
cmd.parameters. add(..)
cmd.parameters. add(..)
cmd.parameters. value = ...
cmd.parameters. value = ...
cmd.commandtext = "Proc2"
cmd.executenonq uery

cmd.parameters. clear
cmd.parameters. add(..)
cmd.parameters. add(..)
cmd.parameters. value = ...
cmd.parameters. value = ...
cmd.commandtext = "Proc3"
cmd.executenonq uery

catch ex as exception
'rollback
end try

[Jim Underwood]

Re: Transaction with stored procedure

Thee are only 2 transactions in my app...

The first is done as a transaction in classic asp, and is not committed
until the second succeeds.

If the second hits any errors then the SP for the second transaction
performs the rollback and returns an error to the application.

When the app gets the error it rolls back the first transaction. If it gets
a success it commits the first transaction.


"ucasesoftw are" <ucasesoftware@ hotmail.fr> wrote in message
news:1131049741 .914763.192860@ g14g2000cwa.goo glegroups.com.. .[color=blue]
> If the 3rnd don't succes how you rolled back the 1rst one ?
>[/color]

[ucasesoftware]

Re: Transaction with stored procedure

thx a lot Chris

it's help me a lot :)