Re: Binding sources question in Vs2005
- From: "Bart Mermuys" <bmermuys.nospam@xxxxxxxxxxx>
- Date: Wed, 18 Jan 2006 00:21:47 +0100
Hi,
"Bob" <bdufour@xxxxxxxxxx> wrote in message
news:OELk7E4GGHA.740@xxxxxxxxxxxxxxxxxxxxxxx
>I have noticed that as one adds related tables to a bindingsource and
>dataset(vs2005 Vb.Net Sql Server 2005), the TODO and following code that
>fills the datasets on form load are written to the formload event in the
>order in which you drag the tables to the form to make a gridview, the last
>table you drag is written to the top. Since you normallly start by dragging
>the parent table and then the child tables and grandchild tables, what
>happens is that the fill statements for the child tables get executed first
>by default.
Even if the child table is loaded after the parent it will still load all
child rows and the child table should be loaded after the parent otherwise
you may get constraint errors saying that the parent row doesn't exist
(yet).
>The implication of this seems to be that the total contents of the child
>tables get taken from the server (sql 2005 in my case) and that as the
>parent record is changed by the user as he runs the form, a filter is
>applied dynamically when you change the row to show only those records in
>the child grids that are then pertinent, but the other unneeded records are
>there taking up ressources in the background
Yes, child rows that are potentially never seen are taking up memory.
> and going accross the network.
Yes, but only once at form_load all rows would be loaded (parent & child).
>
> In systems with few records and on a fast LAN this is no problem. I'm
> concerned however when the number of records goes up into the many
> thousands, even millions. I had tested something with over 50000 patients
> or customers in Vs2003 and found in VS 2003 at least this was the
> behaviour. It was simple to test, you just executed a count on the table
> in the dataset after it was filled and you saw that the result
> corresponded to the number of records in the sql table. I found that forms
> designed with this behaviour could take up to twenty seconds to load 50000
> customer records and their related info. The typical answer in this
> situation when I brought this up was, thats the way ado works -
ADO.NET, true.
> use virtual mode datasets, but that in my humble opinion is a poor
> substitute for really minimizing the number of records to the ones you
> really need at a particular time and so minimizing the transfer of info
> over the network and minimizing ressource useage on the local machine. In
> 2003 we developped a dll that does this filtering and we brought the load
> time down from 20 seconds to less than a second. We were Using C1 data
> controls at the time but in Vs2005 we're not planning to use those.
Not sure what you mean with virtual mode dataset, virtual mode controls i
know, but afaik a bound DataGridView behaves pretty much as a virtual mode
control but eg. a ComboBox not.
>
> What I think is needed is the filling of the child datasets with filtered
> neede info for that parent only downloaded from he server each time change
> the selected parent is changed. In my case this would work since the
> interface allows single row selection of the parent(s) only.
You could implement this yourself, by adding a parameterized query to the
existing child TableAdapter with a fk parameter:
- Inside DataSet schema designer, right-click on the child TableAdapter and
choose "Add query", then make a query that returns the rows only for a
certain fk, eg.
SELECT * FROM child WHERE fk = @fk
The last step of the wizards will ask you for a Fill name, choose FillByFk
and finish wizard.
- Then attach an event to ParentBindingSource.CurrentChanged and make it get
the child rows:
Private Sub ParentBindingSource_CurrentChanged(...) Handles ....
Dim currentDRV As DataRowView = DirectCast( _
ParentBindingSource.Current, DataRowView)
ChildTableAdapter.FillByFk(SomeDataSet.ChildTable, CInt(
curentDRV["pk"] ) )
End Sub
Note; in the above text and code you should replace pk & fk with the actual
pk & fk column names you have.
>
> I would appreciate your comments on this situation and any suggestions on
> how best to proceed and what pitfalls to look for when implementing this
In the above code above it looks like the child DataTable isn't cleared
before filling again, but be aware of the child TableAdapter on the Form, it
has a property "ClearBeforeFill" which is true by default, but can be turned
off.
If you clear the child DataTable before filling it (again) then you must
first save the (previous) child rows otherwise you may loose changes. You
still have a choice whether you want to clear it or not (existing child rows
will be overwritten, at least if they also have a pk). Then there is
another option only load the child rows for a given parent once, but then
you'll need something to store 'for which parent rows the child rows are
already loaded', maybe an extra (dummy) boolean column in the parent
DataTable.
But because of the "lazy-loading", the UI may now freeze a little when you
browse parent rows when there are a lot of child rows for each parent. That
brings us to "asynchronous" loading of both parent and child rows and in
case of 'loading child rows' abort when user moves to another parent,
unfortunetally there isn't much support for this.
HTH,
Greetings
> type of approach, also of course if you think I'm totally off base, please
> let me know. I may be trying to reinvent the wheel and if so I'd
> appreciate knowing.
>
> Thanks for your time.
>
> Bob
>
>
>
>
>
>
.
- Follow-Ups:
- Re: Binding sources question in Vs2005
- From: Bob
- Re: Binding sources question in Vs2005
- References:
- Binding sources question in Vs2005
- From: Bob
- Binding sources question in Vs2005
- Prev by Date: Re: How do menu list with one check
- Next by Date: Re: LIKE operator question
- Previous by thread: Binding sources question in Vs2005
- Next by thread: Re: Binding sources question in Vs2005
- Index(es):
Relevant Pages
|
