HowTo copy data between two databases using ado.net

  • Thread starter Thread starter Miha Markic
  • Start date Start date
M

Miha Markic

Hi,


msnews.microsoft.com said:
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
Click to expand...

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
Click to expand...

No problem on that if your data in dataset is configured ok.
 
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
 
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 ?
 
msnews.microsoft.com said:
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 ?
Click to expand...

Right. If you want to insert new rows, yes.
 
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
 
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 :)
 
Back
Top