I have used VS 2005 Beta2 to create a strongly typed DataSet. Using the
DataSet designer, I have created 3 table adapters to select and retrieve
rows from those tables, which works very well. Two of the tables have a one
to many relationship with the third table, and now I need to perform my
Insert, Update and Delete processing. I had used a transaction to
synchronize the updates with my hand coded ADO.NET 1.1 code, but can't
figure out how to do the equivalent with the 2.0 designer generated code. I
can't find any PUBLIC properties or methods that will let me use
transactions with the designer generated SqlCommand objects.

Is there a way to wrap a transaction around the IUD commands of two tables
in a strongly typed DataSet? If so, how do you do it? Thanks.

Chuck Hartman

Kevin,

Yes, I would like to put the IUD operations of two different table adapters
(from a designer generated DataSet) in a transaction. My business logic is
usually working with one row in one table along with one to many rows in a
second table. I have done this successfully with ADO.NET 1.1 using a
manually coded DataSet, several SqlDataAdapters, several SqlCommand objects
(with corresponding stored procedures), and an explicit SqlTransaction using
(as you mentioned) the public Transaction property of the SqlCommand object.

Looking at the VS 2005 designer generated code for the TableAdapter's
Connection property as an example, on the surface it looks like the designer
could have also implemented a Transaction property using 90% similar code.
Alternatively, the user could do it manually in external code if the Adapter
property were made public (or even internal) instead of private.

If those changes are not possible, I'm looking for a relatively
straightforward way to accomplish this since I have to do this many times.
I was looking forward to cashing in on the developer productivity gains
using the strongly typed DataSet, but without being able to use
transactions, I will probably have to stick with the same approach that I
used with ADO.NET 1.1 and use non-designer based code.

You mentioned that we have to write code to achieve this result. I would be
interested to know the approach that you would suggest. Thanks.

Chuck Hartman

Hi Chuck,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to put your table adapter
operations in a transaction. If there is any misunderstanding, please feel
free to let me know.

As far as I know, there is a public property named Transaction in
SqlCommand. Before execution, we can assign a reference to a SqlTransaction
object to it. In the designer, we don't have an option to assign that
value. So, if we need to set it to a designer generated SqlCommand objects,
we have to write code to achieve that.

SqlCommand1.Transaction = SqlTransaction1
//use table adapters to fill.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Kevin,

For obvious reasons, I would prefer not to modify the designer generated
code itself, however, if that is the easiest approach, I would prefer to
keep the changes to an absolute bare minimum in case I need to redo the
changes after making subsequent changes via the designer (which I am sure I
will need to do over time). I have tried to change the designer generated
Adapter and the CommandCollection properties of the TableAdapter object to
public (and also internal) access. When the project is built, that property
is still not accessible to my external code.

Is it possible that I am not modifying the designer generated code properly
or the wrong copy of the generated source code? I am opening and modifying
the designer generated code by right clicking the TableAdapter class name in
my external code and selecting Go To Definition. After Saving, the changes
that I make do not seem to take effect as I get build errors. What is the
proper way to make changes to the designer generated code? Thanks.

Chuck Hartman

Hi Chuck,

What I meant in my last post, is to add the transaction to the SqlCommand
property in external code after the original code is generated by the
desinger. This seems to be the only to do that, since we can't set
transaction directly in the wizard.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Hi Chuck,

My suggestion on making changes to designer generated code is to write
another method outside the generated code region to add additional setting
such as transactions to the generated TableAdapter objects. Within the
class, the object is visible to all methods. We can call our own method in
the Form.Load event handler. In this case, before the Fill method is
called, the transaction is added. Also, if you change the TableAdapter in
wizard, your own code will not be affected and it can be reused.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."