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.
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....
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.
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.
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.
}
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.
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.
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
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) <> ''
