How to select specific rows of a SQL table without looping in .net

Suppose I have a table called "Na开发者_如何学Cmes"

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.

---

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);

---

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)