>Hi all,
>
>I have some existing code that updates the database by creating a
>SqlCommand and SqlTransaction:
>
>public class GroupMapper
>{
>    public static void Update(Group g)
>    {
>        // Gets SqlConnection, creates SqlCommand and calls
>SqlConnection.BeginTransaction().
>    }
>}
>
>That works fine what I'm only updating one object at a time.  But now
>I need to update a bunch of objects (of different types) in a single
>transaction, so I wrap the Update() calls in a TransactionScope
>(within the TransactionScope, there might be calls to read the
>database that doesn't require a transaction):
>
>public void foo()
>{
>    using (TransactionScope scope = new TransactionScope())
>    {
>        GroupMapper.Update(group);
>        FooMapper.Update(foo);
>        ....
>        scope.Complete();
>    }
>}
>
>But I read in this article (http://msdn2.microsoft.com/en-us/library/
>ms973865(d=printer).aspx) that:
>
>"When using System.Transactions, applications should not directly
>utilize transactional programming interfaces on resource managers--for
>example the T-SQL BEGIN TRANSACTION or COMMIT TRANSACTION verbs, or
>the MessageQueueTransaction() object in System.Messaging namespace,
>when dealing with MSMQ. Those mechanisms would bypass the distributed
>transaction management handled by System.Transactions, and combining
>the use of System.Transactions with these resource manager "internal"
>transactions will lead to inconsistent results. As a rule, use
>System.Transactions in the general case, and use resource manager
>internal transactions only in specific cases where you are certain the
>transaction will not span multiple resources, and will not be composed
>into a larger transaction. Never mix the two."
>
>My transaction will not span multiple resources, so I should be ok?
>
>The reason I'm asking this is that when running my unit tests,
>occasionally, I see the following exception message:
>
>"ExecuteReader requires the command to have a transaction when the
>connection assigned to the command is in a pending local transaction.
>The Transaction property of the command has not been initialized."
>
>Which makes me wonder if I should delete all the code that creates
>SqlTransaction and wrap each database update method in its own
>TransactionScope.
>
>Any ideas will be greatly appreciated!
>
>Thanks,
>Harold

On Nov 23, 8:39 am, "Mary Chipman [MSFT]" <mc***@online.microsoft.com>
wrote:
> If you are performing multiple actions within the scope of a single
> connection, you can use the following pattern:
>
> TransactionScope {
>   Open Connection
>   Do action 1
>   Do action 2
>   Close Connection
>
> }
>
> You don't need SqlTransaction objects and wrapping each update in its
> own TransactionScope misses the point of using them to begin with.
> Here's a link for more information:http://msdn2.microsoft.com/en-us/library/ms172152.aspx
>
> --Mary
>
> On Wed, 21 Nov 2007 16:57:54 -0800 (PST), haroldsp***@gmail.com wrote:
> >Hi all,
>
> >I have some existing code that updates the database by creating a
> >SqlCommand and SqlTransaction:
>
> >public class GroupMapper
> >{
> >    public static void Update(Group g)
> >    {
> >        // Gets SqlConnection, creates SqlCommand and calls
> >SqlConnection.BeginTransaction().
> >    }
> >}
>
> >That works fine what I'm only updating one object at a time.  But now
> >I need to update a bunch of objects (of different types) in a single
> >transaction, so I wrap the Update() calls in a TransactionScope
> >(within the TransactionScope, there might be calls to read the
> >database that doesn't require a transaction):
>
> >public void foo()
> >{
> >    using (TransactionScope scope = new TransactionScope())
> >    {
> >        GroupMapper.Update(group);
> >        FooMapper.Update(foo);
> >        ....
> >        scope.Complete();
> >    }
> >}
>
> >But I read in this article (http://msdn2.microsoft.com/en-us/library/
> >ms973865(d=printer).aspx) that:
>
> >"When using System.Transactions, applications should not directly
> >utilize transactional programming interfaces on resource managers--for
> >example the T-SQL BEGIN TRANSACTION or COMMIT TRANSACTION verbs, or
> >the MessageQueueTransaction() object in System.Messaging namespace,
> >when dealing with MSMQ. Those mechanisms would bypass the distributed
> >transaction management handled by System.Transactions, and combining
> >the use of System.Transactions with these resource manager "internal"
> >transactions will lead to inconsistent results. As a rule, use
> >System.Transactions in the general case, and use resource manager
> >internal transactions only in specific cases where you are certain the
> >transaction will not span multiple resources, and will not be composed
> >into a larger transaction. Never mix the two."
>
> >My transaction will not span multiple resources, so I should be ok?
>
> >The reason I'm asking this is that when running my unit tests,
> >occasionally, I see the following exception message:
>
> >"ExecuteReader requires the command to have a transaction when the
> >connection assigned to the command is in a pending local transaction.
> >The Transaction property of the command has not been initialized."
>
> >Which makes me wonder if I should delete all the code that creates
> >SqlTransaction and wrap each database update method in its own
> >TransactionScope.
>
> >Any ideas will be greatly appreciated!
>
> >Thanks,
> >Harold

>Hi Mary,
>
>Thanks for the reply.  The pattern you have doesn't work for me
>because in my case, "action 1" and "action 2" can be called
>individually, and each of them need to be transactional by
>themselves.  In my example, I will call GroupMapper.Update(group) and
>the update has to be transactional.  I also have some other code that
>will call both GroupMapper.Update(group) and FooMapper.Update(foo).
>Sure, they can be done in a single connection, but again, because the
>Update() methods can be called individually in a different context,
>they need to open their own connection.
>
>I'm just not sure if wrapping the Update() calls within a
>TransactionScope while each one create its own SqlTransaction (legacy
>code...so they can be called individually) is the right way to do
>things, or I should take away the SqlTransaction, create a new
>TransactionScope within each Update(), so it will still be
>transactional when called individually, and become a nested
>TransactionScope when called together within another TransactionScope.
>
>Thanks,
>Harold
>
>On Nov 23, 8:39 am, "Mary Chipman [MSFT]" <mc***@online.microsoft.com>
>wrote:
>> If you are performing multiple actions within the scope of a single
>> connection, you can use the following pattern:
>>
>> TransactionScope {
>>   Open Connection
>>   Do action 1
>>   Do action 2
>>   Close Connection
>>
>> }
>>
>> You don't need SqlTransaction objects and wrapping each update in its
>> own TransactionScope misses the point of using them to begin with.
>> Here's a link for more information:http://msdn2.microsoft.com/en-us/library/ms172152.aspx
>>
>> --Mary
>>
>> On Wed, 21 Nov 2007 16:57:54 -0800 (PST), haroldsp***@gmail.com wrote:
>> >Hi all,
>>
>> >I have some existing code that updates the database by creating a
>> >SqlCommand and SqlTransaction:
>>
>> >public class GroupMapper
>> >{
>> >    public static void Update(Group g)
>> >    {
>> >        // Gets SqlConnection, creates SqlCommand and calls
>> >SqlConnection.BeginTransaction().
>> >    }
>> >}
>>
>> >That works fine what I'm only updating one object at a time.  But now
>> >I need to update a bunch of objects (of different types) in a single
>> >transaction, so I wrap the Update() calls in a TransactionScope
>> >(within the TransactionScope, there might be calls to read the
>> >database that doesn't require a transaction):
>>
>> >public void foo()
>> >{
>> >    using (TransactionScope scope = new TransactionScope())
>> >    {
>> >        GroupMapper.Update(group);
>> >        FooMapper.Update(foo);
>> >        ....
>> >        scope.Complete();
>> >    }
>> >}
>>
>> >But I read in this article (http://msdn2.microsoft.com/en-us/library/
>> >ms973865(d=printer).aspx) that:
>>
>> >"When using System.Transactions, applications should not directly
>> >utilize transactional programming interfaces on resource managers--for
>> >example the T-SQL BEGIN TRANSACTION or COMMIT TRANSACTION verbs, or
>> >the MessageQueueTransaction() object in System.Messaging namespace,
>> >when dealing with MSMQ. Those mechanisms would bypass the distributed
>> >transaction management handled by System.Transactions, and combining
>> >the use of System.Transactions with these resource manager "internal"
>> >transactions will lead to inconsistent results. As a rule, use
>> >System.Transactions in the general case, and use resource manager
>> >internal transactions only in specific cases where you are certain the
>> >transaction will not span multiple resources, and will not be composed
>> >into a larger transaction. Never mix the two."
>>
>> >My transaction will not span multiple resources, so I should be ok?
>>
>> >The reason I'm asking this is that when running my unit tests,
>> >occasionally, I see the following exception message:
>>
>> >"ExecuteReader requires the command to have a transaction when the
>> >connection assigned to the command is in a pending local transaction.
>> >The Transaction property of the command has not been initialized."
>>
>> >Which makes me wonder if I should delete all the code that creates
>> >SqlTransaction and wrap each database update method in its own
>> >TransactionScope.
>>
>> >Any ideas will be greatly appreciated!
>>
>> >Thanks,
>> >Harold

On Nov 28, 6:43 pm, "Mary Chipman [MSFT]" <mc***@online.microsoft.com>
wrote:
> If your method call only performs a single, atomic operation, then
> there is no need to wrap it in any kind of transaction. You only need
> explicit transactions when you want multiple operations to be
> processed as a single unit of work. I hope I am understanding you
> correctly :)
>
> -Mary
>
> On Fri, 23 Nov 2007 16:13:17 -0800 (PST), haroldsp***@gmail.com wrote:
> >Hi Mary,
>
> >Thanks for the reply.  The pattern you have doesn't work for me
> >because in my case, "action 1" and "action 2" can be called
> >individually, and each of them need to be transactional by
> >themselves.  In my example, I will call GroupMapper.Update(group) and
> >the update has to be transactional.  I also have some other code that
> >will call both GroupMapper.Update(group) and FooMapper.Update(foo).
> >Sure, they can be done in a single connection, but again, because the
> >Update() methods can be called individually in a different context,
> >they need to open their own connection.
>
> >I'm just not sure if wrapping the Update() calls within a
> >TransactionScope while each one create its own SqlTransaction (legacy
> >code...so they can be called individually) is the right way to do
> >things, or I should take away the SqlTransaction, create a new
> >TransactionScope within each Update(), so it will still be
> >transactional when called individually, and become a nested
> >TransactionScope when called together within another TransactionScope.
>
> >Thanks,
> >Harold
>
> >On Nov 23, 8:39 am, "Mary Chipman [MSFT]" <mc***@online.microsoft.com>
> >wrote:
> >> If you are performing multiple actions within the scope of a single
> >> connection, you can use the following pattern:
>
> >> TransactionScope {
> >>   Open Connection
> >>   Do action 1
> >>   Do action 2
> >>   Close Connection
>
> >> }
>
> >> You don't need SqlTransaction objects and wrapping each update in its
> >> own TransactionScope misses the point of using them to begin with.
> >> Here's a link for more information:http://msdn2.microsoft.com/en-us/library/ms172152.aspx
>
> >> --Mary
>
> >> On Wed, 21 Nov 2007 16:57:54 -0800 (PST), haroldsp***@gmail.com wrote:
> >> >Hi all,
>
> >> >I have some existing code that updates the database by creating a
> >> >SqlCommand and SqlTransaction:
>
> >> >public class GroupMapper
> >> >{
> >> >    public static void Update(Group g)
> >> >    {
> >> >        // Gets SqlConnection, creates SqlCommand and calls
> >> >SqlConnection.BeginTransaction().
> >> >    }
> >> >}
>
> >> >That works fine what I'm only updating one object at a time.  But now
> >> >I need to update a bunch of objects (of different types) in a single
> >> >transaction, so I wrap the Update() calls in a TransactionScope
> >> >(within the TransactionScope, there might be calls to read the
> >> >database that doesn't require a transaction):
>
> >> >public void foo()
> >> >{
> >> >    using (TransactionScope scope = new TransactionScope())
> >> >    {
> >> >        GroupMapper.Update(group);
> >> >        FooMapper.Update(foo);
> >> >        ....
> >> >        scope.Complete();
> >> >    }
> >> >}
>
> >> >But I read in this article (http://msdn2.microsoft.com/en-us/library/
> >> >ms973865(d=printer).aspx) that:
>
> >> >"When using System.Transactions, applications should not directly
> >> >utilize transactional programming interfaces on resource managers--for
> >> >example the T-SQL BEGIN TRANSACTION or COMMIT TRANSACTION verbs, or
> >> >the MessageQueueTransaction() object in System.Messaging namespace,
> >> >when dealing with MSMQ. Those mechanisms would bypass the distributed
> >> >transaction management handled by System.Transactions, and combining
> >> >the use of System.Transactions with these resource manager "internal"
> >> >transactions will lead to inconsistent results. As a rule, use
> >> >System.Transactions in the general case, and use resource manager
> >> >internal transactions only in specific cases where you are certain the
> >> >transaction will not span multiple resources, and will not be composed
> >> >into a larger transaction. Never mix the two."
>
> >> >My transaction will not span multiple resources, so I should be ok?
>
> >> >The reason I'm asking this is that when running my unit tests,
> >> >occasionally, I see the following exception message:
>
> >> >"ExecuteReader requires the command to have a transaction when the
> >> >connection assigned to the command is in a pending local transaction.
> >> >The Transaction property of the command has not been initialized."
>
> >> >Which makes me wonder if I should delete all the code that creates
> >> >SqlTransaction and wrap each database update method in its own
> >> >TransactionScope.
>
> >> >Any ideas will be greatly appreciated!
>
> >> >Thanks,
> >> >Harold