Typed datasets and joins

[Ron]

Hi,

How is the best way to deal with typed datasets which need to have joins,
and having the adapter manage the Updates and Insert Commands. Obviously
the designer wont generate the commands when the query contains a Join so
I'm a bit stuck here.

Thanks for any advice,
RSH

 

[asellon]

The way I do this is create a join dataset and us it only in selects. No
updates, deletes or inserts. In the join query, include the primary keys of
each table in the join. Then I create a dataset for each table in the join
and use those only for the writing operations.

so something like this

Query join dataset
find row I want to alter
get the primary key(s) from the row for the table(s) I want to update
fill dataset(s) with those row(s) and make the alterations
Call DataSet.Update()

this way, you use the join query as a lookup table and then you use typed
datasets matching the SQL tables for information updates.

Andy

 

[Ron]

Andy,

Im almost following you. If it wouldn't be too much to ask could you go
into a little more detail?

My requirements are forcing me to use strictly the VS designer to setup my
Datasets/Adapters...and right now I'm facing the issue of binding many
controls to different Datasources derived from a single join query...I am
having an issue where if a record exists in table1 but not in Table2 I need
to perform an insert rather than an update. Since all of the controls are
linked to the datasets using complex databinding I do know that information
at runtime. But Im trying to dynamically handle all the updates and inserts
dynamically, and therin lies my problem.

Any additional advice would be greatly appreciated.

Thanks,
Ron

 

[asellon]

your join dataset is feeding controls? then using the VS designer, create a
new dataset and add the tables you include in your join to it. This dataset
will be only the tables and by doing this, you will get the update, delete,
insert logic for free.

then based on whatever your criteria is for changing a row in the database,
grab the primary key you need off the join dataset and open up your
individual table dataset and load that record into it by calling fill on
that dataset for that key. Then make your changes or whatever. then call
update.

now you are done with the individual table dataset and you go back to
continueing your usage of the join dataset.

make sense?

what do you mean dynamic updates? you don't mean hand writing the sql
statments do you?

you can use the VS designer to create these tables for you just like you
would anyway and get all the functionality you need.

the hangup for most people is they think "why would I want to have a join
table dataset and then have a dataset with the individual tables" seems to
remove the efficiency

but it will be the only way you can have VS write all the table updating
logic for you and use typed datasets.

 

[Cor Ligthert[MVP]]

Ron,

As you are talking about a typed dataset I get the idea that you are talking
about the TableAdapter, which is as it says about a table on the server (not
a resultset adapter, which does not exist). That the table adapter creates
internal a DataTable from a resultset has in my idea nothing to do with
that.

As it is about its parent the DBDataAdapter, then you can create probably an
SQL insert transact that can handle it, but don't assume you get it for
free.

Cor