How do I translate a List<string> into a SqlParameter for a Sql In statement? [duplicate]

Question

I seem to be confused on how to perform an In statement with a SqlParameter. So far I have the following code:

cmd.CommandText = "Select dscr from system_settings where setting in @settings"; cmd.Connection = conn; cmd.Parameters.Add(new SqlParameter("@settings", settingList));  reader = cmd.ExecuteReader(); 

settingsList is a List<string>. When cmd.ExecuteReader() is called, I get an ArgumentException due to not being able to map a List<string> to "a known provider type".

How do I (safely) perform an In query with SqlCommands?

Answer 1

You could try something like this:

string sql = "SELECT dscr FROM system_settings WHERE setting IN ({0})"; string[] paramArray = settingList.Select((x, i) => "@settings" + i).ToArray(); cmd.CommandText = string.Format(sql, string.Join(",", paramArray));  for (int i = 0; i < settingList.Count; ++i) {     cmd.Parameters.Add(new SqlParameter("@settings" + i, settingList[i])); }