Save several DataTables of DataSet

[Joe Kovac]

Hi!

I change quite a lot TableRows in several Tables of a typed DataSet. Now
I would like to know which is the best way to save all the rows.

Currently I just use each TableAdapters Update() method.
First, I think I should have some kind of transactions.
Secondly, I think I should use GetChanges()?

Where can I get more informations on how to save the data best? Most
samples are for non typed DataSets and I think it shouldn't be so hard
with a typed DataSet.

Any good samples?

Thanks

Joe

 

[Nicholas Paldino [.NET/C# MVP]]

Joe,

If you have a sample that doesn't use a typed data set, then you can use
it with a typed data set, since typed data sets derive from untyped data
sets.

Generally, you are going to use DataAdapter classes to perform the
updates. If you used the designer to generate your data sets from tables in
a database, then it should have created classes that end in TableAdapter as
well which you can use to save the various records in the typed data set.

If you need transactions, then you should look into the TransactionScope
class in the System.Transactions namespace. You can wrap your code in a
using statment with an instance of the TransactionScope class and it will
place all operations in that code block in a Transaction.

Hope this helps.

 

[Joe Kovac]

Thanks for your information.

I tried:

using (TransactionScope ts = new TransactionScope())
{
mds.UpdateTable1();
mds.UpdateTable2();
mds.UpdateTable3();
}

And got:

The transaction manager has disabled its support for remote/network
transactions.

Without the TransactionScope it worked fine. But the TransactionScope
causes the error above. I guess I have to change some settings on the
server, haven't I? Which ones and where?
Would this also work with non MS-SQl 2005 servers?

Do I have other possibilities? E.g. creating a new delta DataSet using
getChanges() and putting another connection into it? But how would I do
that?

And what is getChanges() good for in general? Wouldn't a Table.Update()
automatically "call" getChanges() and just update the modified rows?

The journey is the reward.

Joe

 

[Nicholas Paldino [.NET/C# MVP]]

Joe,

See inline:

The transaction manager has disabled its support for remote/network
transactions.

Without the TransactionScope it worked fine. But the TransactionScope
causes the error above. I guess I have to change some settings on the
server, haven't I? Which ones and where?
Would this also work with non MS-SQl 2005 servers?

Without TransactionScope, it worked, but if you had an error on the
second update, none of the work would be rolled back. The
TransactionManager would most likely not work with non MS SQL Server 2005
installations since you would have to have DTC running on both machines to
handle the coordination of the distributed transaction.

Do I have other possibilities? E.g. creating a new delta DataSet using
getChanges() and putting another connection into it? But how would I do
that?

You could manage the transaction yourself, by calling BeginTransaction
on the connection. However, depending on your needs for transaction based
processing, this can get very unwieldy very quickly (especially if you have
multiple calls you need to handle in the transaction, you would have to pass
the transaction object everywhere).

GetChanges is only going to give you the changes that occured in the
datatable, nothing more. Using another connection to process it isn't going
to help.

And what is getChanges() good for in general? Wouldn't a Table.Update()
automatically "call" getChanges() and just update the modified rows?

The Update method on the DataAdapter is going to cycle through the rows
and if there is a changed state for a row, it is going to process it.
However, AFAIK, it does not call GetChanges to only get changed rows.

 

[Joe Kovac]

Joe,

See inline:


Without TransactionScope, it worked, but if you had an error on the
second update, none of the work would be rolled back. The
TransactionManager would most likely not work with non MS SQL Server 2005
installations since you would have to have DTC running on both machines to
handle the coordination of the distributed transaction.


You could manage the transaction yourself, by calling BeginTransaction
on the connection. However, depending on your needs for transaction based
processing, this can get very unwieldy very quickly (especially if you have
multiple calls you need to handle in the transaction, you would have to pass
the transaction object everywhere).

Well, I tried to handle the transactions myself. I followed the template
provided at:

http://weblogs.asp.net/ryanw/archive/2006/03/30/441529.aspx

Unfortunately I got following error:

"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."

How can I fix that? (Or is there another easy way to implement
transactions within datasets without DTC?)

 

[Joe Kovac]

Well, I tried to handle the transactions myself. I followed the template
provided at:

http://weblogs.asp.net/ryanw/archive/2006/03/30/441529.aspx

Unfortunately I got following error:

"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."

How can I fix that? (Or is there another easy way to implement
transactions within datasets without DTC?)

According to another news group entry it might never be possible to use
a transaction by hand:

# "Figured it out. The TableAdapterHelper sets all the commands
# transactions... but the .Update() command doesn't use an existing
# command, it dynamically builds one. This, of course, means that the
# transaction is not set on that command. So I'm using manual SQL
# statements instead of Update. "

If I am (hopefully!) wrong, how to do the transaction handling exactly?

 

[Joe Kovac]

According to another news group entry it might never be possible to use
a transaction by hand:

# "Figured it out. The TableAdapterHelper sets all the commands
# transactions... but the .Update() command doesn't use an existing
# command, it dynamically builds one. This, of course, means that the
# transaction is not set on that command. So I'm using manual SQL
# statements instead of Update. "

If I am (hopefully!) wrong, how to do the transaction handling exactly?

Anyone???