mebarron
asked on
C#/ADO Relations between three tables
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
Project_ID
Project_Name
Detail Table #2
Project_ID (FKey)
User_ID (FKey)
User Table #3
User_ID
User_Name
User_Phone
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"
and "Table2-Table3"
////////////////////////// ////////// //////
myConnection = new
System.Data.SqlClient.SqlC onnection( connection String);
myConnection.Open( );
// create the data set
myDataSet = new System.Data.DataSet( );
myDataSet.CaseSensitive=tr ue;
// set up the command and DataSet command for the first table
myCommand = new System.Data.SqlClient.SqlC ommand( );
myCommand.Connection=myCon nection;
myCommand.CommandText = "Select * from Project";//customers
DataAdapter = new System.Data.SqlClient.SqlD ataAdapter ( );
DataAdapter.SelectCommand= myCommand;
DataAdapter.TableMappings. Add("Table ","Project ");//"Cust omers"
DataAdapter.Fill(myDataSet );
// set up the command and DataSet command for the second table
myCommand2 = new System.Data.SqlClient.SqlC ommand( );
DataAdapter2 = new System.Data.SqlClient.SqlD ataAdapter ( );
myCommand2.Connection = myConnection;
myCommand2.CommandText = "SELECT * from Detail";
DataAdapter2.SelectCommand = myCommand2;
DataAdapter2.TableMappings .Add ("Table", "Detail");//"Orders"
DataAdapter2.Fill(myDataSe t);
// set up the command and DataSet command for the third table
myCommand3 = new System.Data.SqlClient.SqlC ommand( );
DataAdapter3 = new System.Data.SqlClient.SqlD ataAdapter ( );
myCommand3.Connection = myConnection;
myCommand3.CommandText = "SELECT * from Users";
DataAdapter3.SelectCommand = myCommand3;
DataAdapter3.TableMappings .Add ("Table", "Users");//"Orders"
DataAdapter3.Fill(myDataSe t);
// establish the relationship between the tables
System.Data.DataRelation dataRelation;
System.Data.DataColumn dataColumn1;
System.Data.DataColumn dataColumn2;
dataColumn1 =
myDataSet.Tables["Project" ].Columns[ "Project_I D"];
dataColumn2 =
myDataSet.Tables["Detail"] .Columns[" PRoject_ID "];
dataRelation =
new System.Data.DataRelation(
"ProjectToDetail",
dataColumn1,
dataColumn2);
///2nd relation
dataColumn1 =
myDataSet.Tables["Detail"] .Columns[" User_ID"];
dataColumn2 =
myDataSet.Tables["users"]. Columns["u ser_ID"];
dataRelation =
new System.Data.DataRelation(
"ProjectToDetail",
dataColumn1,
dataColumn2);
// add the relation object to the data set
myDataSet.Relations.Add(da taRelation );
// set up the grid's view and member data and display it
DataViewManager DataSetView =
myDataSet.DefaultViewManag er;
customerDataGrid.DataSourc e = DataSetView;
customerDataGrid.DataMembe r= "Project";
Table #1 has a Foreing Key for the Table #2.
My problem is more complicated.
A DB has three tables
Project Table #1
Project_ID
Project_Name
Detail Table #2
Project_ID (FKey)
User_ID (FKey)
User Table #3
User_ID
User_Name
User_Phone
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"
and "Table2-Table3"
//////////////////////////
myConnection = new
System.Data.SqlClient.SqlC
myConnection.Open( );
// create the data set
myDataSet = new System.Data.DataSet( );
myDataSet.CaseSensitive=tr
// set up the command and DataSet command for the first table
myCommand = new System.Data.SqlClient.SqlC
myCommand.Connection=myCon
myCommand.CommandText = "Select * from Project";//customers
DataAdapter = new System.Data.SqlClient.SqlD
DataAdapter.SelectCommand=
DataAdapter.TableMappings.
DataAdapter.Fill(myDataSet
// set up the command and DataSet command for the second table
myCommand2 = new System.Data.SqlClient.SqlC
DataAdapter2 = new System.Data.SqlClient.SqlD
myCommand2.Connection = myConnection;
myCommand2.CommandText = "SELECT * from Detail";
DataAdapter2.SelectCommand
DataAdapter2.TableMappings
DataAdapter2.Fill(myDataSe
// set up the command and DataSet command for the third table
myCommand3 = new System.Data.SqlClient.SqlC
DataAdapter3 = new System.Data.SqlClient.SqlD
myCommand3.Connection = myConnection;
myCommand3.CommandText = "SELECT * from Users";
DataAdapter3.SelectCommand
DataAdapter3.TableMappings
DataAdapter3.Fill(myDataSe
// establish the relationship between the tables
System.Data.DataRelation dataRelation;
System.Data.DataColumn dataColumn1;
System.Data.DataColumn dataColumn2;
dataColumn1 =
myDataSet.Tables["Project"
dataColumn2 =
myDataSet.Tables["Detail"]
dataRelation =
new System.Data.DataRelation(
"ProjectToDetail",
dataColumn1,
dataColumn2);
///2nd relation
dataColumn1 =
myDataSet.Tables["Detail"]
dataColumn2 =
myDataSet.Tables["users"].
dataRelation =
new System.Data.DataRelation(
"ProjectToDetail",
dataColumn1,
dataColumn2);
// add the relation object to the data set
myDataSet.Relations.Add(da
// set up the grid's view and member data and display it
DataViewManager DataSetView =
myDataSet.DefaultViewManag
customerDataGrid.DataSourc
customerDataGrid.DataMembe
ASKER
I agree that they together are a sort of primary key
but I do not know how to "setting both foreign keys in table2 as a primary key (because both together is a primary key).
" Thanks for your reply.
but I do not know how to "setting both foreign keys in table2 as a primary key (because both together is a primary key).
" Thanks for your reply.
Just select both columns in design time and make it a primary key (btw, what kind of database do you have?)
ASKER
Thanks, I am using SQL Server 2000
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
PAQ/No Refund
Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
TheAvenger
EE Cleanup Volunteer
I will leave a recommendation in the Cleanup topic area that this question is:
PAQ/No Refund
Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
TheAvenger
EE Cleanup Volunteer
Have you tried setting both foreign keys in table2 as a primary key (because both together is a primary key).
CJ