Adding data to database from a web service

[Thomas Due]

We have a customer who would like to tranfer data from one database to
another. These two databases are in different physical locations, and
are not on the same network. The customer do not wish for the databases
to be on the same network.

We have come up with an idea of using a webservice to transport the
data from one database to another. It is a bit involved, but basically
what happens is:

Move data from Database1 to Database3:

Data from Database1 is copied onto a intermediate database (Database2)
through ftp (we have software that handles this). There is a webservice
on the server containing Database2).

A client runs on a machine containing Database3, this client connects
to the webservice and retrieves a ado.net DataSet containing data from
Database1.

Now my question is: How do I get the data in this DataSet into
Database3?

I have experimented with Merge, but I dont seem to get anywhere.

--
Thomas Due
Posted with XanaNews version 1.17.1.2

"The best victory is when the opponent surrenders of its own accord
before there are any actual hostilities...It is best to win without
fighting."
-- Sun-tzu, The Art of War. Planning a Siege

 

[Sahil Malik]

Thomas,

What you need is SqlDataAdapter.Update .. instead of Merge.
Depending upon your data schemas, you might need to specify the various
commands on it's various properties
(SelectCommand,InsertCommand,UpdateCOmmand, DeleteCommand).

Now I must add though - first of all ADO.NET is not a good candidate for
database copy - because it introduces a lot of additional bloat. ADO.NET 2.0
has a class called SqlBulkCopy that makes life a bit easier though. Try
doing DTS, export as VBScript, and modify to VB.NET - if that is something
you can do. ANd there is always backup restore of data, bcp, or plain SQL
Scripts even.

Secondly COmmandBuilder generates very inefficient commands by default - and
that is what you'll see if you donot specify the command explicitly in para
#1.

 

[Thomas Due]

Now I must add though - first of all ADO.NET is not a good candidate
for database copy - because it introduces a lot of additional bloat.
ADO.NET 2.0 has a class called SqlBulkCopy that makes life a bit
easier though. Try doing DTS, export as VBScript, and modify to
VB.NET - if that is something you can do. ANd there is always backup
restore of data, bcp, or plain SQL Scripts even.

For other reasons as well, I have decided to scrap the SqlDataAdapter
and SqlCommandBuilder completely and iterate through the imported
dataset manually.

I will need to "personally" handle each record in the future, so I
might as well prepare the code right now.

I dont think you other suggestions (DTS, backup/restore) will work, as
I am transporting data across the internet. Backup/restore would of
course work, as I could just stream the file, but I dont need the
entire database, only a subset of records.

Thanks for you input, it helped make this decision.

--
Thomas Due
Posted with XanaNews version 1.17.1.2

"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche