469,946 Members | 1,997 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,946 developers. It's quick & easy.

ADO.net Select with multiply value for a parameter?

Hi All,

I am currently developing a module where the user will input multiple items
(as many as 20-600) and I am suppose to retrieve it from a sql database all
the information and update it with some calculation which is quite intense.

Currently, I am using a loop to select the information from the table

For i = 0 to SelectedParts.count - 1
....'typing from memory, syntax maybe off but u get the idea
PartSelectCommand.Parameters("PartID").value = XXXX(i)
PartSelectCommand.fill(PartDS)
MainPartDS.merge(PartDS)
....
next

This is working fine, unfortunately, the calculations is again based on
information from as many as 20- 25 other tables. So efficiency and bandwidth
become a major problem. So any ideas to avoid using the loop? I have try
the following ideas

1. Using the select statement
"Select * from table where PartID in (" & combineStr & ")"
where the combineStr is a string combining all the partID separated
with comma and single quote
----- result -----> success, however, company policy is to use
parameters :(
-----> also, the statment is very long as the PartID is made up of GUID
-----> partID list is read from a text file, there is no garuantee that
the number of item will not cause the SQL statement to hit the maximum length
of 8000 chars.

2. Using the select statement
"Select * from table where PartID in @CombineStr"
where the combineStr is a string combining all the partNo separated
with comma and single quote
----- result -----> fail, whole string is considered on item, return
zero result
so any ideas??

--
Mechanical engineer by training...
Programmer by accident....
Jammmm....inc
Nov 21 '05 #1
1 1880
Your best bet in terms of efficiency would be to pass all of the
values as a delimited string to a stored procedure which does the
actual processing. T-SQL code in your stored procedure would validate
and parse the varchar input parameter and do all the necessary work.
If you perform each operation individually inside of a loop, it's n
number of calls going back and forth from the client code to the
server, which is not going to be as efficient as a single call to a
stored procedure.

--Mary

On Mon, 13 Dec 2004 20:11:01 -0800, "jamminc"
<ja*****@discussions.microsoft.com> wrote:
Hi All,

I am currently developing a module where the user will input multiple items
(as many as 20-600) and I am suppose to retrieve it from a sql database all
the information and update it with some calculation which is quite intense.

Currently, I am using a loop to select the information from the table

For i = 0 to SelectedParts.count - 1
....'typing from memory, syntax maybe off but u get the idea
PartSelectCommand.Parameters("PartID").value = XXXX(i)
PartSelectCommand.fill(PartDS)
MainPartDS.merge(PartDS)
....
next

This is working fine, unfortunately, the calculations is again based on
information from as many as 20- 25 other tables. So efficiency and bandwidth
become a major problem. So any ideas to avoid using the loop? I have try
the following ideas

1. Using the select statement
"Select * from table where PartID in (" & combineStr & ")"
where the combineStr is a string combining all the partID separated
with comma and single quote
----- result -----> success, however, company policy is to use
parameters :(
-----> also, the statment is very long as the PartID is made up of GUID
-----> partID list is read from a text file, there is no garuantee that
the number of item will not cause the SQL statement to hit the maximum length
of 8000 chars.

2. Using the select statement
"Select * from table where PartID in @CombineStr"
where the combineStr is a string combining all the partNo separated
with comma and single quote
----- result -----> fail, whole string is considered on item, return
zero result
so any ideas??


Nov 21 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

21 posts views Thread by John Fabiani | last post: by
6 posts views Thread by Ben Hallert | last post: by
4 posts views Thread by Oak Hall | last post: by
5 posts views Thread by John | last post: by
20 posts views Thread by jjmillertime | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.