parent/child grids

[Rick]

VS 2005

I' m setting up a parent/child datagridviews in a form.

I am doing a lot of this by hand coding in order to get the feel of things.
I want a change in the parent table to trigger a change in the child.

Where is the best place (if I cannot set it up to be automatic through
relationships) to catch the change in the parent to refetch the child rows?

This must be quite basic, but I can't seem to figure it out.

Thanks,

Rick

The setup is:

dataset with 2 datatables
the master is connected to a binding source to feed the form UI.
the detail is connected directly to the datagridview

The tables have their relation set (parentPK to childFK)

 

[RobinS]

You don't need to refetch the child rows manually, unless you're just a
masochist. For display purposes, two grids, one for parent and one to show
children corresponding to the selected row in the parent, try this:

You need 2 BindingSources -- one for the parent, and one for the child.
Set the BindingSource for the parent to point at the parent table.
Set the BindingSource for the child to point at the parent's BindingSource,
with the datamember being the relation between the two tables.

Here's an example using NorthWind and a strongly typed dataset, but you
could just as easily do it with your own dataset.

Dim ds as CustomersDataSet = CustomersDataSet.GetCustomers()

m_CustomersBindingSource.DataSource = ds
m_CustomersBindingSource.DataMember = "Customers"
m_CustomersGrid.DataSource = m_CustomersBindingSource

m_ChildOrdersBindingSource.DataSource = m_CustomersBindingSource
m_ChildOrdersBindingSource.DataMember = "FK_Orders_Customers"

m_OrdersGrid.DataSource = m_ChildOrdersBindingSource


Hope this helps.
Robin S.

 

[Rick]

Thanks Robin, that's what I was looking for.

Rick

 

[Rick]

Ok, I set that up, but with no results in the grid.

Perhaps this is an obvious question, but how does the parent table and
relation know what query to execute to get the child rows?

Do I need to put the child query into the parent?

Rick

 

[RobinS]

Ah. You need to populate both data tables. In my example, GetCustomers is a
method of the strongly typed dataset that fills both tables.

So what you want to pull for your children is all of the children for all
of the parents. Obviously, if you're pulling all parents, you can just pull
all children. Otherwise, you're going to have to figure out how to pull all
of the children for the parents you've seleted. You can do that with a
subquery or a join.

This fills two tables in the same dataset at one time.

Dim SQLString As String = "SELECT * FROM Customers; SELECT * FROM Orders"
Dim da As New SqlDataAdapter(SQLString, connString)
da.TableMappings.Add("Table","Customers")
da.TableMappings.Add("Table1","Orders")
Dim ds as New DataSet()
da.Fill(ds)
For each tbl as DataTable in ds.Tables
Console.WriteLine("TableName = {0}", tbl.TableName)
Next

Hope this helps.

Robin S.
-----------------------------------

 

[Rick]

Ok, but from what I can tell I am back to square one.

It seems you are populating the datatables with ALL the records from the db
and then letting the binding source filter the detail records.

If this is the case, it's probably a valid senario for a small set of
orders, but like in my case I have hundreds of thousands to millions of
detail records and it's certainly not practical to fetch them all and filter
them later.

Do I understand this correctly?

Rick

 

[RobinS]

Hi Rick,

Are you populating the parent table with all of the parent records? If that
is the case, then yes, you are screwed. (Not to put too fine a point on
it).

When have this kind of case, I put selection criteria on the screen and ask
them to narrow down their selection. I only pull the first 10,000 records
or something like that.

If you go back to your original idea of capturing the change on the parent,
and refilling the child, you can capture the PositionChanged event on the
BindingSource for the parent.

I would keep the BS for the child DGV, because it handles the data being
filled into the control so quickly. But you don't want it to be bound to
the foreign key. If you are going to reload the child every time they
change parents, the child grid should just be bound to the child table.

You might also want to put some kind of timer in there, to measure how long
they have been sitting still, so you're not trying to load the child while
they are paging through the parent trying to find the one record they want
to see the children for. Or clear the child datasource when they change
parent records, and wait until they hit a button to fill it.

Good luck. I'd like to know what kind of performance you get; hope it's
okay.

Robin S
-------------------------------

 

[Rick]

no, I only fill the parent with one requested record.

I guess the child adapter will never fill a table with all the DB rows since
it is never really asked to since the child table is filled from the FK.

Thanks for you explanations and help.

Rick

 

[Chris]

Rick

Please take a look at the last episode of Dot Net Rocks TV

http://www.dnrtv.com/default.aspx Show #60 Rocky Lhotka

There is also a link to the source code used during the demo

Rocky shows databinding to datagrids with a Parent--> Child-->Grandchild
relationship using databinding.

I may be off based here. I'm just starting to learn this stuff. Hope the
info helps.

Chris