0

Suppose I have a table called "Names"

ID | Name
---+--------------
1  | Bob
2  | Billy
3  | James
4  | John
5  | Tom
and on and on with thousands of names...

Suppose that I have written the following .net code to retrieve the names:

   public DataSet selectFromNames()
    {
        const string SQL_STATEMENT =
        @"SELECT Id, Name From Names;";

        Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>("MyConnection");

        DbCommand command = db.GetSqlStringCommand(SQL_STATEMENT);
        command.CommandType = CommandType.Text;
        command.CommandText = SQL_STATEMENT;

        DataSet ds = db.ExecuteDataSet(command);
        return ds;
    }

That works fine. What if I want a function that has an integer array parameter specifying the rows of the names I want to retrieve? How would I write that query?

I'm familiar with passing parameters as @parameter, but I have no idea how to pass in an array with using the string builder to write something like where ID in (1,2,3) which is ugly and error-prone.

Vivian River
  • 31,198
  • 62
  • 198
  • 313
  • possible duplicate of [Parameterizing a SQL IN clause?](http://stackoverflow.com/questions/337704/parameterizing-a-sql-in-clause) If SQL Server you might find this useful [http://www.sommarskog.se/arrays-in-sql.html](http://www.sommarskog.se/arrays-in-sql.html) – Martin Smith Apr 27 '11 at 21:07

2 Answers2

0

Suggest using a SQL Server UDF to help you. Here's an article explaining how to implement this.

Basically you'd create a UDF to split a varchar() into a table variable. Let's call it dbo.MySplit().

Then you could:

string sql = "SELECT * FROM Table WHERE ID IN (SELECT Item FROM dbo.MySplitUDF(@csv));";
cmd.CommandText = sql;
cmd.Parameters.AddWithValue("@csv", string.Join(",",myIntegerArray);
p.campbell
  • 98,673
  • 67
  • 256
  • 322
0

how about this (using LINQtoSQL) ? 

//...  
int[] idList = new int[] { 1, 2, 3, 4 };
var myNames = from n in db.Names
              where idList.Contains(n.ID)
              select n;

(lifted from Creating IN Queries With Linq To SQL)

dizzwave
  • 4,013
  • 1
  • 19
  • 16