a vb.net equivalent to the sql IN function?

  • Thread starter Thread starter hazz
  • Start date Start date
H

hazz

I need to get a set of comma delimited client ids from a config file to test
against while running through my main processing loop.
If a given clientid matches any of the ids in the list, I don't want to send
email to them.

What is the best way to code that? I was thinking of the idea of if
currentClientID is not in the list of these clientids, then .....

Thanks,
Greg
 
Hello hazz,

While not incredibly sexy, an easy way to do this would be:

Dim tIDStr As String = "1,2,3,4,5,6,7,8,9"
Dim tIDs() As String = tIDStr.Split(",")

If (tIDs.GetLowerBound(0) - 1) = Array.IndexOf(tIDs, "3") Then
' NOT IN ARRAY
Else
' IN ARRAY
End If

There are of course other methods.

Enjoy,
-Boo
 
GhostInAK said:
Hello hazz,

While not incredibly sexy, an easy way to do this would be:

Dim tIDStr As String = "1,2,3,4,5,6,7,8,9"
Dim tIDs() As String = tIDStr.Split(",")

If (tIDs.GetLowerBound(0) - 1) = Array.IndexOf(tIDs, "3") Then
' NOT IN ARRAY
Else
' IN ARRAY
End If

There are of course other methods.
Click to expand...

You can get this down to one (ugly) line:

Dim theList As String = "1,2,33,56,7,8,9"
Dim theItem As String = "56"

Dim contains As Boolean

contains = DirectCast(theList.Split(","c),
IList).Contains(theItem)

But this should really be hidden away in a utility class that just
exposes a Contains method that takes a comma-delimited string and an
item.
 
Larry said:
GhostInAK wrote:
Click to expand...

OK, I'm [probably] going to rekindle a Religious War here but,
hopefully, learn something at the same time ...

In VB6, I'd have done [something like] this:

Dim sSearch As String = "1,2,3,4,5,6,7,8,9"
Dim sTarget As String = "4"

If ("," & sSearch & ",").IndexOf("," & sTarget & ",") > -1 Then
. . .
End If

Is this horribly, horribly wrong in our Brave New World?

TIA,
Phill W.
 
Phill said:
Larry said:
GhostInAK wrote:
Click to expand...

OK, I'm [probably] going to rekindle a Religious War here but,
hopefully, learn something at the same time ...

In VB6, I'd have done [something like] this:

Dim sSearch As String = "1,2,3,4,5,6,7,8,9"
Dim sTarget As String = "4"

If ("," & sSearch & ",").IndexOf("," & sTarget & ",") > -1 Then
. . .
End If

Is this horribly, horribly wrong in our Brave New World?
Click to expand...

Well, it works, which is good, and it's not horribly ugly, which is
better, and also you are using IndexOf rather than Instr, so you're
clearly well on the way to thinking in a .NET way :)

The only comment I'd make is that stylistically I'd prefer my method
(cast to IList and use .Contains), because semantically it does only
what is necessary (determines containment); whereas you obtain the
actual _position_ of the sought item in the searched list, only to
throw it away.

A matter of taste, really.
 
Larry said:
OK, I'm [probably] going to rekindle a Religious War here but,
hopefully, learn something at the same time ...

In VB6, I'd have done [something like] this:

Dim sSearch As String = "1,2,3,4,5,6,7,8,9"
Dim sTarget As String = "4"
If ("," & sSearch & ",").IndexOf("," & sTarget & ",") > -1 Then
. . .
End If
Is this horribly, horribly wrong in our Brave New World?
Click to expand...

As with most things, the best option when faced with a number of alternatives
is to test them against each other. I was expecting to recommend the .Contains
method mentioned by Larry Lard, but my test rig shows that it is the slowest
option. I did need to make some tweaks (most notably in GhostInAK's post
which returned true only if the item was not in the search pattern). I also
made some optomizations to pre evaluate items which didn't change for each
iteration of the test.

Essentially in each test, I am testing the values 0-10 against a list of
numbers 1-9 to see if they match, if so, add them to a running total of successful
matches. I am running this test 1000 times for each variant to try to get
a significant result. The results are similar if I change the number of iterations
to 1 or 1,000,000. Feel free to try this for yourself to see if you can come
up with a better algorhythm. Naturally, there is often a trade-off between
performance and maintainablility. You will need to determine which option
is the best for your situation.

Here is my test rig (using 2005):

Public Shared Sub Main()
Const IterationCount As Integer = 1000
Dim sw As New Stopwatch

Dim Pattern As String = "1,2,3,4,5,6,7,8,9"
Dim PatternArray() As String = Pattern.Split(",")
Dim CountSuccess As Integer

Console.Write("method1: ")
sw.Start()
For iterations As Integer = 1 To IterationCount
For TestItem As Integer = 0 To 10
If Not (Array.IndexOf(PatternArray, TestItem.ToString) =
-1) Then
CountSuccess += 1
End If
Next
Next
sw.Stop()
Console.WriteLine(sw.ElapsedTicks.ToString)

Console.Write("method2: ")
sw.Start()
Dim TestPattern As String = "," & Pattern & ","
For iterations As Integer = 1 To IterationCount
For TestItem As Integer = 0 To 10
If TestPattern.IndexOf("," & TestItem.ToString & ",") > -1
Then
CountSuccess += 1
End If
Next
Next
sw.Stop()
Console.WriteLine(sw.ElapsedTicks.ToString)

Console.Write("method3: ")
Dim Test3PatternList As IList = Pattern.Split(","c)
sw.Start()
For iterations As Integer = 1 To IterationCount
For TestItem As Integer = 0 To 10
If Test3PatternList.Contains(TestItem.ToString) Then
CountSuccess += 1
End If
Next
Next
sw.Stop()
Console.WriteLine(sw.ElapsedTicks.ToString)
Console.WriteLine("Total success: " & CountSuccess.ToString)
Console.WriteLine("Total should equal: " & (9 * 3 * IterationCount).ToString)
Console.ReadLine()
End Sub

Here are the results when run in release mode:

method1: 22803
method2: 57436
method3: 88614
Total success: 27000
Total should equal: 27000

Jim Wooley
http://devauthority.com/blogs/jwooley/default.asp
 
Dim Pattern As String = "1,2,3,4,5,6,7,8,9"
Dim PatternArray() As String = Pattern.Split(",")
Dim CountSuccess As Integer

Console.Write("method1: ")
If Not (Array.IndexOf(PatternArray, TestItem.ToString) = -1) Then .. . .
Console.Write("method2: ")
If TestPattern.IndexOf("," & TestItem.ToString & ",") > -1 Then .. . .
Console.Write("method3: ")
If Test3PatternList.Contains(TestItem.ToString) Then
Click to expand...
.. . .
Here are the results when run in release mode:

method1: 22803
method2: 57436
method3: 88614
Click to expand...

Thanks for that, Jim.

If nothing else, it shows that my "throw-away String" method takes more
than twice as long to run as searching for an element in an array -
another VB favourite on its way to the Scrap Heap, me thinks.

Regards,
Phill W.
 
Hello Jimbo,

I would like to point out that Larry Lard is quite correct in his assertion
that whichever method is chosen it needs to be abstracted away into a function
that accepts a delimited string and a value as inputs and returns a boolean.

Second, if the Array.IndexOf approach is chosen, PLEASE read the documentation.
This is NOT vb6. The result of Array.IndexOf is ONE LESS than the array's
lower bound if an item is not found. This is a huge split from the -1 result
from VB6's InStr.
If Not (Array.IndexOf(PatternArray, TestItem.ToString) = -1) Then
Click to expand...


-Boo
 
Larry said:
GhostInAK wrote:
Click to expand...

OK, I'm [probably] going to rekindle a Religious War here but,
hopefully, learn something at the same time ...

In VB6, I'd have done [something like] this:

Dim sSearch As String = "1,2,3,4,5,6,7,8,9"
Dim sTarget As String = "4"

If ("," & sSearch & ",").IndexOf("," & sTarget & ",") > -1 Then
. . .
End If

Is this horribly, horribly wrong in our Brave New World?
Click to expand...

IMHO, yes that's horribly wrong.

It works, which is a plus, but if you're reading that code in a year's
time I can't believe you won't stop to figure out what's going on.

The array option isn't bad, but I must admit I find array syntax
cumbersome. It's much easier to simply create a list....

Dim allowedIds as new List(Of String)(sSearch.Split(","c))

if allowedIds.Contains(sTarget) Then
.....


Worrying about efficient code here is probably counterproductive. It's
extremely unlikely that this simple lookup is going to be your
bottleneck, especially if you're reading the list of ids from disk. Go
for what's most readable.
 
GhostInAK said:
The result of Array.IndexOf is ONE LESS than the array's lower bound
if an item is not found.
Click to expand...

Does that mean that we can still /have/ arrays whose lower bound is
/not/ zero? I thought we'd consigned those to History and that /every/
array just started at zero now.

Or am I just deluding myself (again)?

Regards,
Phill W.
 
Jim Wooley
http://devauthority.com/blogs/jwooley/default.aspx
Hello Jimbo,

I would like to point out that Larry Lard is quite correct in his
assertion that whichever method is chosen it needs to be abstracted
away into a function that accepts a delimited string and a value as
inputs and returns a boolean.

Second, if the Array.IndexOf approach is chosen, PLEASE read the
documentation.
This is NOT vb6. The result of Array.IndexOf is ONE LESS than the
array's
lower bound if an item is not found. This is a huge split from the -1
result
from VB6's InStr.

-Boo
Click to expand...

I understand how the lower bound of the array can be something other than
0. I was trying to isolate the time for parsing on the timers rather than
including the time for determining the lowerbound on each iteration. I did
find that I forgot to reset the stopwatch after each test, so the timing
results are cumulative rather than per test. I revised the test and still
found that the Contains method was the slowest, but not my as much as originally
thought.

I also added a couple other tests to use your method of testing the LowerBound
on each iteration (method 1.1) as well as pulling it and putting it into
a variable outside of the loop (method 1.2). here are the results for 1000
iterations:

Testing against -1: 25093
testing against Array.GetLowerBound: 28384
Using a named variable outside of the loop and using Array.GetLowerBound:
22342

I am not surprised with the results of the second test. I am somewhat surprised
that the third is faster than the second method even though it would appear
that you are adding to the memory overhead. It is more readable and maintainable.
The source for these three tests are below. I welcome alternative methods
to test as well.

Jim Wooley
http://devauthority.com/blogs/jwooley/default.aspx


Console.Write("method1: ")
sw.Start()
For iterations As Integer = 1 To IterationCount
For TestItem As Integer = 0 To 10
If Not (Array.IndexOf(PatternArray, TestItem.ToString) =
-1) Then
CountSuccess += 1
End If
Next
Next
sw.Stop()
Console.WriteLine(sw.ElapsedTicks.ToString)
sw.Reset()

Console.Write("method1.1: ")
sw.Start()
For iterations As Integer = 1 To IterationCount
For TestItem As Integer = 0 To 10
If Not (Array.IndexOf(PatternArray, TestItem.ToString) =
(PatternArray.GetLowerBound(0) - 1)) Then
CountSuccess += 1
End If
Next
Next
sw.Stop()
Console.WriteLine(sw.ElapsedTicks.ToString)
sw.Reset()

Console.Write("method1.2: ")
sw.Start()
Dim NotFoundKey As Integer = (PatternArray.GetLowerBound(0) - 1)
For iterations As Integer = 1 To IterationCount
For TestItem As Integer = 0 To 10
If Not (Array.IndexOf(PatternArray, TestItem.ToString) =
NotFoundKey) Then
CountSuccess += 1
End If
Next
Next
sw.Stop()
Console.WriteLine(sw.ElapsedTicks.ToString)
sw.Reset()
method1: 25093
method1.1: 28384
method1.2: 22342
method2: 33875
method3: 32627
Total success: 45000
Total should equal: 2700
 
Hello Phill W.,

Indeed Phil, you can have array's whose lower bounds are non-zero. Check
out the MSDN doco for Array.CreateInstance.

-Boo
 
Back
Top