XML->Dataset->Database

[David Harris]

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.upda te() call.

Any ideas? Thanks in advance.

[Balasubramanian Ramanathan]

Re: XML->Dataset->Database

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

"David Harris" <DavidHarris@di scussions.micro soft.com> wrote in message
news:4BE8FD35-11EF-41AD-932E-476AFBE22841@mi crosoft.com...[color=blue]
>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.upda te() call.
>
> Any ideas? Thanks in advance.[/color]

[David Harris]

Re: XML-&gt;Dataset-&gt;Database

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

"Balasubramania n Ramanathan" wrote:
[color=blue]
> Why can't you use bulk import and export feature of sqlserver. you can
> import export xml documents also..[/color]

[Balasubramanian Ramanathan]

Re: XML-&gt;Dataset-&gt;Database

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


"David Harris" <DavidHarris@di scussions.micro soft.com> wrote in message
news:3F43951F-D1D9-4455-B9D1-FDC1F2F2A41C@mi crosoft.com...[color=blue]
> 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
>
> "Balasubramania n Ramanathan" wrote:
>[color=green]
>> Why can't you use bulk import and export feature of sqlserver. you can
>> import export xml documents also..[/color]
>[/color]

[David Harris]

Re: XML-&gt;Dataset-&gt;Database

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(DStrans fer.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


"Balasubramania n Ramanathan" wrote:
[color=blue]
> Here is a good example
> http://www.codeproject.com/cs/databa...ic_OpenXml.asp. Meets your
> requirement[/color]

[Balasubramanian Ramanathan]

Re: XML-&gt;Dataset-&gt;Database

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

"David Harris" <DavidHarris@di scussions.micro soft.com> wrote in message
news:ADF2A5C5-6D04-4046-9733-629EAB5E4805@mi crosoft.com...[color=blue]
> 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(DStrans fer.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
>
>
> "Balasubramania n Ramanathan" wrote:
>[color=green]
>> Here is a good example
>> http://www.codeproject.com/cs/databa...ic_OpenXml.asp. Meets your
>> requirement[/color]
>[/color]