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.BeginTransaction
Try
call (New SqlCommand("INSERT INTO TransactionDemo " + "(Text)
VALUES ('Row1');", db, transaction)).ExecuteNonQuery
call (New SqlCommand("INSERT INTO TransactionDemo " + "(Text)
VALUES ('Row2');", db, transaction)).ExecuteNonQuery
call (New SqlCommand("INSERT INTO CrashMeNow VALUES " + "('Die',
'Die', 'Die');", db, transaction)).ExecuteNonQuery
transaction.Commit
Catch sqlError As SqlException
transaction.Rollback
End Try
db.Close
End Sub


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

Call (New SqlCommand("mySP", myConnexion,
myTransaction)).ExecuteNonQuery()

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

how can i do ?

 

[Chris]

i need to use this :

Private Shared Sub Demo1()
Dim db As SqlConnection = New SqlConnection("connstringhere")
Dim transaction As SqlTransaction
db.Open
transaction = db.BeginTransaction
Try
call (New SqlCommand("INSERT INTO TransactionDemo " + "(Text)
VALUES ('Row1');", db, transaction)).ExecuteNonQuery
call (New SqlCommand("INSERT INTO TransactionDemo " + "(Text)
VALUES ('Row2');", db, transaction)).ExecuteNonQuery
call (New SqlCommand("INSERT INTO CrashMeNow VALUES " + "('Die',
'Die', 'Die');", db, transaction)).ExecuteNonQuery
transaction.Commit
Catch sqlError As SqlException
transaction.Rollback
End Try
db.Close
End Sub


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

Call (New SqlCommand("mySP", myConnexion,
myTransaction)).ExecuteNonQuery()

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

how can i do ?

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.BeginTransaction
cmd.transaction = transaction
cmd.commandtype = storedprocedure
cmd.parameter.add(...)
Try
cmd.parameter(x).value = ....
cmd.executenonquery
'note that you don't have to make a new command object every time.
cmd.parameter(x).value = ....
cmd.executenonquery

Catch sqlError As SqlException
transaction.Rollback
End Try
db.Close
End Sub

 

[ucasesoftware]

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

it's possible with this exemple ?

 

[Jim Underwood]

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.

 

[ucasesoftware]

negative

cause one of the 3 are in another database

 

[Jim Underwood]

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.

 

[ucasesoftware]

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

 

[Chris]

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

it's possible with this exemple ?

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.executenonquery

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

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

catch ex as exception
'rollback
end try

 

[Jim Underwood]

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.

 

[ucasesoftware]

thx a lot Chris

it's help me a lot