• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 552
  • Last Modified:

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.SqlConnection(connectionString);
        myConnection.Open(  );

        // create the data set
        myDataSet = new System.Data.DataSet(  );
        myDataSet.CaseSensitive=true;

        // set up the command and DataSet command for the first table
        myCommand = new System.Data.SqlClient.SqlCommand(  );
        myCommand.Connection=myConnection;
        myCommand.CommandText = "Select * from Project";//customers
        DataAdapter = new System.Data.SqlClient.SqlDataAdapter(  );
        DataAdapter.SelectCommand= myCommand;
        DataAdapter.TableMappings.Add("Table","Project");//"Customers"
        DataAdapter.Fill(myDataSet);

        // 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"
        DataAdapter2.Fill(myDataSet);

                  // 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"
                  DataAdapter3.Fill(myDataSet);


        // establish the relationship between the tables
        System.Data.DataRelation dataRelation;
        System.Data.DataColumn dataColumn1;
        System.Data.DataColumn dataColumn2;
        dataColumn1 =        
                  myDataSet.Tables["Project"].Columns["Project_ID"];
            
        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["user_ID"];                  
       
                  dataRelation =
                        new System.Data.DataRelation(
                        "ProjectToDetail",
                        dataColumn1,
                        dataColumn2);

        // add the relation object to the data set
        myDataSet.Relations.Add(dataRelation);

        // set up the grid's view and member data and display it
        DataViewManager DataSetView =
                  myDataSet.DefaultViewManager;
        customerDataGrid.DataSource = DataSetView;
        customerDataGrid.DataMember= "Project";            
0
mebarron
Asked:
mebarron
  • 3
  • 2
1 Solution
 
CJ_SCommented:
I understand your problem but don't know how to do it using databinding.

Have you tried setting both foreign keys in table2 as a primary key (because both together is a primary key).

CJ
0
 
mebarronAuthor Commented:
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.
0
 
CJ_SCommented:
Just select both columns in design time and make it a primary key (btw, what kind of database do you have?)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
mebarronAuthor Commented:
Thanks, I am using SQL Server 2000
0
 
CJ_SCommented:
Try
      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 "
with

dataColumn1 =        
               myDataSet.Tables["Project"].Columns["Project_ID"];
         
       dataColumn2 =
               myDataSet.Tables["Detail"].Columns["PRoject_ID"];
         
       
       dataRelation =
               new System.Data.DataRelation(
                    "ProjectToDetail",
                    dataColumn1,
                    dataColumn2);
0
 
TheAvengerCommented:
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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now