CODEWITHSUNDEEP
.netsqlado.net
JoelHess
JoelHess

Reputation: 1207

SQL Select where values in List<string>

Is there a way that I can create query against a data source (could be sql, oracle or access) that has a where clause that points to an ArrayList or List?

example: 

Select * from Table where RecordID in (RecordIDList)

I've seen some ways to do it with Linq, but I'd rather not resort to it if it's avoidable.

Upvotes: 19

Views: 160577

Answers (8)

cjk
cjk

Reputation: 46425

You can iterate over your array and add a parameter to your SQL for each. This gets you around SQL injection, but make sure you use a StringBuilder rather than string concatenation as you build up your SQL statement.

e.g.

StringBuilder sql = new StringBuilder("select * from Table where ");
for  (int i = 0; i < RecordIDList.Length; i++)
{
    if (i > 0) sql.Append (" OR ");
    sql.Append(" RecordID = @param" + i.ToString() + " ");
    IDbDataParameter param = new Param();
    param.value etc.
}

Upvotes: 4

Chisha
Chisha

Reputation: 1

You can do it like this with an example string list of colors, separated by a comma:

SELECT value
FROM STRING_SPLIT('red,green,blue', ',')
WHERE TRIM(value) <> ''

Upvotes: 0

Amy B
Amy B

Reputation: 110111

Linq to Sql. RecordList should be a List<T>, not an ArrayList or an IList<T>

IEnumerable<TableRow> query =
  from t in db.Table
  where RecordList.Any(r => t.RecordId == r)
  select t;

This will generate sql with parameters:

SELECT *
FROM Table
WHERE RecordId in (@p0, @p1, @p2, @p3, @p4)

Linq will generate as many parameters as are needed. Some database implementations are limited in the number of parameters that can be accepted. SqlServer2005's limit is a little over 2000 parameters... so don't use a list with more than 2000 elements.

Upvotes: 9

Manu
Manu

Reputation: 29143

If you use dynamic SQL, you can send the content of the parentheses as a literal comma-separated list. Otherwise you can use an XML variable for sending multiple values. See http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Upvotes: 0

GordonBy
GordonBy

Reputation: 3407

I've only done what your trying to do with a comma separated list

Select * from Table where RecordID in (1,2,34,45,76,34,457,34)

or where the results come from a separate select

Select * from Table where RecordID in (select recordId from otherTable where afieldtype=1)

I'm pretty sure you can't achieve what you're after....

Upvotes: 21

erikkallen
erikkallen

Reputation: 34401

You can write a table-valued user-defined function that takes a list of IDs and creates a table, then join agains the result of this function. This article by Erland Sommarskog describes how to do it.

Or, you could use table parameters, new in SQL server 2008 (I think).

Or, as Manu said, you can use XML.

However, I advice against using the IN String.Join approach in the accepted answer since it is like asking for SQL injection.

Upvotes: 1

Andrew Hare
Andrew Hare

Reputation: 351526

You could use String.Join. Try something like this:

String query = "select * from table where RecordId in ({0});";
String formatted = String.Format(query, String.Join(",", list.ToArray()));

As a side note this will not protect you against SQL injection - hopefully this example will point you in the right direction.

Upvotes: 13

andleer
andleer

Reputation: 22578

Using Linq to SQL and I assume the Entity Framework you can do the following:

dataContext.Table.Where(t => RecordIDList.Contains(t.RecordID));

Will work with both List<> and ArrayList as they both implement IEnumerable.

Linq and Lambdas require that you reverse the Contains method but it does work and generates a SQL "IN ()" clause.

Upvotes: 0

Related Questions