Multiple Stored Procedure Calls within single transaction

  • Thread starter Thread starter BostonNole
  • Start date Start date
B

BostonNole

I am using SQL 2000 and VB.NET (VS 2005).

I have three stored procedure: sp_A, sp_B and sp_C

I need to be able to call sp_A once and sp_B and sp_C 1 to n number of
times all within a single transaction that can be rolled back if any
of the calls to the stored procedures fail.

sp_A - inserts to table ZZZ
sp_B - inserts to table YYY (but needs to be called multiple times)
and is dependent on the primary key being inserted into table ZZZ that
sp_A is inserting into.
sp_C inserts to table QQQ (but needs to be called multiple times) and
is dependent on the primary key being inserted into table ZZZ that
sp_A is inserting into.

I would prefer to use Enterprise Library 3 for my database calls.
 
I am using SQL 2000 and VB.NET (VS 2005).

I have three stored procedure: sp_A, sp_B and sp_C

I need to be able to call sp_A once and sp_B and sp_C 1 to n number of
times all within a single transaction that can be rolled back if any
of the calls to the stored procedures fail.

sp_A - inserts to table ZZZ
sp_B - inserts to table YYY (but needs to be called multiple times)
and is dependent on the primary key being inserted into table ZZZ that
sp_A is inserting into.
sp_C inserts to table QQQ (but needs to be called multiple times) and
is dependent on the primary key being inserted into table ZZZ that
sp_A is inserting into.

I would prefer to use Enterprise Library 3 for my database calls.
Click to expand...

Are you expecting someone to write the code for you?

I do not know what Enterprise Library 3 is, but it's easy to do what
you want with ADO.NET. Check out:

https://msdn2.microsoft.com/en-us/library/system.data.odbc.odbctransaction(VS.71).aspx
 
I am not asking anyone to write any code for me. Your link is for an
ODBC class that is for .NET 1.1 only. I am using .NET 2.0.
Click to expand...

ODBC class is part of the .NET 2.0 framework too.

AFAIK, you can't wrap multiple SPs in a transaction. Each SP has it's own
execution context. But I could be wrong.
 
I am using SQL 2000 and VB.NET (VS 2005).

I have three stored procedure: sp_A, sp_B and sp_C

I need to be able to call sp_A once and sp_B and sp_C 1 to n number of
times all within a single transaction that can be rolled back if any
of the calls to the stored procedures fail.

sp_A - inserts to table ZZZ
sp_B - inserts to table YYY (but needs to be called multiple times)
and is dependent on the primary key being inserted into table ZZZ that
sp_A is inserting into.
sp_C inserts to table QQQ (but needs to be called multiple times) and
is dependent on the primary key being inserted into table ZZZ that
sp_A is inserting into.

I would prefer to use Enterprise Library 3 for my database calls.
Click to expand...

If you have your Enterprise Library Database object, you can
call .CreateConnection method to get the connection. Then with the
connection instance, you can call BeginTransaction.

Then for each stored proc you need to call, pass that transaction in
when you call the various ExecuteReader, ExecuteNonQuery, etc.
methods.

Then afterwards, assuming everything went ok, call the Commit method
on the transaction object.

HTH

Chris
 
If you have your Enterprise Library Database object, you can
call .CreateConnection method to get the connection. Then with the
connection instance, you can call BeginTransaction.

Then for each stored proc you need to call, pass that transaction in
when you call the various ExecuteReader, ExecuteNonQuery, etc.
methods.

Then afterwards, assuming everything went ok, call the Commit method
on the transaction object.

HTH

Chris- Hide quoted text -

- Show quoted text -
Click to expand...

Thank you Chris, I think this is exactly what I was looking for.
 
Back
Top