Solved

C#/ADO Relations between three tables

Posted on 2002-07-01
6
543 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
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.

 

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

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!

Question has a verified solution.

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

This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

726 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