XML->Dataset->Database

[Guest]

I am developing a type of data warehouse. Basically, we have 20 client survey
machines that are each separated from the server by space and lack of
network. Yet both need to speak to each other such that the clients know the
latest configuration information and the server contains completed survey
data from the clients. Currently I'm accomplishing this by using a few
special database tables to control the flow of information, and using XML
serialized datasets on both ends and a USB drive to move them back and forth.
The design seems to work.

However, some of these tables have 50+ columns and creating manual insert
statements has gotten absurd. For example, on the server I generate a dataset
based on what needs to be sent to the client, it works fine. I then read in
that XML into a dataset on the client. So now, how do I best get that data
into the SQL database on the client side? The only way I've found to do it is
to create a DataAdapter and insert each row at a time, after setting up all
the commands, parameters, and filling out each row. This is absurd, it winds
up being hundreds of lines. I really want something like this:
Dataset ds;
ds.connection = my connection;
ds.update();

After all, the schema is exactly the same on both ends! So, I don't really
expect the above, but there has to be an easier way than reinventing the
wheel. Basically, I have a dataset full of data that matches the database I
want to get it inside, only it wasn't taken from that database so it's not
just a simple sqladapter.update() call.

Any ideas? Thanks in advance.

 

[Balasubramanian Ramanathan]

Why can't you use bulk import and export feature of sqlserver. you can
import export xml documents also..

 

[Guest]

Well, the main reason is that I want to edit the dataset before I import the
data. For example, the server sends down a lot of data, I import it into a
dataset, and depending on settings or actions the user makes I want to remove
or edit records in that dataset before I put it into the actual database.

Thanks
David

 

[Balasubramanian Ramanathan]

Here is a good example
http://www.codeproject.com/cs/database/generic_OpenXml.asp. Meets your
requirement

 

[Guest]

That definitely looks like it would work. However, I have one idea, can you
look and see if this passes the logic test? I have limited time to test this
in field conditions, so I have to get it right and complete as possible
before I try actual testing...

Basically, I create two datasets, one for the XML I bring in from the
server, and one for the current client database. I then fill the client
dataset with the current information in the database, update it, and call the
Update method on it. DStransfer is the dataset schema.

DStransfer dsClient = new DStransfer();
DStransfer dsServer = new DStransfer();
// ... Code to read in from xml to dsServer
// ... Code to read in from Client DB to dsClient
foreach(DStransfer.SurveyRow r in dsServer.Survey.Rows) {
dsClient.Survey.AddSurveyRow(r);
}
// repeat above for each table in the dataset...

Basically, I just move each row from one dataset to the other, and then call
update on the original DataAdapter that filled the client dataset... Does
this look like it will work? I know it'll be expensive performance-wise, but
that's not an issue this time around.

Thanks,
David

 

[Balasubramanian Ramanathan]

Yeh...It should work without any problem. one more suggestion..instead of
adding rows one by one you can use Merge method of the datatable.