Multiple Stored Procedure Calls within single transaction

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

[zacks]

On Nov 28, 12:52 pm, BostonNole <[e-mail]:

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.

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/li...on(VS.71).aspx

[BostonNole]

On Nov 28, 2:05 pm, [e-mail] wrote:

On Nov 28, 12:52 pm, BostonNole <[e-mail]:

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.

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/li...odbctransa...- Hide quoted text -

- Show quoted text -

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.

[Spam Catcher]

BostonNole <bo********@gmail.comwrote in news:9fd85aa9-1a88-4a0a-8e1a-
81**********@w34g2000hsg.googlegroups.com:

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.

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.

[Jack Jackson]

On Wed, 28 Nov 2007 12:01:18 -0800 (PST), BostonNole
<bo********@gmail.comwrote:

>On Nov 28, 2:05 pm, [e-mail] wrote:

>On Nov 28, 12:52 pm, BostonNole <[e-mail]:

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.

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/li...odbctransa...- Hide quoted text -

- Show quoted text -

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.

For native SqlServer the equivalent object is:

<http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx>

I have never used Enterprise Library, but Googling for "Enterprise
Library 3 transactions" has some links that might be helpful.

[Chris Dunaway]

On Nov 28, 11:52 am, BostonNole <[e-mail]:

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.

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

[BostonNole]

On Nov 28, 5:19 pm, Chris Dunaway <[e-mail]:

On Nov 28, 11:52 am, BostonNole <[e-mail]:

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.

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 -

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