SQLCE, DataAdaptors, MultipleTables, Updates and Inserts

[Paul [Paradise Solutions]]

Hey all

I know the above subjects have all been covered one way or another, but
not always all together and fewer still that answer the question.

Usual situation - one *SQLCE* database two tables requiring updates,
implimenting a 'save as you go' entry system.

One main question is that in some posts I've read about using multiple
update queries with the DataAdapter.Update method, but as SQLCE doesn't
allow batched commands I couldn't see how this could be done.

I was also thwarted when trying to use two DataAdapters when I realised
that the first Adapter locks the database.

Using one Adapter and populating two datatables is easy enough, but then
you're back to how to get the database to update if there's inly one
command behind the update and insert commands of the update method.

Any input apprieciated.


Paul

 

[Jon Skeet [C# MVP]]

I know the above subjects have all been covered one way or another, but
not always all together and fewer still that answer the question.

Usual situation - one *SQLCE* database two tables requiring updates,
implimenting a 'save as you go' entry system.

One main question is that in some posts I've read about using multiple
update queries with the DataAdapter.Update method, but as SQLCE doesn't
allow batched commands I couldn't see how this could be done.

I was also thwarted when trying to use two DataAdapters when I realised
that the first Adapter locks the database.

Using one Adapter and populating two datatables is easy enough, but then
you're back to how to get the database to update if there's inly one
command behind the update and insert commands of the update method.

Any input apprieciated.

Do one update and then the other, within a transaction if you want to.

Not sure what you mean by saying that "the first adapter locks the
database" - I've never seen that, unless you're trying to use the same
connection from two different threads at the same time.

 

[Paul [Paradise Solutions]]

Do one update and then the other, within a transaction if you want to.

Not sure what you mean by saying that "the first adapter locks the
database" - I've never seen that, unless you're trying to use the same
connection from two different threads at the same time.

How you mean? As in (don't mean to dumb it down - just getting me head
round it clearly):

Set multitable select query
Fill tables
Do table inserts/updates
Set Insert/Update commands on adapter for table1
Update
Set Insert/Update commands on adapter for table2
Update



Paul

 

[Paul [Paradise Solutions]]

Do one update and then the other, within a transaction if you want to.

Not sure what you mean by saying that "the first adapter locks the
database" - I've never seen that, unless you're trying to use the same
connection from two different threads at the same time.

My First reply to this seemed to vanish, so posting again:


How you mean? As in (don't mean to dumb it down - just getting me head
round it clearly):

Set multitable select query
Fill tables
Do table inserts/updates
Set Insert/Update commands on adapter for table1
Update
Set Insert/Update commands on adapter for table2
Update



Paul

 

[Jon Skeet [C# MVP]]

My First reply to this seemed to vanish, so posting again:

How you mean? As in (don't mean to dumb it down - just getting me head
round it clearly):

Set multitable select query
Fill tables
Do table inserts/updates
Set Insert/Update commands on adapter for table1
Update
Set Insert/Update commands on adapter for table2
Update

Well, I'd suggest:

Set up adapter 1 and commands
Set up adapter 2 and commands

then later:

Fill tables
Modify tables
Open connection
Begin transaction
Set connection and transaction for commands in both adapters
Call update in adapter 1
Call update in adapter 2
Commit transaction
Close connection

 

[Paul [Paradise Solutions]]

Well, I'd suggest:

Set up adapter 1 and commands
Set up adapter 2 and commands

then later:

Fill tables
Modify tables
Open connection
Begin transaction
Set connection and transaction for commands in both adapters
Call update in adapter 1
Call update in adapter 2
Commit transaction
Close connection

I did try using the two adapters, but as I said, as soon as one adapter
is used to fill a table, the fill on the other fails due to a locked DB
(adapter setup works ok, just using them causes the issue) - I'm running
in shared subs if that makes any diference, no additional threading.

 

[Paul [Paradise Solutions]]

Well, I'd suggest:

Set up adapter 1 and commands
Set up adapter 2 and commands

then later:

Fill tables
Modify tables
Open connection
Begin transaction
Set connection and transaction for commands in both adapters
Call update in adapter 1
Call update in adapter 2
Commit transaction
Close connection

Please disregard last post - as per ususal two days of staring at my
code and I find the problem ~after~ I click send.. always the same...

Many thanks for your input on the solution though.


Paul