Re: Strongly typed dataset and transactions

The problem is that the automatically generated TableAdapter from the xsd
file, whilst derived from a standard TableAdapter, does not give you access
to the InsertCommand, so you cannot set its transaction!

I'm clear on parent/child records, and I know that SQL server has countless
advantages but I'm stuck with a Jet database for this project.
I can certainly use transactions with a normal TableAdapter, it just seems
stange that MS automatically generates these custom TableAdapters, then
makes is very difficult to use transactions, essential for all but the most
trivial databases.


James

"Cowboy (Gregory A. Beamer)" <NoSpamMgbworld@xxxxxxxxxxxxxxxxxx> wrote in
message news:ewXzC9ZjGHA.1552@xxxxxxxxxxxxxxxxxxxxxxx
The DataSet, itself, whether typed or not, is a container. The Adapter
(DataAdapter, TableAdapter, etc.) is the bridge from the container to your
persistent source (it can actually get more complex than this, but that is
a good start).

The easiest system I have seen is to set up your DataSets with an ordering
attribute and then fire back so each sproc is fired in order to guarantee
parent records are inserted prior to child records. You can wrap all of
the adapters in a transaction and roll back.

Another option is XML input into a stored procedure, but I am not sure
this is the best option with Access, as I am not well versed enough to
know what XML it currently supports.

The third option is roll your own data layer. With SQL Server and other
full RDBMS products, this is easier than Access, as well.

Hope this gives you some useful hints.

--
Gregory A. Beamer

*************************************************
Think Outside the Box!
*************************************************
"James Minns" <minns@xxxxxxxxxxxxxxxxxx> wrote in message
news:%23gN0zlZjGHA.4660@xxxxxxxxxxxxxxxxxxxxxxx
Hi all,
I am using VB.NET (2005) to write to an Access database, with oledb.
I have created strongly typed datasets in an xsd file to read from and
update several tables in the database.

I would like to use a transaction so that in case the update of one table
fails, I can roll back the updates made to another table, and here lies
the problem.

I can't see how to access the insert/update/delete command for the
strongly typed dataset so I can set the transaction property - The
problem does not arise when using a standard TableAdapter!

Any ideas?

Thanks, James





.

Relevant Pages

  • RE: Visual Studio 2008 TableAdapters and Transactions
    ... As for the "Transaction" property of the VS generated TableAdapter, ... As you can see, when you set its value, it will update all the Command ... Microsoft MSDN Online Support Lead ...
    (microsoft.public.dotnet.framework.aspnet)
  • Transactions + multiple table adapters
    ... with two DataTables each having a TableAdapter. ... use the insert command on the first TableAdapter and then execute a stored ... procedure on the second table adapter - all within a transaction. ... Problem is that each TableAdapter uses it's own connection. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: TableAdapter with Transactions
    ... bet is to simply open the connection, wrap it in a TransactionScope and run ... sure your transaction isn't promoting the MSDTC. ... SqlTransaction TableAdapter.BeginTransaction ... This way, you can use the TableAdapter to start/end transactions, and ENSURE ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: TableAdapter with Transactions
    ... > a) If you are using Sql Server 2005, and *only one* connection, your best ... > make sure your transaction isn't promoting the MSDTC. ... > TableAdapter and add a BeginTransaction method on the table adapter ... > SqlTransaction TableAdapter.BeginTransaction ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: HOW TO: Implement transaction in TableAdapter class
    ... The TableAdapter will not generate code that implements a transaction by ... If you wish to make the TableAdapter commit a transaction to the ... database, you have to add SQL script yourself. ...
    (microsoft.public.data.ado)