locked
How do I make realtional tables work in a DataGridView RRS feed

  • Question

  • I used to know all of this back in the day. Let's say I have the traditional Customer and Products tables where CustID is the primary key in the customer table and CustID is just a key in the Products table. I set the relationship with Customer as the master and Products as forieg with a simple relation.

    Now I have two datagridviews on the same form. How do I make the Products view show only the related items in the Product view when I choose a different record in the Customer view?

    • Moved by CoolDadTx Friday, February 19, 2010 7:22 PM Winforms related (From:Visual C# General)
    Friday, February 19, 2010 3:27 PM

Answers

  • I don't know where all of you learned relational design but I dragged the answer up from my old dBase days. When you have the two bindingsource's you set the filter on the foriegn key and all works well

    foriegBindingSource.Filter = "custID = 'somevalue'"

    Then the gridview of foriegn (or child) rows shows only what is filtered. When you change rows in the Primary gridview, you change the filter to reflect the new value.
    Saturday, February 20, 2010 3:07 AM

All replies

  • I used to know all of this back in the day. Let's say I have the traditional Customer and Products tables where CustID is the primary key in the customer table and CustID is just a key in the Products table. I set the relationship with Customer as the master and Products as forieg with a simple relation.

    Now I have two datagridviews on the same form. How do I make the Products view show only the related items in the Product view when I choose a different record in the Customer view?


    use bindingSource and create a join
     String twotables= "Select * From Table1 LEFT OUTER JOIN Table2 ON Table1.ID = Table2.IDMe"
    Just Be Humble Malange!
    Friday, February 19, 2010 3:34 PM
  • Umm, I don't think a Join is the way to work with relational tables the way I'm asiking. Also, I'm using DataSet's and TableAdapters, etc.., not straight SQL. A join would be good for a single view. I want to be able to view Customers in a grid and thier associated (related) Products in another view right next to them.
    Friday, February 19, 2010 3:46 PM
  • Umm, I don't think a Join is the way to work with relational tables the way I'm asiking. Also, I'm using DataSet's and TableAdapters, etc.., not straight SQL. A join would be good for a single view. I want to be able to view Customers in a grid and thier associated (related) Products in another view right next to them.

    you can do that way, use bindingsource and remove whatver you won't and bind both datgrid with the same bindingsource and removes the coulmn that u dont need.

    or you have to use select, one by one....
    Just Be Humble Malange!
    Friday, February 19, 2010 3:56 PM
  • Then what good is a realtionship? Joins are expensive and I need to do this quickly.
    • Proposed as answer by JohnGrove Friday, February 19, 2010 8:40 PM
    Friday, February 19, 2010 4:45 PM
  • Bind both datagrids to the same dataset.
    Set the DataMember of the master grid to the master table and the DataMember of the child grid to the child table via the master table and the relation:

    masterGrid.DataSource = myDataSet;
    masterGrid.DataMember = "MasterTable";
    childGrid.DataSource = myDataSet;
    childGrid.DataMember = "MasterTable.Relation";

    Friday, February 19, 2010 7:14 PM
  • Hello,

    Traditionally, you would need 2 query's.

    Query 1: Select * from Customers -> Populates Main Grid
    Query 2: Select * from Products where CustID = DataGridView.SelectedCells(0).Value ->Populates Child Grid on row change event from Main Grid.

    Adam

    Dibble and dabble but please don't babble.
    Friday, February 19, 2010 8:36 PM
  • Then what good is a realtionship? Joins are expensive and I need to do this quickly.

    I accidently clicked "propose as Answer instead of "Quote".

    How many rows are in your tables?

    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Friday, February 19, 2010 8:40 PM
  • I don't know where all of you learned relational design but I dragged the answer up from my old dBase days. When you have the two bindingsource's you set the filter on the foriegn key and all works well

    foriegBindingSource.Filter = "custID = 'somevalue'"

    Then the gridview of foriegn (or child) rows shows only what is filtered. When you change rows in the Primary gridview, you change the filter to reflect the new value.
    Saturday, February 20, 2010 3:07 AM
  • 600,000 rows in the table
    Saturday, February 20, 2010 3:51 AM
  • I have done joins with millions of rows. (Not with a DataSet but "SELECT INTO").
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Saturday, February 20, 2010 5:38 AM
  • I don't know where all of you learned relational design but I dragged the answer up from my old dBase days. When you have the two bindingsource's you set the filter on the foriegn key and all works well

    foriegBindingSource.Filter = "custID = 'somevalue'"

    Then the gridview of foriegn (or child) rows shows only what is filtered. When you change rows in the Primary gridview, you change the filter to reflect the new value.
    I don't see how your solution is linked to relational design. You use your child table as though there was no parent table.
    I don't see what's the point in making a relation between the two tables if you don't use it.
    Sunday, February 21, 2010 3:33 AM