SQL SELECT Arrays in C#?

Question

Possible Duplicate:
Is it possible to send a collection of ID’s as a ADO.NET SQL parameter?

Duplicate: this is a duplicate of Is it possible to send a collection of ID’s as a ADO.NET SQL parameter? and many others. Please vote to close it and add any additional answers to one of the other duplicates.

Given an array of parameters, I want to write a SELECT/WHERE/IN statement that can handle it. I would have thought I could do something like this.

// Given List<int> listOfLanguageIDs...
string cmdText =
    "SELECT StringID\n" +
    "FROM TRANSLATIONTB\n" +
    "WHERE LanguageID IN(@arrayOfLanguagesIDs)";

using (SqlCommand command = new SqlCommand(cmdText, ConnectionManager.Connection))
{                       
    command.Parameters.Add( "arrayOfLanguagesIDs", SqlDbType.IntArray ).Value = listOfLanguageIDs.ToArray();

...

But alas, this does not seem to be the case, and my searching hasn't turned up anything. Am I asking the wrong questions? Or does everyone really write their own array builder/SqlDbType converter/etc?

This is a major duplicate. It should have its own FAQ. Consider not answering for a few minutes while I find some of the duplicates. — John Saunders, Aug 01 '09 at 01:02
Ok, that didn't take long: http://stackoverflow.com/questions/114504/is-it-possible-to-send-a-collection-of-ids-as-a-ado-net-sql-parameter. — John Saunders, Aug 01 '09 at 01:05

JBrooks · Answer 1 · 2009-08-01T01:33:54.003

0

Its more like:

create  proc [dbo].[GetRequestsItems](@RequestIds as varchar(8000)) as
exec('select ri.id, ri.RequestID, ri.Namefrom RequestItems ri with(nolock)
where RequestID in ('+@RequestIds+')')

and then you pass in a string of ids like: "3, 47, 10, 9"

edited Aug 01 '09 at 01:33

answered Aug 01 '09 at 01:00

JBrooks

9,901
2
28
32

And get all the wonders of SQL injection. – Pavel Minaev Aug 01 '09 at 01:13
1

I don't think you are going to have an input box where the user types in a bunch of ids. My example came from a list that the user could check the checkboxes of the rows they wanted, I then generate the list of IDs based on that. Then bring back the child rows. No opportunity for sql injection there. – JBrooks Aug 01 '09 at 01:31

score 0 · Answer 2 · answered Aug 01 '09 at 01:14

Arrays and Lists in SQL Server 2005 by Erland Sommarskog. The reference article on the subject, with all alternatives deeply covered and discussed. The SO duplicate actually fails to mention this one, which is a shame.

As of 2008 there is also the table valued parameter option.

score 0 · Answer 3 · answered Aug 01 '09 at 01:24

0

I've found it useful to use stored procedures with the array of values passed in as XML:

<languages>
   <id>2001</id>
   <id>2023</id>
   <id>2076</id>
</languages>


CREATE PROCEDURE [dbo].[GetStringsFromLanguageIds]
@languageIds xml
AS
BEGIN

SELECT StringID
FROM TRANSLATIONTB
JOIN @languageIds.nodes('languages/id') R(languages)
ON LanguageID = languages.value('.','int')

END

answered Aug 01 '09 at 01:24

James Conigliaro

3,809
19
22

@James: just curious. Your choice either way, but did you read the part about how this question is likely to be closed as a duplicate? – John Saunders Aug 01 '09 at 01:25

SQL SELECT Arrays in C#?

3 Answers3