updating from one data source to another

[Jay Nesbitt]

A common scenario I run into is to grab data from one data source and place
it in a dataset, modify the data, and then insert the data into another
datasource. The problem is that when I use the SqlDataAdapter's update
command it doesn't work because the Row versions are all set to Unchanged or
Modified and not New so the insert commands never get run. The row version
property is read only so it can't be changed. Is there a good work around
for this?
Thanks!

Jason

 

[Joe Fallon]

I have the same problem.
It would be so much simpler if we could change the property from Modified to
New (or vice versa.)

I have hacked up a workaround which is inelegant.

Any other ideas?

 

[Baskar]

Use AcceptChangesDuringFill Property of the DataAdapter object...Hope this
helps..

This is the excerpt from the ADO.NET reference book...
When I worked as a support engineer helping developers who were having
problems using ADO, I was amazed at how many developers were trying to use
ADO as some sort of data synchronization tool. They would query one database
and then point their Recordset at a different database and call Update,
expecting that ADO would synchronize the tables in the two databases. ADO
could not do that. But ADO.NET can.sort of.

The DataAdapter object has an AcceptChangesDuringFill property that accepts
a Boolean value. This property, which is set to True by default, controls
the RowState of the rows retrieved by the DataAdapter. If the property is
set to True, the new DataRow objects will each have a RowState of Unchanged.
Setting AcceptChangesDuringFill to False causes the new DataRow objects to
have a RowState of New.

This means that if you set AcceptChangesDuringFill to False, you can query a
table in one database and then pass the DataSet to a DataAdapter that's set
to communicate with another database and insert all of the newly retrieved
rows into this other database.

 

[David Sceppa]

Joe,

You could set the DataAdapter's InsertCommand to execute a
query like the following:

IF NOT EXISTS(SELECT ID FROM MyTable WHERE ID = @ID)
BEGIN
-- Insert Scenario
INSERT INTO MyTable (...) VALUES (...)
END
ELSE
BEGIN
-- Update Scenario
UPDATE MyTable SET ... WHERE ...
END

Of course, placing this logic in a stored procedure and
calling the stored procedure from the InsertCommand would be an
even better idea.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.

 

[Joe Fallon]

David,
Thanks for the hint.

Just finished your book on ADO.Net.
Very well written and very useful.