Update SQL Server from WebService DataSet - Best Practice?

[Paul W]

I would appreciate any advice on best practice or better solution to the
following ...
I have a project where I retrieve a dataset from a webservice. I then use
writexml to persist the dataset to the Pocket PC CF or SD card. The data
will be manipulated over the course of several days before being sent back
to the SQL Server database. I am modifying data, accepting changes, etc...
with the dataset and using writexml frequently as a safe guard in case the
Pocket PC is reset or the battery dies. I do not want to use SQLCE for
various reasons, so I am opted to store the data on the Pocket PC as an xml
file. Once the data manipulation is complete, the dataset is sent back
using a webservice.
Currently, the webservice recieves the dataset and reads it table by table,
row by row and executes a stored procedure to update one row at a time in
the original SQL Server tables. I am only dealing with 4 tables and less
than 100 rows per table. The stored procedure takes the tables Primary Key
as an argument and overwrites all fields for that PK.
Is there a better way to update the SQL database?

 

[Ben Albahari [MS]]

Hi Paul,

Your approach sounds good - the only untidiness seems to be with upating
the keys. The stored procedures for inserting records should have an OUT
parameter that is set to @@identity (the value of the newly generated key)
after the insertion. The corresponding ADO.NET InsertCommand for the stored
procedure can then map that OUT parameter to the primary key column of the
DataTable. When you get back the dataset on the server from the client,
calling update on the DataAdapter with a given table will automatically
turn client generated keys in the datable into server generated keys in a
sql table, as well as updating the datable's keys. If you have set up
relations in your DataSet, all the foreign keys in related tables will get
updated too.

I'm not sure of your exact requirements, but I hope that helps,

Ben Albahari
NET Compact Framework
Microsoft

This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
| From: "Paul W" <[email protected]>
| Newsgroups: microsoft.public.dotnet.framework.compactframework
| Subject: Update SQL Server from WebService DataSet - Best Practice?
| Lines: 19
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <u%Nfb.41511$%h1.28754@sccrnsc02>
| NNTP-Posting-Host: 12.253.93.209
| X-Complaints-To: (e-mail address removed)
| X-Trace: sccrnsc02 1065331802 12.253.93.209 (Sun, 05 Oct 2003 05:30:02
GMT)
| NNTP-Posting-Date: Sun, 05 Oct 2003 05:30:02 GMT
| Organization: Comcast Online
| Date: Sun, 05 Oct 2003 05:30:02 GMT
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onlin
e.de!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!small1.nntp.aus1.gigane
ws.com!border3.nntp.aus1.giganews.com!nntp.giganews.com!wn14feed!worldnet.at
t.net!204.127.198.203!attbi_feed3!attbi_feed4!attbi.com!sccrnsc02.POSTED!not
-for-mail
| Xref: cpmsftngxa06.phx.gbl
microsoft.public.dotnet.framework.compactframework:35186
| X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework
|
| I would appreciate any advice on best practice or better solution to the
| following ...
| I have a project where I retrieve a dataset from a webservice. I then use
| writexml to persist the dataset to the Pocket PC CF or SD card. The data
| will be manipulated over the course of several days before being sent back
| to the SQL Server database. I am modifying data, accepting changes,
etc...
| with the dataset and using writexml frequently as a safe guard in case the
| Pocket PC is reset or the battery dies. I do not want to use SQLCE for
| various reasons, so I am opted to store the data on the Pocket PC as an
xml
| file. Once the data manipulation is complete, the dataset is sent back
| using a webservice.
| Currently, the webservice recieves the dataset and reads it table by
table,
| row by row and executes a stored procedure to update one row at a time in
| the original SQL Server tables. I am only dealing with 4 tables and less
| than 100 rows per table. The stored procedure takes the tables Primary
Key
| as an argument and overwrites all fields for that PK.
| Is there a better way to update the SQL database?
|
|
|