473,238 Members | 3,271 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,238 software developers and data experts.

XML->Dataset->Database

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.
Jun 14 '06 #1
5 1412
Why can't you use bulk import and export feature of sqlserver. you can
import export xml documents also..

"David Harris" <Da*********@discussions.microsoft.com> wrote in message
news:4B**********************************@microsof t.com...
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.

Jun 14 '06 #2
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" wrote:
Why can't you use bulk import and export feature of sqlserver. you can
import export xml documents also..


Jun 14 '06 #3
Here is a good example
http://www.codeproject.com/cs/databa...ic_OpenXml.asp. Meets your
requirement
"David Harris" <Da*********@discussions.microsoft.com> wrote in message
news:3F**********************************@microsof t.com...
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" wrote:
Why can't you use bulk import and export feature of sqlserver. you can
import export xml documents also..

Jun 14 '06 #4
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" wrote:
Here is a good example
http://www.codeproject.com/cs/databa...ic_OpenXml.asp. Meets your
requirement


Jun 14 '06 #5
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.

"David Harris" <Da*********@discussions.microsoft.com> wrote in message
news:AD**********************************@microsof t.com...
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" wrote:
Here is a good example
http://www.codeproject.com/cs/databa...ic_OpenXml.asp. Meets your
requirement


Jun 14 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Piyoosh Rai | last post by:
Hi, I have multiple XML files that I am reading into a DataSet in C#. What I would want to do it Create Tables from the DataSet in the destination database and fill the tables with data from the...
2
by: Steven (dotnet newbie) | last post by:
Hello. I am trying to create a database from an XML file. I am able to create a dataset from the XML doc, but how can I create a database schema from the dataset and populate the database? Or is...
1
by: Trey | last post by:
Let me ask one more time! If you have an application that uses a DataSet (System.Data) but the dataset is only loaded with XML data. Do you need to deploy MDAC with your application? Trey
0
by: Chris Kennedy | last post by:
I am having big problem manipulating XML in a dataset. I am reading in a schema based on the following XML document. The minute I try to e.g. create a new row I get 'Object reference not set to an...
2
by: JAS | last post by:
I have made client software where users make orders. Local data is stored in xml file. Users are offline from network most of time eg. one week or month and then they connect dbserver. Problem:...
1
by: buran | last post by:
Dear ASP.NET Programmers, I need your help on the following case: 1. I have a page coverpage.aspx, on which I load invoice data into the datalist control dliHospCosts. I use the stored procedure...
1
by: Maziar Aflatoun | last post by:
Hi, I have a multi-level complex XML that I like to process and store in the database. I'm comfortable with using DataSet and then reading the tables (XML is parsed into 7 tables in my case) and...
5
by: Benny Raymond | last post by:
I'm nearly done writing the first version of my tracking app and I'm starting to re-think the way I store my database. Currently I have my database setup as an xml dataset. Before I started the...
8
by: Sam | last post by:
Hi there! I would like to know: a) How to read xml datasets from cache? b) How to save it as xml and map the fields and update the my sql / SYbase database? Please help me out.
2
by: paulhux174 | last post by:
Hi, Is there a way of writing some xml data to a database table directly. I know you can extract the values then insert or update but can you some how create a xml dataset representation of the...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.