[ACCEPTED]-C# with SQL Server SELECT WHERE IN with data list-sql-server

Accepted answer
Score: 10

If you just want the SQL string, this should 14 work:

var sql = "select Person_Name from tbl_Person_Info where Person_Id in ("
               + string.Join( ",", PersonIdList )
               + ")";

Note that there is a limit (2000, I 13 think) on the number of items allowed in 12 the IN clause. Also, depending on which 11 version of .NET you're using, string.Join might have 10 different argument types that don't allow 9 a list of integers (are they integers?) to 8 be used. You might have to convert them 7 to strings first before you can join them.

EDIT: Please 6 be aware that if the PersonIdList items 5 come from user input (and are strings), this 4 is very dangerous. I'd suggest using a 3 newer .NET technology in any event that 2 would allow you to handle this much more 1 safely -- such as LINQ or EF.

Score: 4

Now behold the awful power of the table-valued parameter! (provided 14 that you're using SQL Server 2008)

Essentially, this 13 is the means to pass your array of integers, properly 12 typed, to a stored procedure... i.e.: no 11 string concatenation / sql injection. Mainly 10 this all centers around creating a SQL table-type 9 having a single integer column... then you 8 just pass a .NET DataTable (of the same 7 structure) to a stored procedure expecting 6 said type.

Step #1: Create a table-type (on 5 SQL Server) for passing a series of integers. You 4 only need to do this once so don't go placing 3 it in your stored procedure.

create type IntegerValues as table (IntegerValue int)

Step #2: Create 2 your stored procedure (on SQL Server).

create procedure dbo.GetPersonsByID
 @PersonIDs IntegerValues readonly -- must be readonly
as begin

   from [YourPersonTable] as p
       join @PersonIDs as pi
       on pi.[IntegerValue] = p.[Person_ID];


Step 1 #3: Call your stored procedure from C#

// Written from my laptop straight into the textarea... so, it's untested.
public DataTable GetPersonsByIDs(int[] personIDs)
    var dtResults = new DataTable();
    var dtPersonIDs = new DataTable();

    dtPersonIDs.Columns.Add("IntegerValue", typeof(int));

    foreach(int id in personIDs)

    using(var cnx = new SqlConnection("YourConnectionString"))
    using(var cmd = new SqlCommand {
        Connection = cnx,
        CommandText = "dbo.GetPersonsByIDs",
        CommandType = CommandType.StoredProcedure,
        Parameters = {
            new SqlParameter {
                ParameterName = "PersonIDs",
                SqlDbType = SqlDbType.Structured, // must be structured
                Value = dtPersonIDs,
            return dtResults;
        catch(Exception ex)
            throw new Exception("Error executing GetPersonsByIDs.", ex);

More Related questions