SQL query... is there a better way?

[Scott Reynolds]

Hi,

I am using following code to read and generate SQL query based on values
stored in ArrayList. But I am not sure if it is the best way... all
suggestions are
welcome!

Thank you!
Scott


Dim SqlQuery As String
Dim SqlCity As String

If Not Cities.Count = 0 Then
For i As Integer = 0 To Cities.Count - 1
If i = 0 Then
SqlCity = "(City =" + Cities(i) + ")"
Else
SqlCity = SqlCity + " Or (City =" + Cities(i) + ")"
End If
Next
SqlCity = "(" & SqlCity & ")"
End If

SqlQuery = "Select * FROM MyTable WHERE " & SqlCity

 

[Guest]

It is better for you to use parameter instead of passing in the value
directly. You can achieve what you wanted with String.Join method though.

Tu-Thach

 

[Kevin Spencer]

Well, I do have one suggestion. First, TURN OPTION STRICT ON!!!!

It really would help you to learn about data types. If you can understand
data types, you can understand how data is stored, and what exactly it is.
This prevents a lot of errors, and increases your programming power
personally. It will also help your app run faster.

An ArrayList is a Collection of objects. As such, it is not typed data per
se (everything inherits from object). This means that you will either have
to cast the proper data type, or have Option Strict turned off in order to
compile your app. When Option Strict is turned off, and you use untyped
data, "Late Binding" occurs at run-time. This means that the .Net platform
has to query the data to find out what it is, which will slow down your
app's performance. In addition, as you can add ANY type of object to the
Collection, it is possible, for example, to assign a DataReader to one of
the values in your ArrayList, which would throw an exception at run-time,
although it would compile quite nicely. I have seen classes that defined
private fields as Integers, and exposed them as public string properties.
Not a problem, unless, of course, something tries to assign "foo" to the
value. "foo" is indeed a string, but it cannot be parsed into a number.
Hence, difficult-to-identify errors can pop up in your code at run-time.

The fastest enumerated type to use in this case would be an array of
strings. Of course, an array is of fixed length, so you need to either know
how many elements are in your array when you declare it, or you have to use
a Collection. You can certainly create a safely-typed Collection, by
inheriting CollectionBase in a class, and adding your own typed
implementations of the Add, Remove, etc. methods.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Neither a follower nor a lender be.

 

[Scott Reynolds]

Hi!

Could you please provide me an example how to use parameter?

Scott

 

[Guest]

For your case you might use this in your loop (C#, not VB):

SqlCommand command = new SqlCommand();
....
for loop..
....
SqlParameter param = new SqlParameter("@City" + i, Cities);
command.Parameters.Add(param);
...
end foor loop
execute your command to retrieve result.

 

[Scott Reynolds]

Thank you Kevin! I'll turn Option Strict On.

Scott

 

[Scott Reynolds]

I don't know if your solution works for me, because I need to achieve
following...

SELECT * FROM Cities WHERE City = 'City1' OR City = 'City2' OR City =
'City4'

depends on what values are stored in array. What do you think?

Scott

For your case you might use this in your loop (C#, not VB):

SqlCommand command = new SqlCommand();
...
for loop..
....
SqlParameter param = new SqlParameter("@City" + i, Cities);
command.Parameters.Add(param);
...
end foor loop
execute your command to retrieve result.

Hi!

Could you please provide me an example how to use parameter?

Scott

 

[Scott Reynolds]

Kevin,

What if I store a ListItem in ArrayList? Is this OK?

Scott

 

[Kevin Spencer]

What if I store a ListItem in ArrayList? Is this OK?

Not if you want your app to perform as best it can. It's really easy to
create a custom strongly-typed Collection. Here's a link to an article that
explains how:

http://msdn.microsoft.com/library/d.../html/vaconcreatingyourowncollectionclass.asp

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Neither a follower nor a lender be.

 

[Guest]

You still have to build the SQL string, but you should be building so that it
looks like

SELECT * FROM Cities WHERE City = @City1 OR City = @City2 OR City = @City4

I don't know if your solution works for me, because I need to achieve
following...

SELECT * FROM Cities WHERE City = 'City1' OR City = 'City2' OR City =
'City4'

depends on what values are stored in array. What do you think?

Scott

For your case you might use this in your loop (C#, not VB):

SqlCommand command = new SqlCommand();
...
for loop..
....
SqlParameter param = new SqlParameter("@City" + i, Cities);
command.Parameters.Add(param);
...
end foor loop
execute your command to retrieve result.

Hi!

Could you please provide me an example how to use parameter?

Scott


It is better for you to use parameter instead of passing in the value
directly. You can achieve what you wanted with String.Join method
though.

Tu-Thach

: