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

[Tu-Thach]

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

"Scott Reynolds" wrote:

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

[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" <sc***@it-xenius.co.uk> wrote in message
news:uB******** ******@TK2MSFTN GP12.phx.gbl...

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

[Scott Reynolds]

Hi!

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

Scott
"Tu-Thach" <Tu*****@discus sions.microsoft .com> wrote in message
news:8F******** *************** ***********@mic rosoft.com...

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

"Scott Reynolds" wrote:

[Tu-Thach]

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[i]);
command.Paramet ers.Add(param);
...
end foor loop
execute your command to retrieve result.
"Scott Reynolds" wrote:

Hi!

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

Scott
"Tu-Thach" <Tu*****@discus sions.microsoft .com> wrote in message
news:8F******** *************** ***********@mic rosoft.com...

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

"Scott Reynolds" wrote:

[Scott Reynolds]

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

Scott

"Kevin Spencer" <ke***@DIESPAMM ERSDIEtakempis. com> wrote in message
news:u$******** ******@TK2MSFTN GP09.phx.gbl...

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" <sc***@it-xenius.co.uk> wrote in message
news:uB******** ******@TK2MSFTN GP12.phx.gbl...

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

[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

"Tu-Thach" <Tu*****@discus sions.microsoft .com> wrote in message
news:5C******** *************** ***********@mic rosoft.com...

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[i]);
command.Paramet ers.Add(param);
...
end foor loop
execute your command to retrieve result.
"Scott Reynolds" wrote:

Hi!

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

Scott
"Tu-Thach" <Tu*****@discus sions.microsoft .com> wrote in message
news:8F******** *************** ***********@mic rosoft.com...

> 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
>
> "Scott Reynolds" wrote:

[Scott Reynolds]

Kevin,

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

Scott

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" <sc***@it-xenius.co.uk> wrote in message
news:uB******** ******@TK2MSFTN GP12.phx.gbl...

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

[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/de...ctionclass.asp

--
HTH,

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

"Scott Reynolds" <sc***@it-xenius.co.uk> wrote in message
news:ea******** ******@TK2MSFTN GP11.phx.gbl...

Kevin,

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

Scott

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" <sc***@it-xenius.co.uk> wrote in message
news:uB******** ******@TK2MSFTN GP12.phx.gbl...

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

[Tu-Thach]

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
"Scott Reynolds" wrote:

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

"Tu-Thach" <Tu*****@discus sions.microsoft .com> wrote in message
news:5C******** *************** ***********@mic rosoft.com...

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[i]);
command.Paramet ers.Add(param);
...
end foor loop
execute your command to retrieve result.
"Scott Reynolds" wrote:

Hi!

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

Scott
"Tu-Thach" <Tu*****@discus sions.microsoft .com> wrote in message
news:8F******** *************** ***********@mic rosoft.com...
> 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
>
> "Scott Reynolds" wrote: