HowTo copy data between two databases using ado.net
msnews.microsoft.com
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
Miha Markic
"msnews.microsoft.com" <A...@B.COM> wrote in message
news:e2oXGcKw...@TK2MSFTNGP09.phx.gbl...
> 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.
--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
msnews.microsoft.com
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" <miha at rthand com> wrote in message
news:OjtfJzKw...@TK2MSFTNGP10.phx.gbl...
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.
--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
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
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Miha Markic
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 :)
--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rhand.com
"Rick Reynolds" <rrey...@warrantycorp.com> wrote in message
news:eBjvlfZy...@TK2MSFTNGP09.phx.gbl...