Re: Binding sources question in Vs2005

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Bart Thanks an awfull lot, your code snippets and how tp implement them will
go in my permanent reference library.
I really appreciate your insight,

Bob
"Bart Mermuys" <bmermuys.nospam@xxxxxxxxxxx> wrote in message
news:uIQ%23Gz7GGHA.2012@xxxxxxxxxxxxxxxxxxxxxxx
> 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
>>
>>
>>
>>
>>
>>
>
>


.

Relevant Pages

  • Re: Binding sources question in Vs2005
    ... >happens is that the fill statements for the child tables get executed first ... Even if the child table is loaded after the parent it will still load all ... child rows that are potentially never seen are taking up memory. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: HOW TO: Parent/Child rows with bound controls?
    ... between the parent DataTable and a child one. ... I have a parent and child rows senario. ... > If my already did a Fillon both tables so that I have all the customers ...
    (microsoft.public.data.ado)
  • RE: The "disappearing" subform
    ... There is data in both the parent & child forms on the load. ... do you have the master child link set between the mainform and subform? ...
    (microsoft.public.access.forms)
  • Re: Converting Tree Structure from Vector
    ... >I need to create a structure which will be loaded on JSP page and will ... >values will be related if user select a child and which child to load. ... You could invent a Node class that points to a parent with perhaps an ...
    (comp.lang.java.help)
  • Re: Calling Any ADO.NET people !!! "Drama in the DataSet - by Mr Newbie "
    ... confused as to why cascading a change should render child rows unchanged ?? ... This function is called after the master row and its ...
    (microsoft.public.dotnet.languages.vb)