dataadapter.Update() multiple table update strategies

2 Nov 2006 1:00 AM

jarb

Im not sure how to proceed on this one...

I have a dataset with one table being populated by a complex join from several
tables. Basically a fancy SELECT statement. Designer doesn't support generating
a dataset from a query like this (right?) so I hand created the dataset and the
SELECT statement. Now dataadapter.Fill() works fine. Its time to get Update()
working. Since I need to put values back into multiple tables I need to do
several SQL UPDATEs. I tried crafting a BEGIN END; block with all the needed SQL
(im using Oracle). But this doesn't return an affected row count so a
concurrency exception gets thrown. Seems like I have the wrong approach.

Am I supposed to not do SQL joins, but instead pull my whole database schema, or
at least all the necessary tables, locally into the dataset and then work with
it using the dataset paradigm, relations, and constraints? I really don't like
this. I don't want an in-memory wanna-be database. I just want a lightweight
cache to save network bandwidth and allow for form control binding.

Or should I create multiple data adapters for each SQL table UPDATE I need to
do? Or just create multiple update command objects and then assign them to the
dataadapter and Update() the dataset one command at a time? But im going to need
them all to be part of a transaction.

Maybe the simplest thing would just be to create yet another view in Oracle that
makes everything look like a single table. Too bad the db developer is swamped
with work.

TIA

2 Nov 2006 5:11 AM

Cor Ligthert [MVP]

Jarb,

You can do it with one dataadapter.
This returns a table to your database.

You have to create for the update in your database your own procedure (if
that is a SP is not important).

That procedure and the dataset (table information) needs than all the
constraints that are necessary. You need to update the tables than ofcourse
one by one in your SQL transact procedure. In other words you have to
extract from the data and parameters that you return the deletes, the
insert, the updates code for your SQL transact procedure. (A hug job, which
I never did).

Probably you cannot get any generator for that. (I don't know if LLBLGen
does this). But you can try them. http://www.llblgen.com

I hope this helps,

Cor

Show quote

"jarb" <no@spam.net> schreef in bericht
news:unb2h.209$3K7.53@newsfe13.phx...
> Im not sure how to proceed on this one...
>
> I have a dataset with one table being populated by a complex join from
> several tables. Basically a fancy SELECT statement. Designer doesn't
> support generating a dataset from a query like this (right?) so I hand
> created the dataset and the SELECT statement. Now dataadapter.Fill() works
> fine. Its time to get Update() working. Since I need to put values back
> into multiple tables I need to do several SQL UPDATEs. I tried crafting a
> BEGIN END; block with all the needed SQL (im using Oracle). But this
> doesn't return an affected row count so a concurrency exception gets
> thrown. Seems like I have the wrong approach.
>
> Am I supposed to not do SQL joins, but instead pull my whole database
> schema, or at least all the necessary tables, locally into the dataset and
> then work with it using the dataset paradigm, relations, and constraints?
> I really don't like this. I don't want an in-memory wanna-be database. I
> just want a lightweight cache to save network bandwidth and allow for form
> control binding.
>
> Or should I create multiple data adapters for each SQL table UPDATE I need
> to do? Or just create multiple update command objects and then assign them
> to the dataadapter and Update() the dataset one command at a time? But im
> going to need them all to be part of a transaction.
>
> Maybe the simplest thing would just be to create yet another view in
> Oracle that makes everything look like a single table. Too bad the db
> developer is swamped with work.
>
> TIA

2 Nov 2006 5:50 AM

Earl

The problem is not with the dataset, it's with how you are submitting your
updates. I am battling a very similar issue, as it appears that Microsoft
generally does not support a database that has structures more complex than
parent-child, so many-to-many or other complex db scenarios have to be
hand-crafted. Most books I've read just blow right past the subject,
although I've heard that Sahil Malik's "Pro ADO" book discusses it. Sceppa's
book discusses ADO.Net transactions but other than a brief mention there's
not much on HOW to submit a massive update to a transaction sproc.

One thing, you'll never accompish what you are trying to do with a join.
Each table has to be handled separately, even if you are going to submit the
updates as a transaction. Something I'm playing around with is to create all
the individual table parameters with the DACW and then stitch them together
for the transaction (you can dump the visual adapters after you copy out the
params).

Show quote

"jarb" <no@spam.net> wrote in message news:unb2h.209$3K7.53@newsfe13.phx...
> Im not sure how to proceed on this one...
>
> I have a dataset with one table being populated by a complex join from
> several tables. Basically a fancy SELECT statement. Designer doesn't
> support generating a dataset from a query like this (right?) so I hand
> created the dataset and the SELECT statement. Now dataadapter.Fill() works
> fine. Its time to get Update() working. Since I need to put values back
> into multiple tables I need to do several SQL UPDATEs. I tried crafting a
> BEGIN END; block with all the needed SQL (im using Oracle). But this
> doesn't return an affected row count so a concurrency exception gets
> thrown. Seems like I have the wrong approach.
>
> Am I supposed to not do SQL joins, but instead pull my whole database
> schema, or at least all the necessary tables, locally into the dataset and
> then work with it using the dataset paradigm, relations, and constraints?
> I really don't like this. I don't want an in-memory wanna-be database. I
> just want a lightweight cache to save network bandwidth and allow for form
> control binding.
>
> Or should I create multiple data adapters for each SQL table UPDATE I need
> to do? Or just create multiple update command objects and then assign them
> to the dataadapter and Update() the dataset one command at a time? But im
> going to need them all to be part of a transaction.
>
> Maybe the simplest thing would just be to create yet another view in
> Oracle that makes everything look like a single table. Too bad the db
> developer is swamped with work.
>
> TIA

2 Nov 2006 8:57 AM

Miha Markic [MVP C#]

Hi there,

I think you have roughly speaking three options:
- create a stored procedure that does all updates for you and call it from
within adapter
- put all updates in adapters.commandtext property: UPDATE SomeTable SET
....;UPDATE AnotherTable SET ...; ... I guess you might have problems with
checking concurrency exceptions at this point (you can ignore them I guess)
- run same set of rows on more adapter (adapter per table)

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Show quote

"jarb" <no@spam.net> wrote in message news:unb2h.209$3K7.53@newsfe13.phx...
> Im not sure how to proceed on this one...
>
> I have a dataset with one table being populated by a complex join from
> several tables. Basically a fancy SELECT statement. Designer doesn't
> support generating a dataset from a query like this (right?) so I hand
> created the dataset and the SELECT statement. Now dataadapter.Fill() works
> fine. Its time to get Update() working. Since I need to put values back
> into multiple tables I need to do several SQL UPDATEs. I tried crafting a
> BEGIN END; block with all the needed SQL (im using Oracle). But this
> doesn't return an affected row count so a concurrency exception gets
> thrown. Seems like I have the wrong approach.
>
> Am I supposed to not do SQL joins, but instead pull my whole database
> schema, or at least all the necessary tables, locally into the dataset and
> then work with it using the dataset paradigm, relations, and constraints?
> I really don't like this. I don't want an in-memory wanna-be database. I
> just want a lightweight cache to save network bandwidth and allow for form
> control binding.
>
> Or should I create multiple data adapters for each SQL table UPDATE I need
> to do? Or just create multiple update command objects and then assign them
> to the dataadapter and Update() the dataset one command at a time? But im
> going to need them all to be part of a transaction.
>
> Maybe the simplest thing would just be to create yet another view in
> Oracle that makes everything look like a single table. Too bad the db
> developer is swamped with work.
>
> TIA