Moving data from one db to another for identical tables

[dalchri]

Here is what I want to do:

Fill a dataset with one adapter using one connection. Use the same dataset to Update using a second adapter and a second connection. The only problem is that I cannot find a way to trick the dataset into thinking that all of the rows in its datatables were added and hence need added to the second connection. The RowState property is readonly.

Two practical applications:

1) I have an online and offline db so our salespeople can work on the road without being connected to our office. I want them to be able to copy all orders for identical tables in an online and offline (local) db.

2) We have standard models that we sell. Each order uses these standard specs as a "template", essentially copying a query of the models table -> the orders table.

Now, I have pulled this off by creating a second dataset, then iterating through each row in the first dataset and creating a identical rows in the second dataset. However, it seems like you should be able to skip (1) the redundant memory usage and (2) the time it takes to do this.

Any ideas?

 

[sunaj]

Hi,

You should be able to do it the direct way using JET sql. It could look something like:
--------------------------------------
Dim Conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sourcedb)
Conn.Open()
Dim Comm As New OleDb.OleDbCommand("INSERT INTO tblTest (MyString, MyNumeric) IN '" & targetdb & "' SELECT MyString,MyNumeric FROM tblTest2", Conn)
Comm.ExecuteNonQuery()
conn.close
---------------------------------------

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[dalchri]

How I miss those days. This is a MySQL database. No such functionality available.

Thanks for the suggestion. Actually, I guess I could create a temporary database and link the tables.

 

[Jables]

dalchri,

Did you ever find a good solution to your problem. I am in a similar situation. Could you explain how you iterate through the rows of the first dataset to duplicate the row in the second dataset?

 

[BikerDave2]

Please also notify me if you have any information on this. It would probably speed up my application greatly.
Thanks

 

[dalchri]

Something I saw in my travels that I have not had a chance to try out is a property on the DataAdapter object.

There should be a property called AcceptChangesDuringFill. I suspect that if you set it to false, all the rows added by the Fill method will remain marked as added rows.

Then you could either change the Insert command to hit a different table or change the connection object to hit the same table in a different database when you call the Update method.

Please let me know if it works out 'cause I haven't tried it out yet.

Thanks!