Simple multiple table design question

[Danny Liberty]

Hi,

I'm building a relatively simple Windows Forms application that
supports displaying and editing of data from multiple RELATED tables.
To simplify my question, I'll supply an example:
The windows forms should display a store's sales data. The data is
divided into multiple tables: Sales, Customers and Inventory.
I built a dataset to represent the tables, and I used some bound
textboxes and grids to display the data. I also provided a data
navigator control to allow the user to switch between records.

What would be the best approach to fill and update the dataset for this
form?
The approach I took was to create one data adapter per table, then when
the form loads I fill the dataset by calling Fill() on all the data
adapters. This works just fine, but it's also very inefficient. Every
time the form loads, ALL the inventory and customers tables are loaded
into the dataset. I assume a better approach would be to fill the
dataset only with the data currently needed to display on the form. So
now I have to use BindingContext.PositionChanged and write some messy
code...

Must be a cleaner solution...anybody?

 

[Miha Markic [MVP C#]]

Hi,

I'm building a relatively simple Windows Forms application that
supports displaying and editing of data from multiple RELATED tables.
To simplify my question, I'll supply an example:
The windows forms should display a store's sales data. The data is
divided into multiple tables: Sales, Customers and Inventory.
I built a dataset to represent the tables, and I used some bound
textboxes and grids to display the data. I also provided a data
navigator control to allow the user to switch between records.

What would be the best approach to fill and update the dataset for this
form?
The approach I took was to create one data adapter per table, then when
the form loads I fill the dataset by calling Fill() on all the data
adapters. This works just fine, but it's also very inefficient. Every
time the form loads, ALL the inventory and customers tables are loaded
into the dataset. I assume a better approach would be to fill the
dataset only with the data currently needed to display on the form. So
now I have to use BindingContext.PositionChanged and write some messy
code...

You guessed right the approach. You fill data on demand and it isn't that
messy.
Why do you find it messy? You call a single method (fill data for some ID)
from within your form and that's it

 

[Danny Liberty]

Thanks.
I'm still curious though, is this what most applications do? Because if
you just drag and drop tables from the data sources view (using VS
2005) it'll gladly call DataAdapter.Fill() for you on every table when
the form loads.
So is this the best way to create master-detail forms?


Danny

 

[Otis Mukinfus]

Thanks.
I'm still curious though, is this what most applications do? Because if
you just drag and drop tables from the data sources view (using VS
2005) it'll gladly call DataAdapter.Fill() for you on every table when
the form loads.
So is this the best way to create master-detail forms?

[snip]

You guessed right the approach. You fill data on demand and it isn't that
messy.
Why do you find it messy? You call a single method (fill data for some ID)
from within your form and that's it

I agree with Miha.

All you have to do is open your XSD file by double clicking it. After it is
open just right click the Fill method at the bottom of the TableAdapter
definition. When the menu appears, follow this menu path:
Add/Query and you will be able to write the SQL that limits the number of rows
returned, including adding selection criteria in the form of parameters.


Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

 

[Dave Sexton]

Hi Otis,

You can do that in the Form designer as well. Just right-mouse click the
table adapter in the component tray and select "Add Query...". The adapter
will use the query you write or select. If you parameterize it then you can
explicitly run the query in code, just like you suggested, because the
designer will no longer be able to serialize code to execute the query for you
(since it's parameterized). Or you can leave the new ToolStrip that is added
once you finish adding the new query, which includes a button to run the query
and controls to input the parameters' values.

It sounds like the OP might not need the ToolStrip, so you can safely delete
it (and the code that was generated to handle the button click event that
executes the parameterized query), if desired.

The code that needs to be written to fill the data "on demand" isn't always
simple because sometimes you need to consider:

1. whether replacing existing, modified and uncommitted data is acceptable
2. whether replacing existing, unmodified data is acceptable

These points are more of a concern when dealing in optimistic concurrency,
when performance or memory are issues, when network traffic must be reduced or
when your end-users aren't really that computer savvy and will frequently
cause themselves to lose their hard-work when their modifications are
inadvertently replaced by a SelectedIndexChanged event handler.

--
Dave Sexton

Thanks.
I'm still curious though, is this what most applications do? Because if
you just drag and drop tables from the data sources view (using VS
2005) it'll gladly call DataAdapter.Fill() for you on every table when
the form loads.
So is this the best way to create master-detail forms?

[snip]

You guessed right the approach. You fill data on demand and it isn't that
messy.
Why do you find it messy? You call a single method (fill data for some ID)
from within your form and that's it

I agree with Miha.

All you have to do is open your XSD file by double clicking it. After it is
open just right click the Fill method at the bottom of the TableAdapter
definition. When the menu appears, follow this menu path:
Add/Query and you will be able to write the SQL that limits the number of
rows
returned, including adding selection criteria in the form of parameters.


Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

 

[Dave Sexton]

Hi Danny,

I recommend using the tools provided by VS 2005 if none of the points I've
made in my related post are of real concern to you. (But you shouldn't make
them a concern unless they really need to be). If more flexibility is
desirable than the master-details relationship designer tools and
parameterized queries can provide for you then by all means code away.

--
Dave Sexton

Thanks.
I'm still curious though, is this what most applications do? Because if
you just drag and drop tables from the data sources view (using VS
2005) it'll gladly call DataAdapter.Fill() for you on every table when
the form loads.
So is this the best way to create master-detail forms?


Danny