HowTo copy data between two databases using ado.net

[Miha Markic]

Hi,

I need to copy data between 2 sql servers using ado.net
this is currently done in vb6/ado record by record for the tables in a
config file

Since a dataset is disconnected, is there a way to pull all the data that's
required, set the row status flags to new record

Knowing which records are required is gona to be hard.
For new records:
One way would be to query target server for the last record id (in case you
have an autoincrement pk or something) and fetch only newer records from
source server.
For updates: you'll have to compare one ore more colums. (timestamp column
would be the best IMO)
Set AcceptChangesDuringFill to false - that will leave rows as "new" when
filled to datatable.
For updated rows you'll have to invoke AcceptChanges() on that row and
change a field to itself (that will cause the row to be marked as Modified).

, and call database update

using this dataset

No problem on that if your data in dataset is configured ok.

 

[msnews.microsoft.com]

I need to copy data between 2 sql servers using ado.net
this is currently done in vb6/ado record by record for the tables in a
config file

Since a dataset is disconnected, is there a way to pull all the data that's
required, set the row status flags to new record, and call database update
using this dataset

Thanks much
Suds
p/s
In case you're wondering, sql server replication does not work because of
firewall/security issues

 

[msnews.microsoft.com]

sorry i did not explain everything in detail but you are right on the mark
all the tables have an identity column and the config has the last updated
autoincrement value
so copying is rather simple with reading all rows with identity column >
last value
and insert them into the new database which have identity insert enabled

If i understand your post correctly, i need to set AcceptChangesDuringFill =
false before ds.Fill()
and then call dataadaptor.update(ds) and the target database gets updated,
right ?

 

[Miha Markic]

sorry i did not explain everything in detail but you are right on the mark
all the tables have an identity column and the config has the last updated
autoincrement value
so copying is rather simple with reading all rows with identity column >
last value
and insert them into the new database which have identity insert enabled

If i understand your post correctly, i need to set AcceptChangesDuringFill =
false before ds.Fill()
and then call dataadaptor.update(ds) and the target database gets updated,
right ?

Right. If you want to insert new rows, yes.

 

[Rick Reynolds]

Hello,

I read this thread and it's similar to something I'm trying to do. I
want to copy data between a text data source and an SQL table. I have
been able to get the data from the text file into a dataset but I don't
understand how to use that to insert records to a SQL server table. If
you could provide a little more detail on how this might be done it
would be greatly appreciated.


Thanks,
Rick

 

[Miha Markic]

Hi Rick,

When you insert the rows from text into dataset are you using Fill method?
If so, you should set adapter.AcceptChangesDuringFill = false.
In this case the rows will remain marked as Added.

You'll have to create an adapter that implements InsertCommand that will
store data from datatable to database.
Once you have it, just invoke its Update method on source datatable.
Ask for more details if you need to