Searching a table

[Geoff Jones]

Hiya

I have a DataTable containing thousands of records. Each record has a
primary key field called "ID" and another field called "PRODUCT" I want to
retrieve the rows that satisy the following criteria:

I have a list of about 100 numbers which correspond to the ID field and also
another 40 say numbers corresponding to the numbers in the PRODUCT field. I
want to show the rows that correspond to both these criteria.

My oringinal idea was to create a DataView and then generate a string an SQL
string, which I could then use with RowFilter to get the rows I want.

However, I'm uncertain of the SQL syntax to use and, more importantly, if
this is the best way to do it.

Can anybody help?

Geoff

 

[Bernie Yaeger]

Hi Geoff,

Select * from ttable where id in ('324','17', '934'...etc) and product in
('a1234','3434'...etc)

Now, if these are variable and selected from, say, a listbox by the user,
then you have to use a form of dynamic sql - not of the traditional kind,
but rather the vb .net kind. If that is the case, let me know - I can show
you how to do this easily by reconstructing the sql string from a variable
array or arraylist.

HTH,

Bernie Yaeger

 

[Bernie Yaeger]

Hi Geoff,

By the way, if you already have a 'larger' datatable, you can use the method
I showed you earlier in a dataview select.

Bernie

 

[Geoff Jones]

Hi Bernie

Yes, the variables are indeed selected from a list box. Two in fact. One
list box generates the ID's and the other generates the product keys. So, in
effect, I'm trying to get an AND. The rows in the table that correspond to
the ID "AND" the PRODUCT id.

The dynamic SQL sounds like exactly what I'm looking for

I look forward to hearing your idea.

Thanks in advance

Geoff

 

[Bernie Yaeger]

Hi Geoff,

Here's essentially how I do this (and I've done so literally in more than
100 different routines):

1. I generally have a 'frombox' and 'tobox' listboxes; the user moves the
selected items from the from box to the to box;
2. create an arraylist containing the selections in the tobox when they
click 'process' or whatever; make a string - 'longstring' - from this array,
such that it looks like "('123','556','329'...etc)" by using a simple for
loop - here's an example (where i = tobox.items.count)
longstring = "('"

For j = 0 To i - 1

longstring = longstring & RTrim(titlearray(j)) & RTrim(issuecodearray(j)) &
Chr(39) & ",'"

Next

longstring = Mid(longstring, 1, longstring.Length - 2)

longstring = longstring & ")"

3. do this for both the id and the product columns, so that you will have
longstring1 and longstring2

4. construct your .select of the dataview like this:
For Each irow In dv.Table.Select("bipad in (" & longstring & ")")

In this example I didn't build longstring to have the open and close parens,
but you can do it either way.

Let me know if you have any problems with this.

Bernie

 

[Cor Ligthert]

Bernie,

I break a little bit in, about the majority of your answer I see no need to
discuss, however my eye did fall on this. Here are you using in fact the
datatable select from the original table.

For Each irow In dv.Table.Select("bipad in (" & longstring & ")")

dv.Table tells which datatable the dataview is using and is not a dataview
method, however a property.

I would use in this case the original datatable direct. Although I would
probably choose to read a new dataset from the database and create a small
dataset for selections.

Just to inform you.

Cor

 

[Geoff Jones]

Hi Bernie

Thanks very much. I wasn't familiar with the syntax for "And"ing the
numbers. Interesting.

I take it that Chr(39) is a way to generate ' ? Could I also use "'"?

In addition, I assume that I'd write:

For Each irow In dv.Table.Select("ID in (" & longstring & ")")

i.e. bipad has been changed to ID (I'm "bipad" is not a keyword but rather
the name of the field I'm searching?)

Could I also trouble you further with a bit of the syntax I need? The line
above will gengerate the rows I require for one criteria. What if I wanted
to filter this selection down further by, for example, finding all the rows
where the "PRODUCT" field was equal to, say, (3,565,3457)?

Would it be something like, in pseudo code:

For Each irow In dv.Table.Select("ID in (" & longstring1 & ")" & " AND " &
"PRODUCT in (" & longstring2 & ")"

Thanks in advance

Geoff

 

[Bernie Yaeger]

Hi Geoff,

Yes to every question: chr(39) is an apostrophe and yes you can call it with
"'"; 'bipad' is my field (column) name; you're pseudocode for using 'and' is
correct, although the term 'anding' is usually used in a different context
(re hex notation).

Glad to help,

Bernie

 

[Bernie Yaeger]

Hi Cor,

You are correct, but either approach will work. Geoff mentioned that he had
a datatable already created from which he wanted to drill down further.

Bernie

 

[Geoff Jones]

Many thanks for your help Bernie. It is most appreciated.

Geoff

 

[Bernie Yaeger]

Hi Goeff,

Last point - if the select ever errors out, look at it in messagebox.show to
make sure all of the apostrophes and parens are in the right place, not
doubled, not missing, etc.

Bernie

 

[Cor Ligthert]

Bernie,

Therefore it was only to point you attention on it, I hope you understood
that was all I wanted to do.

Cor

 

[Bernie Yaeger]

Hi Cor,

Of course; you are always helpful - tx

Bernie

 

[Geoff Jones]

Hi Bernie

I've tried the "IN" scheme and it works but it is very slow. Am I doing
something wrong or can it be slow in searching?

Geoff

 

[Bernie Yaeger]

Hi Goeff,

It shouldn't be slow. Can you send me a code snippet and an idea of the
tables you are using - number of rows, number of columns, kind of database
(sql, ms access, etc).

I would like to test it on my system to see what I experience.

Bernie

 

[Geoff Jones]

Thanks for the kind offer Bernie. Unfortunately the database is massive i.e.
in excess of 200,000 rows!

The code I am using is something like:

Dim f As String = "ID IN ('543', '2345', '435', etc - goes on for about
10,000 numbers) AND PRODUCT IN ('345', '456', '3445')"

Dim vue As DataView = New DataView(theBigTable)

vue.RowFilter = f ' The program appears to hang here i.e. very slow

Obviously, the table is VERY large, so I'd expect some delay. However, the
first bit of code I wrote used a "Find" routine of a DataTable to find the
"ID" numbers (could do this because they were the primary keys). I then,
after finding the rows that satisifed the ID condition, I simply iterated
through them to find the required rows satisfying the second condition i.e.
PRODUCT.

This method worked well i.e. relatively quickly. Is it possible then that
I'm seeing a big increase in filtering time because I'm not searching on the
primary key fields?

Geoff

 

[Geoff Jones]

"not searching on the primary key fields" i.e. not using "Find"

Geoff

 

[Cor Ligthert]

Geoff,

Can you try this piece of code.

\\\Needs one datagrid and a button the first part is to build the table.
Dim dt As New DataTable
Private Sub Form1_Load(ByVal sender _
As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
dt.Columns.Add("number")
For i As Integer = 0 To 200000
dt.Rows.Add(dt.NewRow)
dt.Rows(i)(0) = i.ToString
Next
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim str() As String = {"1", "3", "100", "100000", "150", "199999"}
Dim dtnew As DataTable = dt.Clone
For Each st As String In str
Dim dr As DataRow() = dt.Select("number = '" & st & "'")
If Not dr Is Nothing Then
dtnew.ImportRow(dr(0)) _
'assuming there can only be one otherwise a loop
End If
Next
DataGrid1.DataSource = dtnew
End Sub
///

(I think that it can be faster by looping through the sorted dataview and
make a clever check about the as well sorted part what has to be checked
everytime. That is a complete different approach by the way).

However try this one first because that other one is more difficult code.

I hope this helps.

Cor

 

[Bernie Yaeger]

Hi Geoff,

OK; I've got your code.

First, let me tell you that we will get this working effectively - 200,000
rows is not daunting. But I need more information. Am I correct in
assuming that you first specify the 10,000 rows and then you want to 'find'
rows that are among those 10,000 but also meet a certain product id
condition? If this is so, let me know - I can construct a dataview of the
10,000 and show you how to search it very easily, with or without using the
PK.

Get back to me asap. I will work on this approach until I hear from you.

Bernie

 

[Cor Ligthert]

Geof,

I was curious if the select with a OR would be faster than looping, I think
not. However this is your problem more complete in my opinion using a Select
and concatinated OR's (that stringbuilding can better using a for index
however in that part I did not wanted to change it anymore)

\\\
Dim dt As New DataTable
Private Sub Form1_Load(ByVal sender _
As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
dt.Columns.Add("ID")
dt.Columns.Add("PRODUCT")
For i As Integer = 0 To 200000
dt.Rows.Add(dt.NewRow)
dt.Rows(i)(0) = (i \ 10).ToString
dt.Rows(i)(1) = (i \ 100).ToString
Next
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim str1() As String = {"10", "3", "100", "150", "19999"}
Dim str2() As String = {"1", "10", "150", "19990"}
Dim dtnew1 As DataTable = dt.Clone
Dim sb1 As New System.Text.StringBuilder
For Each st As String In str1
sb1.Append("ID = '" & st & "'" & " OR ")
Next
sb1.Append("ID = 'NotExist'")
Dim dr1 As DataRow() = dt.Select(sb1.ToString)
If Not dr1 Is Nothing Then
For Each drs As DataRow In dr1
dtnew1.ImportRow(drs)
Next
End If
Dim dtnew2 As DataTable = dtnew1.Clone
Dim sb2 As New System.Text.StringBuilder
For Each st As String In str2
sb2.Append("PRODUCT = '" & st & "'" & " OR ")
Next
sb2.Append("PRODUCT = 'NotExist'")
Dim dr2 As DataRow() = dtnew1.Select(sb2.ToString)
If Not dr2 Is Nothing Then
For Each drs As DataRow In dr2
dtnew2.ImportRow(drs)
Next
End If
DataGrid1.DataSource = dtnew2
End Sub
///

I hope this helps?

Cor