C# with SQL Server SELECT WHERE IN with data list

Question

I have a SQL Server database with person_id and name fields.

In my application I have a array of person_id, I need to get from my database, person records with ids from my array.

Answer 1

If you just want the SQL string, this should 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 think) on the number of items allowed in the IN clause. Also, depending on which version of .NET you're using, string.Join might have different argument types that don't allow a list of integers (are they integers?) to be used. You might have to convert them to strings first before you can join them.

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

Answer 2

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

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

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

create type IntegerValues as table (IntegerValue int)

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

create procedure dbo.GetPersonsByID
(
 @PersonIDs IntegerValues readonly -- must be readonly
)
as begin
    
   select
    p.*
   from [YourPersonTable] as p
       join @PersonIDs as pi
       on pi.[IntegerValue] = p.[Person_ID];

end

Step #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)
    {
        dtPersonIDs.Rows.Add(id);
    }
 
    using(dtPersonIDs)
    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,
            }
        }
    })
    { 
        try
        {
            cnx.Open();
            using (var reader = cmd.ExecuteReader())
            {
                dt.Load(reader);
            }
            return dtResults;
        }
        catch(Exception ex)
        {
            throw new Exception("Error executing GetPersonsByIDs.", ex);
        }
    }
}