471,107 Members | 1,859 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,107 software developers and data experts.

Transaction with stored procedure

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 ?

Nov 23 '05 #1
9 10507
ucasesoftware wrote:
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
Nov 23 '05 #2
but i have to do 3 stored procedure in 1 transaction...

it's possible with this exemple ?

Nov 23 '05 #3
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" <uc***********@hotmail.fr> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
but i have to do 3 stored procedure in 1 transaction...

it's possible with this exemple ?

Nov 23 '05 #4
negative

cause one of the 3 are in another database

Nov 23 '05 #5
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" <uc***********@hotmail.fr> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
negative

cause one of the 3 are in another database

Nov 23 '05 #6
If the 3rnd don't succes how you rolled back the 1rst one ?

Nov 23 '05 #7
ucasesoftware wrote:
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
Nov 23 '05 #8
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" <uc***********@hotmail.fr> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
If the 3rnd don't succes how you rolled back the 1rst one ?

Nov 23 '05 #9
thx a lot Chris

it's help me a lot :)

Nov 23 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Deepak Mehta | last post: by
3 posts views Thread by Mark | last post: by
11 posts views Thread by harborboy76 | last post: by
1 post views Thread by Belee | last post: by
2 posts views Thread by Dino L. | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.