Linking Datagridviews...

[cboshdave]

I have 3, 1 to Many related tables. Let's say Customers to Orders and then Orders to Item Details. I have a window that displays all three. When I select a customer, I want the orders for that customer to all display and the active order to display all items.

When I select a different Customer, the grids all update. I have it working, but it is a bit weird. There must be a more elegant example of how to accomplish this? I am happy to read more. I just can't seem to find a good example of this idea.

The following is an example. When dgvCustomers grid selector changes, it calls a getOrders with the new customer id. Generally this works, but when I added a third link (to items for my example), I get "beeps". It is still working and updating, but I can't find the error.

private void dgvCustomers_SelectionChanged(object sender, EventArgs e)
        {
            try            
            {
                getOrders(dgvCustomer.CurrentRow.Cells["CustomerID"].Value.ToString());
            }
            catch (Exception ex)
            {
                //Nothing
            }
         }

Then, getOrders updates as follows. And the exact same link to table 3.

        private void getOrders(string sCustID)        
        {
            string sSQLOrders = "SELECT * from Orders Where CustomerFK = " + sCustID;


            DataClass ProvCurrentDB = new DataClass(sDBConnection);
            ProvCurrentDB.OpenConnection();


            //Load the data to the screen.
            SqlDataAdapter sda = ProvCurrentDB.ExecuteAdapter(sSQLOrders ); //
            DataTable dt = new DataTable();
            sda.Fill(dt);


            //bsSQLData.DataSource = dsSQLData.Tables["Orders"];


            dgvOrders.DataSource = dt;
            dgvOrders.Refresh();
            int iOrderCount = dgvOrders.Rows.Count - 1; //subtract one to not count the "add" row.


            txtBoxOrderCount.Text = iOrderCount .ToString("N0");
            Application.DoEvents();
            this.Refresh();


        }

 

[jmcilhinney]

The simplest way to do this is to retrieve all the data upfront and let the data-binding handle the filtering. The main reason not to do that would be that there's so much data that it's an issue to get it all in one go. If you can get all the data upfront then check out this thread of mine on master/detail data-binding:

Master/Detail (Parent/Child) Data-binding

It's written for VB but the principles are exactly the same in C# and the code isn't much different either.