Solved

C#/ADO Relations between three tables

Posted on 2002-07-01
6
538 Views
Last Modified: 2012-05-04
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
Comment
Question by:mebarron
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 22

Expert Comment

by:CJ_S
ID: 7121888
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
 

Author Comment

by:mebarron
ID: 7121899
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
 
LVL 22

Expert Comment

by:CJ_S
ID: 7121907
Just select both columns in design time and make it a primary key (btw, what kind of database do you have?)
0
Technology Partners: 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!

 

Author Comment

by:mebarron
ID: 7121922
Thanks, I am using SQL Server 2000
0
 
LVL 22

Accepted Solution

by:
CJ_S earned 100 total points
ID: 7123014
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
 
LVL 20

Expert Comment

by:TheAvenger
ID: 9485177
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question