a vb.net equivalent to the sql IN function?

[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

[GhostInAK]

Re: a vb.net equivalent to the sql IN function?

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.GetLowerB ound(0) - 1) = Array.IndexOf(t IDs, "3") Then
' NOT IN ARRAY
Else
' IN ARRAY
End If

There are of course other methods.

Enjoy,
-Boo
[color=blue]
> 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[/color]

[Larry Lard]

Re: a vb.net equivalent to the sql IN function?


GhostInAK wrote:[color=blue]
> 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.GetLowerB ound(0) - 1) = Array.IndexOf(t IDs, "3") Then
> ' NOT IN ARRAY
> Else
> ' IN ARRAY
> End If
>
> There are of course other methods.[/color]

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(theL ist.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 Lard
Replies to group please

[Phill W.]

Re: a vb.net equivalent to the sql IN function?

Larry Lard wrote:[color=blue]
> GhostInAK wrote:[/color]
[color=blue][color=green]
>> Dim tIDStr As String = "1,2,3,4,5,6,7, 8,9"
>> Dim tIDs() As String = tIDStr.Split(", ")
>> If (tIDs.GetLowerB ound(0) - 1) = Array.IndexOf(t IDs, "3") Then
>>
>> There are of course other methods.[/color][/color]

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.

[Herfried K. Wagner [MVP]]

Re: a vb.net equivalent to the sql IN function?

"hazz" <hazz@sonic_net > schrieb:[color=blue]
>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.[/color]

<URL:http://groups.google.d e/group/microsoft.publi c.dotnet.langua ges.vb/msg/d7938dd8bdbd533 5>

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://classicvb.org/petition/>

[Larry Lard]

Re: a vb.net equivalent to the sql IN function?


Phill W. wrote:[color=blue]
> Larry Lard wrote:[color=green]
> > GhostInAK wrote:[/color]
>[color=green][color=darkred]
> >> Dim tIDStr As String = "1,2,3,4,5,6,7, 8,9"
> >> Dim tIDs() As String = tIDStr.Split(", ")
> >> If (tIDs.GetLowerB ound(0) - 1) = Array.IndexOf(t IDs, "3") Then
> >>
> >> There are of course other methods.[/color][/color]
>
> 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?[/color]

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 Lard
Replies to group please

[Jim Wooley]

Re: a vb.net equivalent to the sql IN function?

> Larry Lard wrote:[color=blue]
>[color=green]
>> GhostInAK wrote:
>>[color=darkred]
>>> Dim tIDStr As String = "1,2,3,4,5,6,7, 8,9"
>>> Dim tIDs() As String = tIDStr.Split(", ")
>>> If (tIDs.GetLowerB ound(0) - 1) = Array.IndexOf(t IDs, "3") Then
>>> There are of course other methods.
>>>[/color][/color]
> 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?[/color]

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 maintainablilit y. 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.ToStri ng) =
-1) Then
CountSuccess += 1
End If
Next
Next
sw.Stop()
Console.WriteLi ne(sw.ElapsedTi cks.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.Ind exOf("," & TestItem.ToStri ng & ",") > -1
Then
CountSuccess += 1
End If
Next
Next
sw.Stop()
Console.WriteLi ne(sw.ElapsedTi cks.ToString)

Console.Write(" method3: ")
Dim Test3PatternLis t As IList = Pattern.Split(" ,"c)
sw.Start()
For iterations As Integer = 1 To IterationCount
For TestItem As Integer = 0 To 10
If Test3PatternLis t.Contains(Test Item.ToString) Then
CountSuccess += 1
End If
Next
Next
sw.Stop()
Console.WriteLi ne(sw.ElapsedTi cks.ToString)
Console.WriteLi ne("Total success: " & CountSuccess.To String)
Console.WriteLi ne("Total should equal: " & (9 * 3 * IterationCount) .ToString)
Console.ReadLin e()
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.aspx

[Phill W.]

Re: a vb.net equivalent to the sql IN function?

Jim Wooley wrote:[color=blue][color=green]
>> Larry Lard wrote:[/color][/color]
[color=blue]
> 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.ToStri ng) = -1) Then[/color]
.. . .[color=blue]
> Console.Write(" method2: ")
> If TestPattern.Ind exOf("," & TestItem.ToStri ng & ",") > -1 Then[/color]
.. . .[color=blue]
> Console.Write(" method3: ")
> If Test3PatternLis t.Contains(Test Item.ToString) Then[/color]
.. . .
[color=blue]
> Here are the results when run in release mode:
>
> method1: 22803
> method2: 57436
> method3: 88614[/color]

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.

[GhostInAK]

Re: a vb.net equivalent to the sql IN function?

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.
[color=blue]
> If Not (Array.IndexOf( PatternArray, TestItem.ToStri ng) = -1) Then[/color]


-Boo

[david]

Re: a vb.net equivalent to the sql IN function?

On 2006-06-08, Phill W. <p-.-a-.-w-a-r-d@o-p-e-n-.-a-c-.-u-k> wrote:[color=blue]
> Larry Lard wrote:[color=green]
>> GhostInAK wrote:[/color]
>[color=green][color=darkred]
>>> Dim tIDStr As String = "1,2,3,4,5,6,7, 8,9"
>>> Dim tIDs() As String = tIDStr.Split(", ")
>>> If (tIDs.GetLowerB ound(0) - 1) = Array.IndexOf(t IDs, "3") Then
>>>
>>> There are of course other methods.[/color][/color]
>
> 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?[/color]

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.Cont ains(sTarget) Then
.....


Worrying about efficient code here is probably counterproducti ve. 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.

[hazz]

Re: a vb.net equivalent to the sql IN function?

I have a wealth of excellent ideas to study. Thank you everyone!
Appreciately,
Greg

"hazz" <hazz@sonic_net > wrote in message
news:OxDH4npiGH A.1936@TK2MSFTN GP04.phx.gbl...[color=blue]
>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
>[/color]

[Phill W.]

Re: a vb.net equivalent to the sql IN function?

GhostInAK wrote:
[color=blue]
> The result of Array.IndexOf is ONE LESS than the array's lower bound
> if an item is not found.[/color]

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]

Re: a vb.net equivalent to the sql IN function?


Jim Wooley

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

[color=blue]
> 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.
>[color=green]
>> If Not (Array.IndexOf( PatternArray, TestItem.ToStri ng) = -1) Then
>>[/color]
> -Boo
>[/color]

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.GetLowerB ound: 28384
Using a named variable outside of the loop and using Array.GetLowerB ound:
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.ToStri ng) =
-1) Then
CountSuccess += 1
End If
Next
Next
sw.Stop()
Console.WriteLi ne(sw.ElapsedTi cks.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.ToStri ng) =
(PatternArray.G etLowerBound(0) - 1)) Then
CountSuccess += 1
End If
Next
Next
sw.Stop()
Console.WriteLi ne(sw.ElapsedTi cks.ToString)
sw.Reset()

Console.Write(" method1.2: ")
sw.Start()
Dim NotFoundKey As Integer = (PatternArray.G etLowerBound(0) - 1)
For iterations As Integer = 1 To IterationCount
For TestItem As Integer = 0 To 10
If Not (Array.IndexOf( PatternArray, TestItem.ToStri ng) =
NotFoundKey) Then
CountSuccess += 1
End If
Next
Next
sw.Stop()
Console.WriteLi ne(sw.ElapsedTi cks.ToString)
sw.Reset()
method1: 25093
method1.1: 28384
method1.2: 22342
method2: 33875
method3: 32627
Total success: 45000
Total should equal: 27000

[GhostInAK]

Re: a vb.net equivalent to the sql IN function?

Hello Phill W.,

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

-Boo
[color=blue]
> GhostInAK wrote:
>[color=green]
>> The result of Array.IndexOf is ONE LESS than the array's lower bound
>> if an item is not found.
>>[/color]
> 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.[/color]