Database and DataSet Sorting, Arbitrary?

[Carl Summers]

I have a table in an Access database that has no sort applied in
Access. When I fill a dataset with data from that table (the entire
one dimensional table) my dataset is sorted differently than the
database. This would not be a problem, and really isn't a problem
except that it means I have to search the dataset each time I want to
find a particular record instead of just storing a variable. Which
brings me to a different question.
Since my dataset is nothing more than a copy of a table in my database
(for right now) if I need to find a particular record should I query
the database or do a search on my dataset? Again, the dataset is not
ordered in any way so my search is really nothing more than a row by
row and the database is local... I guess I'm answering my own
question... Ok, clear and concise.

How is the data in the dataset being ordered?
What's the best way to find a single record in a one dimensional table?
Thanks for your time,
Carl Summers

 

[Cor Ligthert]

Carl,

First of all, a dataset is not a datatable, a dataset holds datatables.

For the datatable is the dataview what can show/use the datatable filtered
and sorted (there is even one standard in it the defaultview)

Therefore you can do
ds.tables(0).defaultview.sort = mycolumn

Or you can make more dataviews as
dim dv as new dataview(ds.tables(0))
dv.sort = mycolumn

To filter there is the dataview rowfilter and there is even a datarow.find
to find a position in that.

You can use the dataview as a datasource
http://msdn.microsoft.com/library/d...ef/html/frlrfsystemdatadataviewclasstopic.asp

I hope this helps?

Cor

 

[W.G. Ryan eMVP]

Comments below:

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com

I have a table in an Access database that has no sort applied in
Access. When I fill a dataset with data from that table (the entire
one dimensional table) my dataset is sorted differently than the
database. This would not be a problem, and really isn't a problem
except that it means I have to search the dataset each time I want to
find a particular record instead of just storing a variable.

--- I'm not sure I follow you here. For one thing - you can specify the
sort in your sql statement so your rows match the DB exactly - however I'm
guessing that's not the source of the problem.

Which

brings me to a different question.
Since my dataset is nothing more than a copy of a table in my database
(for right now) if I need to find a particular record should I query
the database or do a search on my dataset?

----Unless you have good reason to beelieve the data has been changed since
your last query and you also must have the latest data - using the local
data cache is probably the most efficient way to solve this. No need to go
grab the same data and create a new dataset just so you can have the same
stuff re-ordered. But if it's volatile data nad you are sure you need the
absolute latest stuff - then requerying it is probably the most feasible
option.

Again, the dataset is not

ordered in any way so my search is really nothing more than a row by
row and the database is local... I guess I'm answering my own
question... Ok, clear and concise. Each Table has a DefaultView property

which creates a DataView on the table. This has as Sort method that allows
you to specify columns to sort on ie MyTable.DefaultView.Sort =
"FirstColumn, SecondColumn, ThirdColumn";

That would sord MyTable first by FirstColumn, then by SecondCOlumn, Then by
ThirdColumn..

----- Ok, let's clear up a few phrases - your dataset is a collection of 0
or more datatables. If they are related - then you can work on more than
one table at a time but in most cases - you are just searching on one table
at a timie

How is the data in the dataset being ordered?
What's the best way to find a single record in a one dimensional table?

Usually DataTable Select
http://msdn.microsoft.com/library/d...f/html/frlrfsystemdatadatatableclasstopic.asp
is the preferred way - but in other cases you can use a DataView and set the
rowfilter. The problem withh the rowfilter approach is that each time you
chagne it - you create a new DataView which is a little inefficient.

If you want to use the View - try its Find & Findrows methods -

Also, if you need aggregate information Table.Compute will do it quite
elegantly for you

These two articles should help you (and the whole series is pretty good for
Sorting and efficiently using ADO.NET
http://www.knowdotnet.com/articles/adopartiii.html

http://www.knowdotnet.com/articles/expressions.html

Let me know if you have any problems.

Cheers,

Bill