KallDrexx KallDrexx - 4 months ago 8
C# Question

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

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
SqlCommand
s?

Answer

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]));
}