I'm trying out the new TableAdapters in ADO.Net 2.0. I was a bit surprised
to see control of transactions being somewhat taken away from me and placed
into the System.Transactions namespace. I had a read and saw all the stuff
about distributed transactions, which seems quite cool for managing access
to multiple resources of different types. However, for plain old SQL
Transactions, it seems like if you're not careful, you might end up paying a
performance hit.

So I wrote a small app that inserts rows in a few tables in one function.
This all needs to be inside the one transaction. So I use the
TransactionScope, and everything seemed sweet while I was only posting the
first TableAdapter. Once I started posted the other two, I received
exceptions complaining that the MSDTC was not running. After reading the
generated code for the TableAdapters, it appears that what I have to do is
ensure that the first adapter's connection is open before it posts
(otherwise it will close it again), and that the other adapters all have
their connections assigned to be the first adapter's connection. This seems
to leave SQLServer 2005 happy enough to do it all on one transaction and not
involve the MSDTC.

Is this the "proper" way to get around your transaction getting promoted up
to the MSDTC? Also, can I/should I close the SqlConnection I manually opened
before/after completing the TransactionScope, or should I leave it?

Niall

If you are working with a single database, open your single SqlConnection
and keep it open throughout the duration of the transaction, that will keep
the tranasction on LTM for SQL2k5.

The control hasn't been "taken away" from you - you can still do
SqlTransaction.

Also, please read this
http://codebetter.com/blogs/sahil.ma.../13/64533.aspx
and this -
http://codebetter.com/blogs/sahil.ma.../11/64484.aspx

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.ma.../13/63199.aspx
-------------------------------------------------------------------------------------------


"Niall" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> I'm trying out the new TableAdapters in ADO.Net 2.0. I was a bit surprised
> to see control of transactions being somewhat taken away from me and
> placed into the System.Transactions namespace. I had a read and saw all
> the stuff about distributed transactions, which seems quite cool for
> managing access to multiple resources of different types. However, for
> plain old SQL Transactions, it seems like if you're not careful, you might
> end up paying a performance hit.
>
> So I wrote a small app that inserts rows in a few tables in one function.
> This all needs to be inside the one transaction. So I use the
> TransactionScope, and everything seemed sweet while I was only posting the
> first TableAdapter. Once I started posted the other two, I received
> exceptions complaining that the MSDTC was not running. After reading the
> generated code for the TableAdapters, it appears that what I have to do is
> ensure that the first adapter's connection is open before it posts
> (otherwise it will close it again), and that the other adapters all have
> their connections assigned to be the first adapter's connection. This
> seems to leave SQLServer 2005 happy enough to do it all on one transaction
> and not involve the MSDTC.
>
> Is this the "proper" way to get around your transaction getting promoted
> up to the MSDTC? Also, can I/should I close the SqlConnection I manually
> opened before/after completing the TransactionScope, or should I leave it?
>
> Niall
>

Hi Sahil,

I've read quite a few of your posts as I searched about TableAdapters and
Transactions.

Perhaps I wasn't quite clear enough with my original post. What I'm trying
to find out is: what is the way the ADO.Net team intended us to use multiple
TableAdapters with transactions? As the class generated is partial, and you
have access to the connection, I can see many different ways of altering the
code for the class or handing it a connection you control, etc, to take
implicit control over the implicit transaction handling - ie you're not
managing the transaction itself, you are moulding the tableadapters into the
right shape so that they don't do the wrong thing and cause the transaction
to get pushed up to the MSDTC. And, of course, you could always just use
data adapters or plain SqlCommands with the old SqlTransaction objects.

However, none of these really click with me. Handling the connection object
yourself so that all adapters posting in a transaction is easy enough when
you're doing all your posting in one method, but can cause a big change in
interface in an enterprise scale system to make sure all relevant code has
the right connection. Altering/adding code to the TableAdapter is even worse
from the perspective of an enterprise scale system.

If you've ever read the blog of people like Rico Mariani, he talks about the
"Pit of success", where using a tool is intuitive to the point that you fall
into using it the best way without realising it. I'm not getting the feeling
that I'm doing this when I use transactions with TableAdapters, so I'm
wondering what the "right" way is supposed to be. It's not that I can't do
it, it's that I haven't found a way to do it yet that doesn't leave a bad
taste in the back of my mouth.

Niall

"Sahil Malik [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> If you are working with a single database, open your single SqlConnection
> and keep it open throughout the duration of the transaction, that will
> keep the tranasction on LTM for SQL2k5.
>
> The control hasn't been "taken away" from you - you can still do
> SqlTransaction.
>
> Also, please read this
> http://codebetter.com/blogs/sahil.ma.../13/64533.aspx
> and this -
> http://codebetter.com/blogs/sahil.ma.../11/64484.aspx
>
> - Sahil Malik [MVP]
> ADO.NET 2.0 book -
> http://codebetter.com/blogs/sahil.ma.../13/63199.aspx
> -------------------------------------------------------------------------------------------
>
>
> "Niall" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> I'm trying out the new TableAdapters in ADO.Net 2.0. I was a bit
>> surprised to see control of transactions being somewhat taken away from
>> me and placed into the System.Transactions namespace. I had a read and
>> saw all the stuff about distributed transactions, which seems quite cool
>> for managing access to multiple resources of different types. However,
>> for plain old SQL Transactions, it seems like if you're not careful, you
>> might end up paying a performance hit.
>>
>> So I wrote a small app that inserts rows in a few tables in one function.
>> This all needs to be inside the one transaction. So I use the
>> TransactionScope, and everything seemed sweet while I was only posting
>> the first TableAdapter. Once I started posted the other two, I received
>> exceptions complaining that the MSDTC was not running. After reading the
>> generated code for the TableAdapters, it appears that what I have to do
>> is ensure that the first adapter's connection is open before it posts
>> (otherwise it will close it again), and that the other adapters all have
>> their connections assigned to be the first adapter's connection. This
>> seems to leave SQLServer 2005 happy enough to do it all on one
>> transaction and not involve the MSDTC.
>>
>> Is this the "proper" way to get around your transaction getting promoted
>> up to the MSDTC? Also, can I/should I close the SqlConnection I manually
>> opened before/after completing the TransactionScope, or should I leave
>> it?
>>
>> Niall
>>
>
>

Niall,

Check this blogpost out -
http://codebetter.com/blogs/sahil.ma...19/133309.aspx

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.ma.../13/63199.aspx
-------------------------------------------------------------------------------------------






"Niall" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Sahil,
>
> I've read quite a few of your posts as I searched about TableAdapters and
> Transactions.
>
> Perhaps I wasn't quite clear enough with my original post. What I'm trying
> to find out is: what is the way the ADO.Net team intended us to use
> multiple TableAdapters with transactions? As the class generated is
> partial, and you have access to the connection, I can see many different
> ways of altering the code for the class or handing it a connection you
> control, etc, to take implicit control over the implicit transaction
> handling - ie you're not managing the transaction itself, you are moulding
> the tableadapters into the right shape so that they don't do the wrong
> thing and cause the transaction to get pushed up to the MSDTC. And, of
> course, you could always just use data adapters or plain SqlCommands with
> the old SqlTransaction objects.
>
> However, none of these really click with me. Handling the connection
> object yourself so that all adapters posting in a transaction is easy
> enough when you're doing all your posting in one method, but can cause a
> big change in interface in an enterprise scale system to make sure all
> relevant code has the right connection. Altering/adding code to the
> TableAdapter is even worse from the perspective of an enterprise scale
> system.
>
> If you've ever read the blog of people like Rico Mariani, he talks about
> the "Pit of success", where using a tool is intuitive to the point that
> you fall into using it the best way without realising it. I'm not getting
> the feeling that I'm doing this when I use transactions with
> TableAdapters, so I'm wondering what the "right" way is supposed to be.
> It's not that I can't do it, it's that I haven't found a way to do it yet
> that doesn't leave a bad taste in the back of my mouth.
>
> Niall
>
> "Sahil Malik [MVP]" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> If you are working with a single database, open your single SqlConnection
>> and keep it open throughout the duration of the transaction, that will
>> keep the tranasction on LTM for SQL2k5.
>>
>> The control hasn't been "taken away" from you - you can still do
>> SqlTransaction.
>>
>> Also, please read this
>> http://codebetter.com/blogs/sahil.ma.../13/64533.aspx
>> and this -
>> http://codebetter.com/blogs/sahil.ma.../11/64484.aspx
>>
>> - Sahil Malik [MVP]
>> ADO.NET 2.0 book -
>> http://codebetter.com/blogs/sahil.ma.../13/63199.aspx
>> -------------------------------------------------------------------------------------------
>>
>>
>> "Niall" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> I'm trying out the new TableAdapters in ADO.Net 2.0. I was a bit
>>> surprised to see control of transactions being somewhat taken away from
>>> me and placed into the System.Transactions namespace. I had a read and
>>> saw all the stuff about distributed transactions, which seems quite cool
>>> for managing access to multiple resources of different types. However,
>>> for plain old SQL Transactions, it seems like if you're not careful, you
>>> might end up paying a performance hit.
>>>
>>> So I wrote a small app that inserts rows in a few tables in one
>>> function. This all needs to be inside the one transaction. So I use the
>>> TransactionScope, and everything seemed sweet while I was only posting
>>> the first TableAdapter. Once I started posted the other two, I received
>>> exceptions complaining that the MSDTC was not running. After reading the
>>> generated code for the TableAdapters, it appears that what I have to do
>>> is ensure that the first adapter's connection is open before it posts
>>> (otherwise it will close it again), and that the other adapters all have
>>> their connections assigned to be the first adapter's connection. This
>>> seems to leave SQLServer 2005 happy enough to do it all on one
>>> transaction and not involve the MSDTC.
>>>
>>> Is this the "proper" way to get around your transaction getting promoted
>>> up to the MSDTC? Also, can I/should I close the SqlConnection I manually
>>> opened before/after completing the TransactionScope, or should I leave
>>> it?
>>>
>>> Niall
>>>
>>
>>
>
>

Thanks Sahil, the contents of the post is what I referring to in my previous
post in terms of the techniques that I know can be used to achieve the
effect I want.

The point I am trying to make is that all of these techniques seem, in one
way or another, unideal. It seems to me that there should be a good way to
make TableAdapters work with transactions with a minimal investment in code,
as companies with hundreds of tables in their database will be deterred by
having to modify the code of every TableAdapter they use. It seems the
"best" way is to share the connection object, but even that entails a not
entirely simple or clean change to architecture design when you are talking
about several hundred tables.

It seems, at the end of the day, that TableAdapter is aimed at quite small
scale solutions. I would have thought they would have been designed with
larger scale use in mind as well, but I guess not.

Niall

"Sahil Malik [MVP]" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Niall,
>
> Check this blogpost out -
> http://codebetter.com/blogs/sahil.ma...19/133309.aspx
>
> - Sahil Malik [MVP]
> ADO.NET 2.0 book -
> http://codebetter.com/blogs/sahil.ma.../13/63199.aspx
> -------------------------------------------------------------------------------------------
>
>
>
>
>
>
> "Niall" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Hi Sahil,
>>
>> I've read quite a few of your posts as I searched about TableAdapters and
>> Transactions.
>>
>> Perhaps I wasn't quite clear enough with my original post. What I'm
>> trying to find out is: what is the way the ADO.Net team intended us to
>> use multiple TableAdapters with transactions? As the class generated is
>> partial, and you have access to the connection, I can see many different
>> ways of altering the code for the class or handing it a connection you
>> control, etc, to take implicit control over the implicit transaction
>> handling - ie you're not managing the transaction itself, you are
>> moulding the tableadapters into the right shape so that they don't do the
>> wrong thing and cause the transaction to get pushed up to the MSDTC. And,
>> of course, you could always just use data adapters or plain SqlCommands
>> with the old SqlTransaction objects.
>>
>> However, none of these really click with me. Handling the connection
>> object yourself so that all adapters posting in a transaction is easy
>> enough when you're doing all your posting in one method, but can cause a
>> big change in interface in an enterprise scale system to make sure all
>> relevant code has the right connection. Altering/adding code to the
>> TableAdapter is even worse from the perspective of an enterprise scale
>> system.
>>
>> If you've ever read the blog of people like Rico Mariani, he talks about
>> the "Pit of success", where using a tool is intuitive to the point that
>> you fall into using it the best way without realising it. I'm not getting
>> the feeling that I'm doing this when I use transactions with
>> TableAdapters, so I'm wondering what the "right" way is supposed to be.
>> It's not that I can't do it, it's that I haven't found a way to do it yet
>> that doesn't leave a bad taste in the back of my mouth.
>>
>> Niall
>>
>> "Sahil Malik [MVP]" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> If you are working with a single database, open your single
>>> SqlConnection and keep it open throughout the duration of the
>>> transaction, that will keep the tranasction on LTM for SQL2k5.
>>>
>>> The control hasn't been "taken away" from you - you can still do
>>> SqlTransaction.
>>>
>>> Also, please read this
>>> http://codebetter.com/blogs/sahil.ma.../13/64533.aspx
>>> and this -
>>> http://codebetter.com/blogs/sahil.ma.../11/64484.aspx
>>>
>>> - Sahil Malik [MVP]
>>> ADO.NET 2.0 book -
>>> http://codebetter.com/blogs/sahil.ma.../13/63199.aspx
>>> -------------------------------------------------------------------------------------------
>>>
>>>
>>> "Niall" <(E-Mail Removed)> wrote in message
>>> news:%(E-Mail Removed)...
>>>> I'm trying out the new TableAdapters in ADO.Net 2.0. I was a bit
>>>> surprised to see control of transactions being somewhat taken away from
>>>> me and placed into the System.Transactions namespace. I had a read and
>>>> saw all the stuff about distributed transactions, which seems quite
>>>> cool for managing access to multiple resources of different types.
>>>> However, for plain old SQL Transactions, it seems like if you're not
>>>> careful, you might end up paying a performance hit.
>>>>
>>>> So I wrote a small app that inserts rows in a few tables in one
>>>> function. This all needs to be inside the one transaction. So I use the
>>>> TransactionScope, and everything seemed sweet while I was only posting
>>>> the first TableAdapter. Once I started posted the other two, I received
>>>> exceptions complaining that the MSDTC was not running. After reading
>>>> the generated code for the TableAdapters, it appears that what I have
>>>> to do is ensure that the first adapter's connection is open before it
>>>> posts (otherwise it will close it again), and that the other adapters
>>>> all have their connections assigned to be the first adapter's
>>>> connection. This seems to leave SQLServer 2005 happy enough to do it
>>>> all on one transaction and not involve the MSDTC.
>>>>
>>>> Is this the "proper" way to get around your transaction getting
>>>> promoted up to the MSDTC? Also, can I/should I close the SqlConnection
>>>> I manually opened before/after completing the TransactionScope, or
>>>> should I leave it?
>>>>
>>>> Niall
>>>>
>>>
>>>
>>
>>
>
>

Niall,

I doubt a perfect OSFA (one size fits all) approach for transactions is even
possible. When you start talking about transactions with disconnected data,
the first thing that hits you is concurrency technique, which depends to a
great extent on your table structures.

Maybe that problem is even solv-able, but the next issue that hits you is
what do you lock and for how long?

Followed by, when you are updating disconnected data, and you have 3 changes
to make, do you update 1,2, and if the 3 errors out - do you leave 1 and 2
as is? Do you roll them back? Or do you keep moving to the 4th? And in that
scenario, how do you reconcile the changes done, back to the disconnected
cache, and finally back to the UI? This gets quite hairy.

I can't speak for microsoft, as I'm not a microsoft employee , but IMO
OSFA transactional support are quite big shoes to fill.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.ma.../13/63199.aspx
----------------------------------------------------------------------------



"Niall" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Thanks Sahil, the contents of the post is what I referring to in my
> previous post in terms of the techniques that I know can be used to
> achieve the effect I want.
>
> The point I am trying to make is that all of these techniques seem, in one
> way or another, unideal. It seems to me that there should be a good way to
> make TableAdapters work with transactions with a minimal investment in
> code, as companies with hundreds of tables in their database will be
> deterred by having to modify the code of every TableAdapter they use. It
> seems the "best" way is to share the connection object, but even that
> entails a not entirely simple or clean change to architecture design when
> you are talking about several hundred tables.
>
> It seems, at the end of the day, that TableAdapter is aimed at quite small
> scale solutions. I would have thought they would have been designed with
> larger scale use in mind as well, but I guess not.
>
> Niall
>
> "Sahil Malik [MVP]" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> Niall,
>>
>> Check this blogpost out -
>> http://codebetter.com/blogs/sahil.ma...19/133309.aspx
>>
>> - Sahil Malik [MVP]
>> ADO.NET 2.0 book -
>> http://codebetter.com/blogs/sahil.ma.../13/63199.aspx
>> -------------------------------------------------------------------------------------------
>>
>>
>>
>>
>>
>>
>> "Niall" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> Hi Sahil,
>>>
>>> I've read quite a few of your posts as I searched about TableAdapters
>>> and Transactions.
>>>
>>> Perhaps I wasn't quite clear enough with my original post. What I'm
>>> trying to find out is: what is the way the ADO.Net team intended us to
>>> use multiple TableAdapters with transactions? As the class generated is
>>> partial, and you have access to the connection, I can see many different
>>> ways of altering the code for the class or handing it a connection you
>>> control, etc, to take implicit control over the implicit transaction
>>> handling - ie you're not managing the transaction itself, you are
>>> moulding the tableadapters into the right shape so that they don't do
>>> the wrong thing and cause the transaction to get pushed up to the MSDTC.
>>> And, of course, you could always just use data adapters or plain
>>> SqlCommands with the old SqlTransaction objects.
>>>
>>> However, none of these really click with me. Handling the connection
>>> object yourself so that all adapters posting in a transaction is easy
>>> enough when you're doing all your posting in one method, but can cause a
>>> big change in interface in an enterprise scale system to make sure all
>>> relevant code has the right connection. Altering/adding code to the
>>> TableAdapter is even worse from the perspective of an enterprise scale
>>> system.
>>>
>>> If you've ever read the blog of people like Rico Mariani, he talks about
>>> the "Pit of success", where using a tool is intuitive to the point that
>>> you fall into using it the best way without realising it. I'm not
>>> getting the feeling that I'm doing this when I use transactions with
>>> TableAdapters, so I'm wondering what the "right" way is supposed to be.
>>> It's not that I can't do it, it's that I haven't found a way to do it
>>> yet that doesn't leave a bad taste in the back of my mouth.
>>>
>>> Niall
>>>
>>> "Sahil Malik [MVP]" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> If you are working with a single database, open your single
>>>> SqlConnection and keep it open throughout the duration of the
>>>> transaction, that will keep the tranasction on LTM for SQL2k5.
>>>>
>>>> The control hasn't been "taken away" from you - you can still do
>>>> SqlTransaction.
>>>>
>>>> Also, please read this
>>>> http://codebetter.com/blogs/sahil.ma.../13/64533.aspx
>>>> and this -
>>>> http://codebetter.com/blogs/sahil.ma.../11/64484.aspx
>>>>
>>>> - Sahil Malik [MVP]
>>>> ADO.NET 2.0 book -
>>>> http://codebetter.com/blogs/sahil.ma.../13/63199.aspx
>>>> -------------------------------------------------------------------------------------------
>>>>
>>>>
>>>> "Niall" <(E-Mail Removed)> wrote in message
>>>> news:%(E-Mail Removed)...
>>>>> I'm trying out the new TableAdapters in ADO.Net 2.0. I was a bit
>>>>> surprised to see control of transactions being somewhat taken away
>>>>> from me and placed into the System.Transactions namespace. I had a
>>>>> read and saw all the stuff about distributed transactions, which seems
>>>>> quite cool for managing access to multiple resources of different
>>>>> types. However, for plain old SQL Transactions, it seems like if
>>>>> you're not careful, you might end up paying a performance hit.
>>>>>
>>>>> So I wrote a small app that inserts rows in a few tables in one
>>>>> function. This all needs to be inside the one transaction. So I use
>>>>> the TransactionScope, and everything seemed sweet while I was only
>>>>> posting the first TableAdapter. Once I started posted the other two, I
>>>>> received exceptions complaining that the MSDTC was not running. After
>>>>> reading the generated code for the TableAdapters, it appears that what
>>>>> I have to do is ensure that the first adapter's connection is open
>>>>> before it posts (otherwise it will close it again), and that the other
>>>>> adapters all have their connections assigned to be the first adapter's
>>>>> connection. This seems to leave SQLServer 2005 happy enough to do it
>>>>> all on one transaction and not involve the MSDTC.
>>>>>
>>>>> Is this the "proper" way to get around your transaction getting
>>>>> promoted up to the MSDTC? Also, can I/should I close the SqlConnection
>>>>> I manually opened before/after completing the TransactionScope, or
>>>>> should I leave it?
>>>>>
>>>>> Niall
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>