I have data from multiple SQL tables, some of the tables will only have one
row, while others multiple rows. I load a bunch of data from the various
tables and add them to a third party grid. With some of the rows, I perform
calculations on some of the rows and all this is loaded into the grid as
well. I am trying to figure out the best way to store all this data so that
it is easier to work with and perform calculations as users edit the grid.

Currently, I have to do lookups back to the datasets and reference each
row/column of the grid. A thought was to have some sort of dataset hold all
the combined data and calculations. Before I head off in any direction, I
thought I'd get suggestions. I also looked at the Collection class and
having several different collections but I ran into trouble getting the
related data from one collection out of another. For an idea of my data
structure, I am working with aircraft but cars will work:

Manufacturer Table - Name, City....
Automobile Table - Tire, Model, Manufacturer, (linked to manufacturer)
speed, Category (linked to category), color, seats, ac/heat
Tire Table - Manufacturer (linked to table) Tire Size, Model, Speed rating
Maintenance - Automobile (linked to table) Maintenance name, frequency,
cost, how long it takes
Global Variable - Fuel Price, Oil Price
Category Variables - Navigation Service, On-Star, Taxes, Residual Value....

I could load 5 different Automobiles, from five different manufacturers and
two different categories. In the grid, I compare the various information.

Any you have would be great.

Thanks,
Brian

microsoft.public.dotnet.framework & microsoft.public.dotnet.general

Hello Brain,

I think the best idea is to keep the data in a dataset, you can have
multiple tables in a dataset, the table/data can be deleted/modified and
the changes can be recorded. More important, you can keep relatationship
between tables so that it can be referenced by each other. This is rather
better than collections. Not sure what the datagrid you used, but most .NET
datagrid controls should support dataset well. What do you think?

Luke Zhang
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Luke - Thanks for the feedback. I use the FlexGrid from Component One and
they support both bound and unbound modes.

This is the track I am on....

So would you recommend that I add a column to my dataset when I have a
calculated value. Using my car example, I'll have a data column fuel price,
miles per gallon and mile driven. Then my calculated value that I add to
the grid called Monthly Fuel Cost which is calculated from Miles Driven /
Miles per gallon * Fuel Price. Would you add the Monthly Fuel to the
dataset and if so, does help show how?

Thanks,
Brian



[quoted text, click to view]

Yes, dataset/datatable also support a "calculated column". You need to set
a data cloumn's Expression property, here is a sample:

Private Sub CalcColumns()
Dim rate As Single = .0862
dim table as DataTable = New DataTable

' Create the first column.
Dim priceColumn As DataColumn = New DataColumn
With priceColumn
.DataType = System.Type.GetType("System.Decimal")
.ColumnName = "price"
.DefaultValue = 50
End With

' Create the second, calculated, column.
Dim taxColumn As DataColumn = New DataColumn
With taxColumn
.DataType = System.Type.GetType("System.Decimal")
.ColumnName = "tax"
.Expression = "price * 0.0862"
End With

' Create third column
Dim totalColumn As DataColumn = New DataColumn
With totalColumn
.DataType = System.Type.GetType("System.Decimal")
.ColumnName = "total"
.Expression = "price + tax"
End With

' Add columns to DataTable
With table.Columns
.Add(priceColumn)
.Add(taxColumn)
.Add(totalColumn)
End With

Dim row As DataRow= table.NewRow
table.Rows.Add(row)
Dim view As New DataView
view.Table = table
DataGrid1.DataSource = view
End Sub

Hope this help,

Luke Zhang
(This posting is provided "AS IS", with no warranties, and confers no
rights.)