C#/ADO Relations between three tables
Posted on 2002-07-01
There are many C#/ADO examples showing relations between two tables.
Table #1 has a Foreing Key for the Table #2.
My problem is more complicated.
A DB has three tables
Project Table #1
Detail Table #2
User Table #3
When a User is assigned to a Project an entry is made in the
Detail Table. There is a One to Many relation between User and Projects
I want to display all fields the Project Table in a DataGrid.
I want to establish a relation between Project and User , such that
when the user click on the plus-sign next to a projects name
he will see a Relation Label such as "ProjectToUser" appears and
when the user click on that tag, the users for that project are displayed.
I have used a DataGrid to relate two tables where a Foreign Key in Table one
is Primary in the Second Table. This involves three tables and I am puzzled
about how to proceed. It seems to me that there is a relation between
Table #1 and Table #2 due to Project_ID, lets call it "ProjectDetail"
There is a relation between Table #2 and Table#3 due to User_ID.
The problem appears to be the Detail Table which , sorry if this sounds
stupid, has two Foreign Keys and no Primary Key.
The following SQL gives me the recordset that I want displayed after clicking
on the relation_tab "ProjectToUser" next to the project corresponding to PID=109.
myCommand2.CommandText = "SELECT u.User_ID, d.Project_id, u.First_Name, u.Last_Name,"
+ "u.User_name, u.User_Phone "
+ "FROM Users u INNER JOIN "
+ " Detail d ON u.User_ID = d.User_id "
+ "Where Project_ID = 109";
Can you help me with some pointer, perhaps point me to an example which uses three tables
instead of two.
Below is a failed attempt to build the relations needed. I believe the two relations
below would result in two Relation-Tags appearing when the plus-sign is click on the
DataGrid. I want a single Tag "Table1-Table3' and not two RelationTags Table1-Table2"
myConnection = new
// create the data set
myDataSet = new System.Data.DataSet( );
// set up the command and DataSet command for the first table
myCommand = new System.Data.SqlClient.SqlCommand( );
myCommand.CommandText = "Select * from Project";//customers
DataAdapter = new System.Data.SqlClient.SqlDataAdapter( );
// set up the command and DataSet command for the second table
myCommand2 = new System.Data.SqlClient.SqlCommand( );
DataAdapter2 = new System.Data.SqlClient.SqlDataAdapter( );
myCommand2.Connection = myConnection;
myCommand2.CommandText = "SELECT * from Detail";
DataAdapter2.SelectCommand = myCommand2;
DataAdapter2.TableMappings.Add ("Table", "Detail");//"Orders"
// set up the command and DataSet command for the third table
myCommand3 = new System.Data.SqlClient.SqlCommand( );
DataAdapter3 = new System.Data.SqlClient.SqlDataAdapter( );
myCommand3.Connection = myConnection;
myCommand3.CommandText = "SELECT * from Users";
DataAdapter3.SelectCommand = myCommand3;
DataAdapter3.TableMappings.Add ("Table", "Users");//"Orders"
// establish the relationship between the tables
// add the relation object to the data set
// set up the grid's view and member data and display it
DataViewManager DataSetView =
customerDataGrid.DataSource = DataSetView;