473,289 Members | 2,040 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,289 software developers and data experts.

UpdateDB from DataSet

Al
I have this scenario:
1. XML file with schema and data is created from SQL Server tables. XML file
contains 6 tables, some of them have rows, some of them are empty.
2. XML file is given to the person with Pocket PC.
3. XML file is used to populate DataSet on the Pocket PC.
4. User adds new rows, changes values, deletes some rows.
5. Altered dataset is saved back to XML file.
6. XML file is given back to the person who initially created it.
7. XML file is used to populate DataSet.

I only know the way to update SQL Server tables by going through each record
in each table.
I was wondering if there is more simple and compact way to bring updated
stuff into SQL Server DB?
Everything is going to be done in VB 2005

Thank you
Al
Aug 4 '06 #1
4 2205
Al
I forgot to tell that all tables in a database have primary keys.

Al

"Al" <al@newsgroups.comwrote in message
news:%2******************@TK2MSFTNGP05.phx.gbl...
>I have this scenario:
1. XML file with schema and data is created from SQL Server tables. XML
file contains 6 tables, some of them have rows, some of them are empty.
2. XML file is given to the person with Pocket PC.
3. XML file is used to populate DataSet on the Pocket PC.
4. User adds new rows, changes values, deletes some rows.
5. Altered dataset is saved back to XML file.
6. XML file is given back to the person who initially created it.
7. XML file is used to populate DataSet.

I only know the way to update SQL Server tables by going through each
record in each table.
I was wondering if there is more simple and compact way to bring updated
stuff into SQL Server DB?
Everything is going to be done in VB 2005

Thank you
Al


Aug 4 '06 #2
Al wrote:
I have this scenario:
1. XML file with schema and data is created from SQL Server tables. XML file
contains 6 tables, some of them have rows, some of them are empty.
2. XML file is given to the person with Pocket PC.
3. XML file is used to populate DataSet on the Pocket PC.
4. User adds new rows, changes values, deletes some rows.
5. Altered dataset is saved back to XML file.
6. XML file is given back to the person who initially created it.
7. XML file is used to populate DataSet.

I only know the way to update SQL Server tables by going through each record
in each table.
I was wondering if there is more simple and compact way to bring updated
stuff into SQL Server DB?
Everything is going to be done in VB 2005

Thank you
Al

You can do the update using datasets. Perhaps not nearly as fast as
direct SQL calls, but probably easier to code/maintain.

Basically, you can reconstruct the "original" data as a dataset, then
"merge" it with the changed rows in the client's dataset, which we'll
call dsModified.

So on the client side, you can do dsModifed.GetChanges to make the
dataset have only changed rows in it. (Assuming GetChanges is available
int the compact framework, if that's what you're using.)

Then do dsModified.WriteXML or similar to write out the XML of this
dataset.

Then on the server side, as you say, reconstruct the client dataset
from the XML with the usual methods.

At this point, since you just constructed a new dataset from your XML,
I believe none of the rows will be flagged as "modified", which is bad,
because a dataset's Merge method will merge *only* those rows that are
marked as Modified. This means you have to use a "For Each" to do
row.SetModified on every row. This is likely to slow things down quite
a bit. (By the way, SetModified exists only in .NET 2.0.)

Then, still one server side -- this is another one of the slow parts --
build a dataset (called dsOriginal) that matches the schema of the
dataset you just built from XML. This can probably be done by using the
same method that built the dataset you sent to the client way back
when.

Then you can just do something like:

dsOriginal.Merge(dsModified)

Then update the data adapter used to create dsOriginal. This will write
the changes back to the database:

da.Update

If the Update command doesn't seem to be working (i.e., the changes
aren't being written to the database), try checking the number of data
tables in both datasets after the merge. I had a real head-scratcher
recently where the Merge wasn't working, and it turned out that
ds.Merge was adding the modified data as a new table instead of merging
it with the existing table. I solved this by using the data table's
Merge method instead of the dataset's, since my ds contained only one
table.

Other people with more experience might dismiss the above technique as
slow and/or inelegant. I welcome their criticism...I am offering this
method only as an example of how I have solved this problem, and would
love to hear of faster, more elegant solutions.

adm

Aug 4 '06 #3
Al
adm,
thank you very much.
I just started to work with Compact Framework and all the time have problems
with its huge limitations, as well as my limited knowledge. So to do
something you are suggesting (on pocket pc side) is going to be a nightmare.
If I will not find anything simple I'll try to do update step by step
comparing each row in each table in 2 datasets.
It looks like a long but clear to understand way. Maybe I'm wrong.

I also have another idea (probably stupid). What if I'll try to do this:
1. I populate DataSet1 with data from the database
2. I populate DataSet2 with data from client XML file
3. I replace tables in Dataset1 with tables from DataSet2 (I do not know if
it's possible)
4. I send data from DataSet1 back to db

I see the problem with deleted records, but in my applications I never
phisically delete any row - I have RowDeleted column in each table and in
case user wants to delete some records they are marked as deleted and never
appear in any row sets.

So everything I need is adding new rows and updating existing ones.

Al

<ad*****@yahoo.comwrote in message
news:11**********************@75g2000cwc.googlegro ups.com...
Al wrote:
>I have this scenario:
1. XML file with schema and data is created from SQL Server tables. XML
file
contains 6 tables, some of them have rows, some of them are empty.
2. XML file is given to the person with Pocket PC.
3. XML file is used to populate DataSet on the Pocket PC.
4. User adds new rows, changes values, deletes some rows.
5. Altered dataset is saved back to XML file.
6. XML file is given back to the person who initially created it.
7. XML file is used to populate DataSet.

I only know the way to update SQL Server tables by going through each
record
in each table.
I was wondering if there is more simple and compact way to bring updated
stuff into SQL Server DB?
Everything is going to be done in VB 2005

Thank you
Al


You can do the update using datasets. Perhaps not nearly as fast as
direct SQL calls, but probably easier to code/maintain.

Basically, you can reconstruct the "original" data as a dataset, then
"merge" it with the changed rows in the client's dataset, which we'll
call dsModified.

So on the client side, you can do dsModifed.GetChanges to make the
dataset have only changed rows in it. (Assuming GetChanges is available
int the compact framework, if that's what you're using.)

Then do dsModified.WriteXML or similar to write out the XML of this
dataset.

Then on the server side, as you say, reconstruct the client dataset
from the XML with the usual methods.

At this point, since you just constructed a new dataset from your XML,
I believe none of the rows will be flagged as "modified", which is bad,
because a dataset's Merge method will merge *only* those rows that are
marked as Modified. This means you have to use a "For Each" to do
row.SetModified on every row. This is likely to slow things down quite
a bit. (By the way, SetModified exists only in .NET 2.0.)

Then, still one server side -- this is another one of the slow parts --
build a dataset (called dsOriginal) that matches the schema of the
dataset you just built from XML. This can probably be done by using the
same method that built the dataset you sent to the client way back
when.

Then you can just do something like:

dsOriginal.Merge(dsModified)

Then update the data adapter used to create dsOriginal. This will write
the changes back to the database:

da.Update

If the Update command doesn't seem to be working (i.e., the changes
aren't being written to the database), try checking the number of data
tables in both datasets after the merge. I had a real head-scratcher
recently where the Merge wasn't working, and it turned out that
ds.Merge was adding the modified data as a new table instead of merging
it with the existing table. I solved this by using the data table's
Merge method instead of the dataset's, since my ds contained only one
table.

Other people with more experience might dismiss the above technique as
slow and/or inelegant. I welcome their criticism...I am offering this
method only as an example of how I have solved this problem, and would
love to hear of faster, more elegant solutions.

adm

Aug 4 '06 #4

Help me understand your application architecture here. My impression
was that it went something like this:

1. Server (a PC) sends "original" data to client (a Pocket PC).
2. User modifies the data on the client.
3. Client re-connects to server.
4. New/modified rows are merged with original data.

Is this true? If so, most of the operations I describe below operate on
the server side, which will have the full .NET framework, and so the
limitations of the client side don't matter.

ds.WriteXML and ds.GetChanges, the two functions that need to be run on
the client, are supported by the compact framework, as described here:
http://tinyurl.com/kmwvk

That is good news for you, and suggests that, unless I have
misunderstood something, the technique I described in my previous
message should work for you.


Al wrote:
adm,
thank you very much.
I just started to work with Compact Framework and all the time have problems
with its huge limitations, as well as my limited knowledge. So to do
something you are suggesting (on pocket pc side) is going to be a nightmare.
If I will not find anything simple I'll try to do update step by step
comparing each row in each table in 2 datasets.
It looks like a long but clear to understand way. Maybe I'm wrong.

I also have another idea (probably stupid). What if I'll try to do this:
1. I populate DataSet1 with data from the database
2. I populate DataSet2 with data from client XML file
3. I replace tables in Dataset1 with tables from DataSet2 (I do not know if
it's possible)
4. I send data from DataSet1 back to db

I see the problem with deleted records, but in my applications I never
phisically delete any row - I have RowDeleted column in each table and in
case user wants to delete some records they are marked as deleted and never
appear in any row sets.

So everything I need is adding new rows and updating existing ones.

Al

<ad*****@yahoo.comwrote in message
news:11**********************@75g2000cwc.googlegro ups.com...
Al wrote:
I have this scenario:
1. XML file with schema and data is created from SQL Server tables. XML
file
contains 6 tables, some of them have rows, some of them are empty.
2. XML file is given to the person with Pocket PC.
3. XML file is used to populate DataSet on the Pocket PC.
4. User adds new rows, changes values, deletes some rows.
5. Altered dataset is saved back to XML file.
6. XML file is given back to the person who initially created it.
7. XML file is used to populate DataSet.

I only know the way to update SQL Server tables by going through each
record
in each table.
I was wondering if there is more simple and compact way to bring updated
stuff into SQL Server DB?
Everything is going to be done in VB 2005

Thank you
Al

You can do the update using datasets. Perhaps not nearly as fast as
direct SQL calls, but probably easier to code/maintain.

Basically, you can reconstruct the "original" data as a dataset, then
"merge" it with the changed rows in the client's dataset, which we'll
call dsModified.

So on the client side, you can do dsModifed.GetChanges to make the
dataset have only changed rows in it. (Assuming GetChanges is available
int the compact framework, if that's what you're using.)

Then do dsModified.WriteXML or similar to write out the XML of this
dataset.

Then on the server side, as you say, reconstruct the client dataset
from the XML with the usual methods.

At this point, since you just constructed a new dataset from your XML,
I believe none of the rows will be flagged as "modified", which is bad,
because a dataset's Merge method will merge *only* those rows that are
marked as Modified. This means you have to use a "For Each" to do
row.SetModified on every row. This is likely to slow things down quite
a bit. (By the way, SetModified exists only in .NET 2.0.)

Then, still one server side -- this is another one of the slow parts --
build a dataset (called dsOriginal) that matches the schema of the
dataset you just built from XML. This can probably be done by using the
same method that built the dataset you sent to the client way back
when.

Then you can just do something like:

dsOriginal.Merge(dsModified)

Then update the data adapter used to create dsOriginal. This will write
the changes back to the database:

da.Update

If the Update command doesn't seem to be working (i.e., the changes
aren't being written to the database), try checking the number of data
tables in both datasets after the merge. I had a real head-scratcher
recently where the Merge wasn't working, and it turned out that
ds.Merge was adding the modified data as a new table instead of merging
it with the existing table. I solved this by using the data table's
Merge method instead of the dataset's, since my ds contained only one
table.

Other people with more experience might dismiss the above technique as
slow and/or inelegant. I welcome their criticism...I am offering this
method only as an example of how I have solved this problem, and would
love to hear of faster, more elegant solutions.

adm
Aug 4 '06 #5

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

Similar topics

3
by: Bill C. | last post by:
Hi, I've got a simple console app that just reads an XML file into a DataSet then prints out a description of each table in the DataSet, including column names and row values for each column. ...
1
by: Andy | last post by:
Hello, I have a WebService that sends a client a DataSet as XML (I use a DataSet.GetXml to get the XML). The DataSet is filled by a DataAdapter in the WebService. The client coverts the XML Back...
3
by: Jeronimo Bertran | last post by:
Hi, I have an xml file that encapsulates a dataset definition within a set of tags (<dataset>)... here is an example <?xml version="1.0" encoding="utf-16"?> <dataset> <MyTable>...
2
by: JS | last post by:
I'm trying to create a data layer and having problems returning a DataSet from my code that's in a class module. Please forgive me. I'm new to C# (VB'er). I decided to create my data layer in...
5
by: Mike | last post by:
I need to expand the DataSet class by inheriting from it and adding functions that work on the data in the tables. However, since I can't upcast how can I get my base DataSet object assigned an...
2
by: John Holmes | last post by:
I have a web interface where the user types in ID's one at a time. After an ID is typed in, a button is clicked and the button click event has code that does a query and returns a data reader and...
22
by: Arne | last post by:
How do I pass a dataset to a webservices? I need to submit a shoppingcart from a pocket PC to a webservice. What is the right datatype? II have tried dataset as a datatype, but I can't get it to...
4
by: Al | last post by:
I have this scenario: 1. XML file with schema and data is created from SQL Server tables. XML file contains 6 tables, some of them have rows, some of them are empty. 2. XML file is given to the...
1
by: matt | last post by:
hello, i have a web app that allows users to query our oracle db and produce a dataset of report data. they then have the option to serialize this data and store it in the database. later, then...
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: 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:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
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 =...
1
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...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

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.