[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

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

end

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)
    {
        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();
            dt.Load(cmd.ExecuteReader());
            return dtResults;
        }
        catch(Exception ex)
        {
            throw new Exception("Error executing GetPersonsByIDs.", ex);
        }
    }
}

Source: stackoverflow.com

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

C# - SQLClient - Simplest INSERT

Best free library in .NET to handle data in SQL Server?

How to Connect to SQL Server using LINQ to SQL?

Creating custom assembly in SQL Server throwing assembly not found in database error

Is it normal to use LocalDb in production?

Escaping the escape character does not work – SQL LIKE Operator

Error attaching a database (.mdf file) to SQL Server

How to read SQL Server COUNT from SqlDataReader

SQL Check if table Exists in C#, if not create

Entity framework issue "The table/view ' does not have a primary key defined.