469,329 Members | 1,364 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,329 developers. It's quick & easy.

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

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
Jun 27 '06 #1
4 5836
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
"Rich" <Ri**@discussions.microsoft.com> schreef in bericht
news:CC**********************************@microsof t.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

Jun 28 '06 #2
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]


"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

Jun 28 '06 #3
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

Jun 28 '06 #4
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]

"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

Jun 29 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Lance Hoffmeyer | last post: by
2 posts views Thread by Zambo via SQLMonster.com | last post: by
1 post views Thread by cyrvb | last post: by
13 posts views Thread by Vai2000 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.