how to search a datatable-can I use sql on a vb2005 datatable?

27 Jun 2006 4:31 PM

Rich

Hello,

I need to store various values that I will need to look up later on. I have
been using hashtables and arraylists. But I can only store 2 items per row
in a hashtable - key, value, and only 1 item in an arraylist. Or, I could
create a class with various members and store that in a collection, or a
structure, ...

I was thinking, I could create a datatable and add data to the datatable.
But when I need to retrieve data from this table what is the best way to
search it? Can I use a sqldatareader on a datatable? like (pseudocode)

dim dtr as sqldatareader = "select * from datatable1 where recordID = 100"

Is something like this doable? What is the correct/best way to search a
datatable? I hope it isn't just to loop through all the rows.

Thanks,
Rich

28 Jun 2006 5:30 AM

Cor Ligthert [MVP]

Rich,

You can use the
Datatable.Select 'returns collection, this is not SQL code before that you
think that
Datatable.Defaultview.rowfilter 'returns a collection search at dataview
DataTable.Defaultview.find 'returns an index
Rowcollection.find 'returns a collection

And I thought another one which won't come in my mind,

I hope this helps,

Cor

Show quoteHide quote

"Rich" <R***@discussions.microsoft.com> schreef in bericht
news:CC1A6827-7A6B-476E-8F31-0D3C90A9029C@microsoft.com...
> Hello,
>
> I need to store various values that I will need to look up later on. I
> have
> been using hashtables and arraylists. But I can only store 2 items per
> row
> in a hashtable - key, value, and only 1 item in an arraylist. Or, I could
> create a class with various members and store that in a collection, or a
> structure, ...
>
> I was thinking, I could create a datatable and add data to the datatable.
> But when I need to retrieve data from this table what is the best way to
> search it? Can I use a sqldatareader on a datatable? like (pseudocode)
>
> dim dtr as sqldatareader = "select * from datatable1 where recordID = 100"
>
> Is something like this doable? What is the correct/best way to search a
> datatable? I hope it isn't just to loop through all the rows.
>
> Thanks,
> Rich

28 Jun 2006 11:39 AM

M. Posseth

If you go for lightning speed and the smallest footprint

i would say go for the structure , hashtable combo

however i would only recomend this if you have a primary key value pair
record layout like this
id data data2 data3 data4 data5 etc etc

if it could also be the case that you might need to search on one or more
of the data fields then a datatable is much more flexible

however if it is so that you need to seacrh on combinations or joins of
fields
well then i would store the values in a SQL capable database

regards

Michel Posseth [MCP]

Show quoteHide quote

"Rich" wrote:

> Hello,
>
> I need to store various values that I will need to look up later on. I have
> been using hashtables and arraylists. But I can only store 2 items per row
> in a hashtable - key, value, and only 1 item in an arraylist. Or, I could
> create a class with various members and store that in a collection, or a
> structure, ...
>
> I was thinking, I could create a datatable and add data to the datatable.
> But when I need to retrieve data from this table what is the best way to
> search it? Can I use a sqldatareader on a datatable? like (pseudocode)
>
> dim dtr as sqldatareader = "select * from datatable1 where recordID = 100"
>
> Is something like this doable? What is the correct/best way to search a
> datatable? I hope it isn't just to loop through all the rows.
>
> Thanks,
> Rich

28 Jun 2006 3:11 PM

Rich

Thank you all for your replies. I think Cor had a little more in mind what I
was looking for in that I am looking for methods to search a datatable. My
thinking is that if I pull of small dataset - say a few hundred records from
a sql server data source containing hundreds of thousands or maybe millions
of records, I would hate to have to keep going back to the database. So I
pull a small subset of data into a dataTable. But now I have to search on
that dataTable.

>>

Datatable.Defaultview.rowfilter 'returns a collection search at dataview
DataTable.Defaultview.find 'returns an index
Rowcollection.find
<<

These methods seems like the methods I would be looking for. I don't
imagine it would be possible to search on more than one parameter would it?

Well thanks all for your replies.

Rich
Show quoteHide quote

"Rich" wrote:

> Hello,
>
> I need to store various values that I will need to look up later on. I have
> been using hashtables and arraylists. But I can only store 2 items per row
> in a hashtable - key, value, and only 1 item in an arraylist. Or, I could
> create a class with various members and store that in a collection, or a
> structure, ...
>
> I was thinking, I could create a datatable and add data to the datatable.
> But when I need to retrieve data from this table what is the best way to
> search it? Can I use a sqldatareader on a datatable? like (pseudocode)
>
> dim dtr as sqldatareader = "select * from datatable1 where recordID = 100"
>
> Is something like this doable? What is the correct/best way to search a
> datatable? I hope it isn't just to loop through all the rows.
>
> Thanks,
> Rich

29 Jun 2006 6:51 AM

M. Posseth

Hello Rich ,

> I think Cor had a little more in mind what I
> was looking for in that I am looking for methods to search a datatable

Euh yes ,,,, is my english so bad ?? in my memory and after rereading i
fully comply with Cor`s answer my answer was an additive to his answer
and the fact of what you are currently using

to answer your question :
<snip my previous answer >
if it could also be the case that you might need to search on one or more
of the data fields then a datatable is much more flexible
</snip my previous answer>

so the answer is yes you can search on more then one field

example ( copied and pasted from one of my projects )

For Each dr As DataRow In dtObjecten.Select("Verwerk='True' AND
errorFataal='False'")
If BlnStop Then Exit Sub
ObjectsSelVerw.Add(dr.Item(1), dr.Item(1))
Next

regards

Michel Posseth [MCP]

Show quoteHide quote

"Rich" wrote:

> Thank you all for your replies. I think Cor had a little more in mind what I
> was looking for in that I am looking for methods to search a datatable. My
> thinking is that if I pull of small dataset - say a few hundred records from
> a sql server data source containing hundreds of thousands or maybe millions
> of records, I would hate to have to keep going back to the database. So I
> pull a small subset of data into a dataTable. But now I have to search on
> that dataTable.
>
> >>
> Datatable.Defaultview.rowfilter 'returns a collection search at dataview
> DataTable.Defaultview.find 'returns an index
> Rowcollection.find
> <<
>
> These methods seems like the methods I would be looking for. I don't
> imagine it would be possible to search on more than one parameter would it?
>
> Well thanks all for your replies.
>
> Rich
> "Rich" wrote:
>
> > Hello,
> >
> > I need to store various values that I will need to look up later on. I have
> > been using hashtables and arraylists. But I can only store 2 items per row
> > in a hashtable - key, value, and only 1 item in an arraylist. Or, I could
> > create a class with various members and store that in a collection, or a
> > structure, ...
> >
> > I was thinking, I could create a datatable and add data to the datatable.
> > But when I need to retrieve data from this table what is the best way to
> > search it? Can I use a sqldatareader on a datatable? like (pseudocode)
> >
> > dim dtr as sqldatareader = "select * from datatable1 where recordID = 100"
> >
> > Is something like this doable? What is the correct/best way to search a
> > datatable? I hope it isn't just to loop through all the rows.
> >
> > Thanks,
> > Rich