Compact Framework and SqlCe questions

[Steve B.]

Hello,

I'm building an application based on Sql Server CE.
I'm trying to create a SqlCeDataAdapter using the same structure than Visual
Studio generates for a Sql Server 2000 DB.
I want my Data Adapter to use optimistic concurency, and reload the dataset
after an update or an insert.

My Update command is this one :

UPDATE
Customers SET CustomerID = 'f39cc1b9-4bfa-4fba-9c68-7e4b179d31b8', <-- It
is my PK
CustomerCompanyName = 'IBM',
CustomerAdress = '20 route du Rhin 67500 Strasbourg',
FollowedByID = '08f1251d-182d-4c4b-92e7-03a021744d80'
WHERE (CustomerID = 'f39cc1b9-4bfa-4fba-9c68-7e4b179d31b8')
AND (CustomerCompanyName = 'the customer')
AND (FollowedByID = '08f1251d-182d-4c4b-92e7-03a021744d80');
SELECT CustomerID,
CustomerCompanyName,
CustomerAdress,
FollowedByID
FROM
Customers
WHERE (CustomerID = 'f39cc1b9-4bfa-4fba-9c68-7e4b179d31b8')

This command does not work :
1. The SELECT command does not works... I get a parsing error. If I remove
everything after the ';' (included), it can be executed. Does SQL Ce
supports two request in on call ?
2. The data adapter created for desktop application updates the PK. With SQL
Ce I always get an error that tells me the primary key cannot be deleted
because of related records. I do not actually modify the value (ie SET a =
a) but the system seems to do an 'delete' / 'insert' ...

Thanks,
Steve

 

[Darren Shaffer]

A couple of things going on here:

1. a SqlCeCommand cannot contain a batch of commands - you're trying to
pass two commands separated by a semicolon
2. you are trying to UPDATE the Customer ID and also using it with the same
value in your WHERE clause. a statement
like UPDATE table SET colA = X WHERE colA = X will exhibit the behavior you
are observing

 

[Steve B.]

Thanks for your answer.

My final goal is to have a SqlCeDataAdapter working a "desktop"
SqlDataAdapter. That's why I used the same code than the one generated by
Visual Studio for Sql 2000 on a desktop application, than converted to sqlce
(anly few differences to make the code compiling).

Your answers make me ask another question:
What have I to do with a modified data adapter to actually update the DB? I
start to think I will have to iterate through each rows in my dataset, check
the rowstate property than manually call the insert/update or delete
command --> more time expensive.

Thanks

 

[Darren Shaffer]

With SQL Server CE, once you fill an adapter from an underlying table,
there is no automatic monitoring/management of underlying changes
to the table. The thinking here is that it's single-connection-only to
the database, so your app should not really be able to create an
integrity/dirty write problem. You can certainly modify rows in the
datatable that the adapter produces and then call Update, but my experience
is that it is better to use SqlCeDataAdapter fill and then manage and
write changes back to the table with code. The only time I would apply a
dataset directly back to a table using the adapter would be if the table
were empty.

This improves greatly in SQLMobile, coming later this year.

 

[Steve B.]

Windows mobile will be released too late for our project :'(

Integrity problem should occurs because of mecanisms we are using.
We use Sql merge replication to synchronize with central office, and we use
a local cache dataset used to keep in memory most used data. But I suppose
setting a dirty bit when replication fires won't be an issue.

Thanks for your answers.
Steve