 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / Languages / C# / March 2008Typed datasets and joins
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 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 > Hi, > [quoted text clipped - 5 lines] > Thanks for any advice, > RSH 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 > 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 [quoted text clipped - 23 lines] >> Thanks for any advice, >> RSH 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. > Andy, > [quoted text clipped - 42 lines] >>> Thanks for any advice, >>> RSH 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
Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.