0

I have this code

List<int> userIDList = new List<int>() { 1,2,3,4,5 };
string Command = "SELECT FirstName, LastName FROM User WHERE UserID in @UserID;";
using (SqlConnection myConnection = new SqlConnection(ConnectionString))
{
    using (SqlDataAdapter myDataAdapter = new SqlDataAdapter(Command, myConnection))
    {
        myDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@UserID", userIDList));
        DataTable dt = new DataTable();
        myDataAdapter.Fill(dt);
    }
}

But this doesn't work because i can't attach a List into a Parameter

new SqlParameter("@UserID", userIDList)

What is the way to go here?

Impostor
  • 2,080
  • 22
  • 43

1 Answers1

0

First you need to convert List to comma separated string

myDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@UserID",
                                               String.join(",",userIDList)));;

then use this string in query like this

string Command = "SELECT FirstName, LastName FROM User WHERE UserID in (@UserID);";
Jaydip Jadhav
  • 12,179
  • 6
  • 24
  • 40
  • 1
    You need one parameter for each value that you pass to `IN`. Like in these answers here: http://stackoverflow.com/a/2377651/284240 But a better(more efficient) way is to use table-valued parameters. – Tim Schmelter Jul 14 '16 at 11:54
  • still both work in similar manner and they both will yield same output – Jaydip Jadhav Jul 14 '16 at 11:58
  • I have my doubts. Because `UserID` seems to be an `int` column but you are passing a `string`/`varchar`, so i expect the database to refuse it as valid argument. At least if you pass more than one. – Tim Schmelter Jul 14 '16 at 12:05
  • May be what you are trying to say is true, but once i have implemented same in my project and this works perfectly, and thanks for info. – Jaydip Jadhav Jul 14 '16 at 12:08