473,320 Members | 2,083 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

a vb.net equivalent to the sql IN function?

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
Jun 8 '06 #1
13 3241
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
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

Jun 8 '06 #2

GhostInAK wrote:
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.


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

Jun 8 '06 #3
Larry Lard wrote:
GhostInAK wrote:

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

There are of course other methods.


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.
Jun 8 '06 #4
"hazz" <hazz@sonic_net> schrieb:
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.


<URL:http://groups.google.de/group/microsoft.public.dotnet.languages.vb/msg/d7938dd8bdbd5335>

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

Jun 8 '06 #5

Phill W. wrote:
Larry Lard wrote:
GhostInAK wrote:

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

There are of course other methods.


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?


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

Jun 8 '06 #6
> Larry Lard wrote:
GhostInAK wrote:
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
There are of course other methods.

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?


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.aspx
Jun 8 '06 #7
Jim Wooley wrote:
Larry Lard wrote:
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 .. . .
Here are the results when run in release mode:

method1: 22803
method2: 57436
method3: 88614


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.
Jun 8 '06 #8
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

-Boo
Jun 8 '06 #9
On 2006-06-08, Phill W. <p-.-a-.-w-a-r-d@o-p-e-n-.-a-c-.-u-k> wrote:
Larry Lard wrote:
GhostInAK wrote:

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

There are of course other methods.


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?


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.







Jun 8 '06 #10
I have a wealth of excellent ideas to study. Thank you everyone!
Appreciately,
Greg

"hazz" <hazz@sonic_net> wrote in message
news:Ox**************@TK2MSFTNGP04.phx.gbl...
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

Jun 9 '06 #11
GhostInAK wrote:
The result of Array.IndexOf is ONE LESS than the array's lower bound
if an item is not found.


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.
Jun 9 '06 #12

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.
If Not (Array.IndexOf(PatternArray, TestItem.ToString) = -1) Then

-Boo


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: 27000
Jun 9 '06 #13
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
GhostInAK wrote:
The result of Array.IndexOf is ONE LESS than the array's lower bound
if an item is not found.

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.

Jun 14 '06 #14

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Susan Baker | last post by:
Hi, I'm working on s simple parser and I would like to split up multi-line expressions into single expressions. Is there a way whereby I could slurp the lines into an array (or other container)?...
7
by: am_ggh | last post by:
Is there a PHP equivalent of TCL's "upvar" ? I will appreciate any insights. Andy
6
by: exquisitus | last post by:
Hi all, I'm porting a DOS application to run on Linux. I need to replace this function or use an equivalent. Anyone knows how or where I can get this function's equivalent (or maybe someones...
9
by: Alan Silver | last post by:
Hello, I'm converting some old VB6 code to use with ASP.NET and have come unstuck with the Asc() function. This was used in the old VB6 code to convert a character to its ASCII numeric...
14
by: Allen | last post by:
Greetings, I need to pass a file path to an application. This file path contains long directory and file names. The target application, pdftotext.exe, only accepts short directory and file...
2
by: ramu | last post by:
Hi I have to call a vc++ function in a c program. suppose i have a function dword fun(dword arg1, bstr arg2); in vc++. I have to call this function in c. But I don't have dword datatype in...
7
by: divya | last post by:
What is an equivalent TimeSerial() function of VBscript in the Java Script?? I have two variables hour and minutes. Timeserial(hour,minutes) returns the time hour : minutes. But I want to do...
6
by: Jon Paal | last post by:
what is vb equiv. of ++ shown in C# ?
6
by: openopt | last post by:
Thank you in advance for your response. Dmitrey
18
by: Csaba Gabor | last post by:
Is there a straightforward way of implementing a PHP equivalent to window.setTimeout? In a javascript web app, it's often the case that things are done on an event driven basis. For example,...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.