ADO.net Select with multiply value for a parameter?

[Guest]

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??

 

[Mary Chipman]

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