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. 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 and going accross the network.
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 - 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.
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.
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
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 2 1856
Hi,
"Bob" <bd*****@sgiims.com> wrote in message
news:OE*************@TK2MSFTNGP12.phx.gbl... 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
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" <bm*************@hotmail.com> wrote in message
news:uI****************@TK2MSFTNGP14.phx.gbl... Hi,
"Bob" <bd*****@sgiims.com> wrote in message news:OE*************@TK2MSFTNGP12.phx.gbl...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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: erik.c.larson |
last post by:
I imported a projected I created in VS2003 to VS2005 beta 2. The
project builds just fine, but throws an ArgumentException (cannot bind
property...) when I add a control to a Controls list. When...
|
by: popsovy |
last post by:
Hi
I have a question about whether Data Binding can facilitate the process of saving data in a web application
I learned that you can data bind information from a number of different data...
|
by: dbuchanan |
last post by:
Hello,
A section in Data Sources window is mystifying to me.
In the case of my code the Data Sources window shows my references to
the data access layer.
First here is what I see in my Data...
|
by: emzyme20 |
last post by:
Hi,
I am trying to populate a list control that is bound to a data table
but the display member needs to come from a different data table.
I have two list controls in C#, one displaying...
|
by: Steven Bolard |
last post by:
Hello,
I am trying to port my .net 1.1 application to 2.0. I am using vs2005. I am
trying to get my webservices to run and although i can compile them and and
get wsdl and service descriptions...
|
by: David Lee Conley |
last post by:
When I open the Data Sources window and create a new data source, everything
works fine. But if I have a form showing in the IDE, the Data Sources
window becomes disabled and doesn't display any...
|
by: Greg Collins [Microsoft MVP] |
last post by:
I am starting a project where I will be using the XML data type in SQL Server 2005, and ASP.NET to display and update the content of the XML.
I've been digging around for a bit trying to find some...
|
by: Max |
last post by:
Hello,
I made a windows form with a combo box and 4 text boxes. All 5 objects should get their data from a data set which is populated
in the form load method. The combo box has item ids. When...
|
by: John Sheppard |
last post by:
Hello there,
I was wondering if anyone knew how to loop through all binding sources on a
form?
I tried the below code but the me.controls collection doesnt return
everything :(
For Each obj...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |