Context connection and transactions

All -
Facts:
(a) SqlConnection allows only one "Context connection" to be opened at a tim
e.
(b) SqlConnection doesnt support parallel transactions. (calling a second
BeginTransaction before committing the first BeginTransaction throws
exception)
Problem:
Lets say a piece of C# code whose assembly is stored in SqlServer 2005 -
registered as SQLProcedure, is invoked.
Inside this code (which is now in SqlServer context) - i open a
SqlConnection as "context connection".
All the SqlCommand objects that uses this context connection, need the same
transaction (Lets say TransA) except one SqlCommand (LogCmd) that needs a
different transaction. (lets say TransB)
How to do this?
Calling BeginTransaction twice blows up.
Creating 2 seperate instances of SqlConnection with context connection =
true, also blows up.
Remember that somehow i might want to rollback TransA but always committ
TransB. For instance
try
{
// Do some actions
// SqlCommands that use TransA and TransB will be executed here
transA.Committ(); // things are fine - committ A
}
catch
{
transA.Rollback(); // things are incorrect - rollback A
}
finally
{
transB.Committ(); // Always comitt the logs...
}
How to do this when we are in context connection?
Regardz
Grafix."Grafix" <Grafix@.discussions.microsoft.com> wrote in message
news:54F45F4C-4F6B-44D7-8B13-0D55F13BB75C@.microsoft.com...
> All -
> Facts:
> (a) SqlConnection allows only one "Context connection" to be opened at a
> time.
> (b) SqlConnection doesnt support parallel transactions. (calling a second
> BeginTransaction before committing the first BeginTransaction throws
> exception)
> Problem:
> Lets say a piece of C# code whose assembly is stored in SqlServer 2005 -
> registered as SQLProcedure, is invoked.
> Inside this code (which is now in SqlServer context) - i open a
> SqlConnection as "context connection".
> All the SqlCommand objects that uses this context connection, need the
> same
> transaction (Lets say TransA) except one SqlCommand (LogCmd) that needs a
> different transaction. (lets say TransB)
> How to do this?
> Calling BeginTransaction twice blows up.
> Creating 2 seperate instances of SqlConnection with context connection =
> true, also blows up.
> Remember that somehow i might want to rollback TransA but always committ
> TransB. For instance
>
You will have exactly the same issue in TSQL. SQL Server does not support
autonomous transactions.
A common workaround is to write data into a table variable (or List<T> ), and
do something with it after the transaction is rolled back.
David|||I haven't gone into CLR stored procedures too much. But can't you add this
one SqlCommand (LogCmd) that needs a different transaction
into a seperate class and use the transaction option as requiresNew and with
the method haveing an autocommit tag to be true. and then call it from the
parent transaction.|||> one SqlCommand (LogCmd) that needs a different transaction
> into a seperate class and use the transaction option as requiresNew
There is the problem.
I am not able to have 2 active transaction scopes at the same time.
Remember LogCmd will be executed multiple times inbetween other regular
xxxCmd.
Which means i want 2 active transaction scopes (each with Option
"RequiresNew")
Unfortunately when i am in context connection (C# sp), i am not allowed to
create 2 connections that i can enlist with the TransactionScope.
I tried ur suggestion with the new .NET 2.0's
System.Transactions.TransactionScope class (Promotable transaction) for
seeing if i can enlist a single connection with multiple transactions - but
the problem is the same. So bad that they have introduced new Options like
TransactionScopeOption.Suppress and i am not able to use it still.
With one SqlConnection - u cannot associate with 2 different transaction
scopes.
And this C# sps allow only one connection at a time.
Seems i have to follow David Browne's suggestion of in-memory datastructure
to hold the logs and committ at the end.