datasets over webservice / size

[Florian Lutz]

Hello,
i have a simple Webservice, that gives me some data from a sql database
as a DataSet. On the pocketpc i modify that dataset and write it back
to the sqldb (via a webservice). my questions: is there a way to get
the size of the data thats being send back? are only modified values
send back to the webservice?

thanks
florian

 

[Jan Yeh_eMVP]

Hi, florian

From your scenario, you might have 2 web methods in your web service.
One is for pull down data from SQL Server, and the other is for send back
data into SQL Server.

For example, if you pull down large data, and modify 3 rows of records
from the dataset, you can just send the 3 rows back to server( carried in
another dataset), instead of sending the whole dataset.

--

Best Regards,

Jan Yeh
MVP - Windows CE.NET, MCSD.NET, .NETcf consultant
Web - http://www.mobilemind.com.tw , Blog - http://blog.mvpcn.net/janyeh

 

[Ilya Tumanov [MS]]

No, DataSet does not know WS has some data already (in fact it has no idea
where data came from), so I sends everything.

In desktop framework you can use DataSet.GetChanges() to get modified rows.
This method is unavailable in CF V1 (available in CF V2), however.

You can easily create a workaround:

1. Clone the DataSet (won't copy data, just schema).
2. For each DataTable in DataSet
3. For each DataRow in DataTable
4. Check DataRowState
5. If it's not Unchanged, add it to cloned table.
6. Send cloned DataSet which now contains changed data.

Alternatively, you could use SQL Client to interact with SQL Server
database directly.
That would be significantly faster than WS and would only update changed
rows for you without extra work on your side.

Best regards,

Ilya

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

--------------------

 

[Florian Lutz]

Hello Ilya,

when i save the dataset to my device (ds.WriteXml(...) and
ds.WriteXmlSchema(...)), read it again
(someOtherDataSet.readXmlSchema(...) and someOtherDataSet.readXml(...))
and then then try to send the new dataset to my webservice i get a
"Violation of Primary Key constraint 'pk_test'. Cannot insert duplicate
key in object 'test'."
strangely enough i don't get that error if i send the original
in-memory dataset back. what is the difference. how can i solve this?
florian

 

[Ilya Tumanov [MS]]

You mean, exception on a desktop while trying to receive data from device?
Do you have hidden autoincrement primary key?

Best regards,

Ilya

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

 

[Florian Lutz]

the webservice reports this exception back to the pocketpc client (i
just paste the exception back as a string)

Do you have hidden autoincrement primary key?

you mean in the DB? how can i find out?

florian

 

[Ilya Tumanov [MS]]

I see...

As to primary key, I meant in DataSet (which should be a reflection of your
DB).
To find out if you have a hidden autoincrement key, you can do something
like this:

foreach(DataTable dt in dataSet) {
foreach (DataColumn dc in dt) {
if (( dc.ColumnMapping == MappingType.Hidden) && dc.AutoIncrement) {
Console.WriteLine ("Got hidden autoincrement column '{0}'",
dc.ColumnName);
}
}
}

If you do, I suspect that information might be lost as it's not saved to
XML (since it's hidden).
You could you also try saving XML in diffgram format, that should preserve
hidden columns (WS uses diffgram format).

And, by the way, could you please post some code from WS side? Thanks.

Best regards,

Ilya

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

--------------------

 

[Florian Lutz]

hello Ilya,

there is no hidden autoincrement key in my dataset. saving the dataset
as a diffgram works excellent.
i found something else:
when i copy all modified rows to a new dataset, their rowstate becomes
"inserted" (checked the local diffgram xml copy)
uploading them causes a primary key constraint violation (makes sense,
they are "inserted" so uploading them would create a copy with the same
primary key)
calling a acceptchanges on the dataset that contains the changed rows,
removes the "inserted" state. an upload works, however the update
method returns 0 updated rows. no updates on the server side.
manually setting them to "modified" seems not possible (cannot change
the rowstate manually)

so i guess i have to change something on the server...? the setup there
relatively simple, just a database and i used the vs.net2003 wizard to
create a data adapter.
the only thing that makes me wonder is the following (autogenerated)
code:

this.sqlUpdateCommand1.CommandText = @"UPDATE manifest SET id = @id,
cash = @cash, signature = @signature, WHERE (id = @Original_id)";
this.sqlUpdateCommand1.Connection = this.sqlConnection1;
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@id", System.Data.SqlDbType.BigInt,
8, "id"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@cash", System.Data.SqlDbType.Bit,
1, "cash"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@signature",
System.Data.SqlDbType.VarBinary, 2147483647, "signature"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@Original_id",
System.Data.SqlDbType.BigInt, 8, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "id",
System.Data.DataRowVersion.Original, null));
whatis the original id?

florian

 

[Ilya Tumanov [MS]]

I see... Well, if you need to preserve a row state, you can use diffgram
format for storage.
Or, you can merge original DataSet with DataSet from device on the server
and submit merged result to SQL.

Best regards,

Ilya

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

--------------------

 

[Jan Yeh_eMVP]

Hello, Florian

If you want to make your dataset with "modified" rowstate,
you can change the row data with the same value, that will make it
"modified"
without changing your data.

For example,
for(int i=0; i<dataTable.Rows.Count; i++)
{
dataTable.Rows[0].Value = dataTable.Rows[0].Value;
}

--

Best Regards,

Jan Yeh
MVP - Windows CE.NET, MCSD.NET, .NETcf consultant
Web - http://www.mobilemind.com.tw , Blog - http://blog.mvpcn.net/janyeh

 

[Florian Lutz]

Perfect! it is working now! thanks all for helping me...
have a good new year,

florian