How to handle data in different tables?

I was wondering if someone could point me to a tutorial that might help me?

I have an application that I'm developing that will display a tabbed form which needs to contain data from different tables. For example, my database has a contact table with typicall information and then it also has a lead table which contains information if the contact is a sales lead. They are linked together by a common contactID column. There are a few other normalized tables also like an AlternateAddress table.

My first problem was that I could only get the binding source to connect to a particular table. This was a problem cause the data that I need to modify is normalized across a few different tables. My solution to that problem was to create a view and join all of my data from the different tables into a 'virtual table'.

My new problem now is I can't generate an update command for a view.

How do I get a binding navigator to use a binding source to navigate through my data for each contact and also be able to update changes to the datasource?

I've read some other posts about this (there's not many) and here's what I think might work. Can someone tell me if there is an easier way?

I would have my dataset with a table for each table in the database (ContactInfo, Lead, and Alternate Address) Each would have a table adapter, but the Lead and Alternate Address tables would have an additional table adapter to fillByContactID. I would have my binding source tied to my contactInfo table, and I would have to catch the position changed event and then re-fill the Lead and Alternate Address tables by the current contactID.

Would it be easier to write my own update function for using a VIEW? Someone please help. Thank you.

create a view in sql server

allow the application to update insert delete from the view

in sql server configure an instead of update,insert and delete trigger

that updates individual table behind the scene

From what I understand, I can't update to a view without writing my own update SQL command correct?

yes you can write your update statement for a view

but inside sql server it can be the instead of triggers

that update the individual base tables behind the scene

most views are not updateable. VS perceive the view as updatable

Adam,

ADO.NET 2.0 is not very apt at dealing with scenarios involving multiple tables. I know it sounds silly, but this is unfortunately the truth. LINQ to SQL and ADO.NET vNext deal with this problem out of the box.

So obviously the question is, "What can you do to deal with this problem today".

The best answer I can give you is to do some further reading into ADO.NET and try and wean yourself away from drag-drop solutions for this problem. A handwritten solution, which is definitely possible in ADO.NET 2.0 (my book covers it well in Chap 10), is your best bet in this scenario.

The "virtual table" scenario you mention - may just be a good shortcut, but you will probably have to hand-specify your own commands.

- Sahil Malik
http://blah.winsmarts.com

I found the answer I was looking for. Basically I followed this example which showed me that the way to have multiple tables on the same form is to have multiple binding sources and then hooking the Data Source of the child tables up the Parent table binding source. Also, hook the Data Member of the child tables up to the Foriegn Key in the parent table's binding source. Anyone else looking for help with this, follow this example and then look at the binding sources.

http://msdn2.microsoft.com/en-us/library/4esb49b4.aspx