Working with multiple tables in a DATASET

[SweetoSyed]

Hi,
I wonder if its possible to query the tables in a dataset same as we do with the database tables!
I have loaded my dataset with almost 10 tables and now want to extract data from four of those tables to display everything in a datagrid!
But i couldn't find a way as how easily it can be done?
In other words i was just trying to avoid the SQL JOINS ... so i opted for dataset manipulations..

Also, If i have populated my datagrid with three or more tables how can i update data in all three if some changes occur in datagrid?

any help in this regard will be appreciated!!!

Thanks

 

[cjard]

Hi,
I wonder if its possible to query the tables in a dataset same as we do with the database tables!
I have loaded my dataset with almost 10 tables and now want to extract data from four of those tables to display everything in a datagrid!
But i couldn't find a way as how easily it can be done?
In other words i was just trying to avoid the SQL JOINS ... so i opted for dataset manipulations..

If Microsopft poured significant development effort into DataSets and turned them into an embedded relational database engine then:

There would be no need for SQL Server to exist
Apps that used Datasets would be very fat
Visual Studio would cost thousands

Data is kept in databases, which have had millions of dollars of investment to ensure they join data very quickly. Datasets are for temporary storage of data in order to show on screen, maybe edit and send back. They arent particularly good or quick at anything outside of this mandate. The main reason why they dont do joins is because they shouldnt. Use SQL to join data.

Also, If i have populated my datagrid with three or more tables how can i update data in all three if some changes occur in datagrid?

Well, you can write an updateable view, or you can use stored procedures for the update of information, or you can just create 3 seprate update queries on the relevant table adapters, and iterate the grid rows yourself, updating the data.
[/QUOTE]

 

[SweetoSyed]

Re: Can you guide me a bit about joins

Hi, thanks for your response! Can you please guide me about retreiving data from these four tables

students: stuid, classID, sectionID, termID, sessionID, stuName, other fields
Classes: classID, className
Sections: sectionID, sectionName
Terms: termID, termName
Sessions: sessionID, sessionName

NOw if i want to extract data with a single query in somewhat this form

stuName, className, sectionName, termName, sessionName

I'd grateful for your help

 

[cjard]

You are only retrieving and updating one table (STUDENT). The other 4 are lookup tables.

Read the following sections of teh DW2 link in my sig:
Creating a Simple Data App
Displaying Data on a Form
Creating a Lookup Table

That should get you in the right track but if youre lost then you will:

In the datasources window, set the following columns of the STUDENTS table to be ComboBox type, not Textbox type: SessionID, TermID, SectionID, ClassesID
Set the main table to details mode
Drag the main table (STUDENTS) to the form
Many controls appear
4 Combos appear for the IDs
Expand the databinding tab of each and move the binding (Cut paste) from .Text, to .Selected value
Next, assign the .DataSource of each combo, to the relevant table (SessionIDCombo has a datasource of the Sessions table)
Next, assign the .ValueMember to be the xxxID column in the xxx Table, where xxx is the relevant combo
Then assign the .DisplayMember to the xxxName column in the xxx Table where xxx is the relevant combo


You end up with 4 combos obeying the following pattern:

xxxCombo .SelectedValue BINDING points to Students.xxxID column
xxxCombo.DataSource = xxxBindingSource
xxxBindingSource.DataSource = theDataSet.xxxTable
xxxCombo.DisplayMember = xxxName
xxxCombo.ValueMember = xxxID


The combo is responsible for reading the ID out of students table, looking it up in the lookup table, showing the friendly name for that ID. If the user changes the combi the combo is responsible for looking up the id of what the user picked, and sending that ID into the students table


Only ever the students table is written. The lookup tables are maintained separately. If none of this makes sense, read the DW2 links mentioned

 

[SweetoSyed]

Thank you Cjard!
Itz helpful and i am also looking at the DW2 section ..

looking forward to learn more from you in future~