Nesting Transactions

[aradia926]

Hopefully someone can point me in the right direction, I've been searching on the net for the answer to this question and can't seem to come up with anything.

First, I'm using ASP.NET 2.0 and Visual Studio 2005 with a SQL Server 2000 backend.

My SQL database is relational and is used to store names, addresses, etc of other companies.

What I need to be able to do is have 2 transactions, one nested within the other. In pseudo-code:

BEGIN TRANSACTION1

BEGIN TRANSACTION2

INSERT INTO COMPANY TABLE

COMMIT TRANSACTION2 -OR- ROLLBACK TRANSACTION2

GET COMPANYID JUST ADDED

PERFORM REMAINING INSERTS

COMMIT TRANSACTION1 -OR- ROLLBACK TRANSACTION 1 & 2

Right now I have everything grouped into one VB.NET transaction, which doesn't work because the company is not actually added until the transaction reaches commit. Therefore, I can't retrieve the companyID halfway through.

Is what I'm trying to do even possible? Thanks in advance for the help!

 

[ca8msm]

It is possible and I don't think it is that difficult. If you created a variable in the first insert statement (for example, a new guid) you could return this variable from the sp. You then use this variable in the second statement and if anything fails, rollback.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[aradia926]

Thanks for the suggestion, ca8msm.

What I ended up doing is:

Start my transaction
Insert into the company table using cmd.executenonquery()
Then I ran: cmd.CommandText = "SELECT @@identity";
string id = cmd.ExecuteScalar().ToString();
Use id throughout the rest of my transaction
Commit transaction

Hopefully this information can help someone else.

Thanks again ca8msm!