Dataset Versus Dataview for dynamic creation/editing

[Bennett]

Hi All,

I'm working with a project that dynamically creates datatables based
on the user input. It then refers back to these tables to access the
data, again based on user input.

For example:

*********

Dim NewTable As DataTable = New
DataTable(experimentDS.Tables("Datatable1").Rows(ListBoxExperiments.SelectedIndex).Item("ExperimentName").ToString()
& "Timeline")

' basically names the new table based on the name of an existing study
contained with a row of another table...

Dim i As Integer
Dim column As DataColumn

For i = 1 To 5

column = New DataColumn()
column.DataType = System.Type.GetType("System.String")
column.ColumnName = "variable" & Trim(Str(i))
column.ReadOnly = False
column.Unique = False

NewTable.Columns.Add(column)

Next i

ExperimentDS.Tables.Add(NewTable)

*********

You can see that the code looks at whatever entries are highlighted in
a listbox, then looks up (from a primary table containing the names of
all the experiments) the name of the study, then names the new table
based on the name of the study in question. It then adds a bunch of
dynamically created columns to the table (I use VariableN but in fact
it'll be something else in real life that is based on whatever
variable names the user wants). Data can then get added to the table
as needed.

Later, the user selects the variable they want to look at the the code
in a similar way will refer back to the table in the DataSet and extra
the data from the rows.

So far things look great. I can save the DataSet as an XML file and
the table is there and looks nice. My question though is that at some
point I am going to be having LARGE amounts of data in tables. We're
not talking a dozen or so variable names or study definitions, but
more like a few thousand rows containing hundreds of columns. There's
also the possibility that data will get added to this large table "out
of order" and will either need to get "inserted" into the Table or the
rows will need to get resorted for the software to make sense of it.

Before I commit to a large amount of programming work I need to decide
whether to use a DataSet as I have above or a DataView... I've read
around but can't find a comprehensive answer to questions like "which
is faster at searching" and "can I add tables, columns and rows in the
same way". If I update a Dataview how do I merge that back into the
Dataset to save it.

Bear in mind that I am NOT using SQL or any other database to
"query". The dataset is an XML file that gets loaded into RAM when
the program starts and the code then accesses the tables within it on
the fly. I need an entirely standalone program in order to deploy
this solution (the customer won't have access to any kind of database
system nor can I simply persuade their IT dept to allow me to install
SQL server!) In any case, when I've used SQL-based database systems
they seem to access the data extremely slowly (a second or more to
update a screen after a single change in some cases). Fine for
updating patient status in the ER, not so fine for real-time point-and-
click data analysis of large datasets. I need to be able to
progressively and randomly access thousands of data points and I've
found that doing this all in RAM is a tad faster.

I guess another option, since I have the ability to programmatically
create tables, is to have a separate table for every individual in the
experiment (cutting down on the need to resort the dataset as new data
just gets tacked onto a new row). This means the dataset will be
incredibly complex but so long as I keep the code straight everything
should work... The question then is, is the dataview faster at random
access of tables than the dataset? (Go to table X, scan through data,
go to table Y, scan through data, etc etc....)

Databinding is irrelevant as a reason to chose one over another - I
simply access the dataset tables to update textboxes, comboboxes,
listboxes etc.

**********
With Me.ListBoxExperiments
For Each ExperimentRow In
ExperimentDS.Tables("Datatable1").Rows()
.Items.Add(ExperimentRow(0))
Next
End With
*********

********
Private Sub ListBoxExperiments_SelectedIndexChanged(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
ListBoxExperiments.SelectedIndexChanged

TextBoxMainDescription.Text =
ExperimentDS.Tables("Datatable1").Rows(ListBoxExperiments.SelectedIndex).Item("Description").ToString()

End Sub

*********

Thoughts would be appreciated.

Cheers

Bennett

 

[Cor Ligthert[MVP]]

Bennet,

A dataview is a property from a datatable (called the defaultview) but you
can create more and then they are called dataview.

A dataset is a wrapper as you know around datatbles.

Be aware that you are busy with a dangerous project, the change that your
dataset on drive becomes corrupted while writing or whatever is high.

Better try an SQL database (the SQL Express one is excelent to replace your
dataset (xml file) on disk.

Cor

 

[Bennett]

Bennet,

A dataview is a property from a datatable (called the defaultview) but you
can create more and then they are called dataview.

A dataset is a wrapper as you know around datatbles.

Be aware that you are busy with a dangerous project, the change that your
dataset on drive becomes corrupted while writing or whatever is high.

The thought crossed my mind today that I should definitely create a
backup in any case - but is the risk really all that high? I mean,
any higher than any other disk access task. I'm assuming you're
referring to a SQL database having some kind of error-checking in
place.

Better try an SQL database (the SQL Express one is excelent to replace your
dataset (xml file) on disk.

If I could I might ;-)

At the very least all this faffing with tables is helping me get my
head around a proper relational database concept. Not something I've
worked with much before...

Thanks

Bennett