Multiple tables

[Ragowit]

Hello!

I'm new to this ADO thing so I have some questions. I'm using Visual Studio 2005 (.NET 2.0) and Oracle 10.

I understand how the OracleDataAdapter.Fill and .Update works for single tables (SELECT * FROM one_table). But how does it work if I do a more complex commandtext?


Like:
SELECT e.FirstName, o.ShipCity FROM Employees e, Orders o WHERE e.EmployeeID = o.EmployeeID ORDER BY e.FirstName

If I do a Fill() on that one, I get a result with FirstName from Employee and ShipCity from Orders. Like a charm.

But if I do an Update(), how would the code know that changes to FirstName goes into Employee and changes to ShipCity goes into Orders, and at the same time check so it's the right EmployeeID?


So, in other words what I want to achieve is:
I want to select a bunch of information from different tables. Show this to the user in a grid where the user can alter this information and then save the changes that the user have made.

[TheCPUWizard]

You have to split it up.

Typically there are two approaches (and the best solutions use BOTH)

1) ALWAYS use stored procedures. This provides additional security and encapsulation.

2) KEEP the data in separate tables (use a DataSet and DataRelation). If you don't "Combine" then you don't need to "Split.

[Ragowit]

Thanks TheCPUWizard for your answer.

For starters so do I only try approach #2 at the moment.

In my DataSet, I have now created two tables and a relation between them.
Further more so do I now use two DataAdapters, one to fill the Employees table and the other one to fill the Orders table in my DataSet. Seems to work like a charm.

But how do I do now to get the information out from my DataSet into a grid so it looks like I have done this query?

SELECT e.FirstName, o.ShipCity FROM Employees e, Orders o WHERE e.EmployeeID = o.EmployeeID ORDER BY e.FirstName

EDIT:
I can also mention that I have tried with a DataGrid, but I got "+"-signs that I had to press to view the Order-data for just that Employee.
What I want to achieve is all columns visible just like if I have done the query above, and still be able to edit values and save stuff where it belong.

[vuyiswam]

You are looking for exactly i wrote an Article About.

See this , the Difference its onyl that am using SQL

http://www.codeproject.com/KB/cs/N-Tier22.aspx

Hope it helps