Data Storage Suggestions

[Brian P. Hammer]

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

 

[Luke Zhang [MSFT]]

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.)

 

[Brian P. Hammer]

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

 

[Brian P. Hammer]

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

 

[Luke Zhang [MSFT]]

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.)

 

[Cor Ligthert [MVP]]

Brian,

In addition to Luke,

On of the main standards in Net is to keep the data in the dataobjects and
to show those using controls (by instance grids).

In other words, don't use the controls to do the calculations. Use the
underlying dataobjects to do that.

There are standard dataclasses, that have a lot in it, to do what is above
written.

To make it confusing has Microsoft always written that it is the DataSet
(not anymore in version 2005). It is true, however the DataSet is just a
wrapper that holds two important elments (objects instanced from classes)

Those are the DataTable with a hug amount of properties, however as well
methods (and a lack of enough events) and the DataRelation which holds the
relations between those datatables.

One of the backward point is that a DataSet represent the formats from a
DataBase and those don't have at the moment complex types or polyphorism.

Therefore as long as the DataBases don't have those, you can invent what you
want, a straigt formward method does not exist for that and should have to
create for every existence their own table (in my opinion goes this forever
wrong in maintanance) or have to accept that some fields are not used.

Another point is that you never can hold all data in a grid, two deep is
probably often confusing for a user. Although some people think it can, you
cannot use one grid to show everything. To get a methaphore all spreadsheets
have tabpages now.

Just some ideas.

Cor

 

[Brian P. Hammer]

Luke and Cor - Thanks for the insight. I am sure I will have questions
along the way....

I will post any to the group.

Thanks,
Brian