How do I translate a List<string> into a SqlParameter for a Sql In statement?
44,364
Solution 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]));
}
Solution 2
You appear to be trying to pass a multi valued parameter, that SQL syntax isn't going to do what you expect. You may want to pass a table value parameter.
Read this: http://www.sommarskog.se/arrays-in-sql.html#iter-list-of-strings
specifically: http://www.sommarskog.se/arrays-in-sql-2008.html#ListSqlDataRecord
private static void datatable_example() {
string [] custids = {"ALFKI", "BONAP", "CACTU", "FRANK"};
DataTable custid_list = new DataTable();
custid_list.Columns.Add("custid", typeof(String));
foreach (string custid in custids) {
DataRow dr = custid_list.NewRow();
dr["custid"] = custid;
custid_list.Rows.Add(dr);
}
using(SqlConnection cn = setup_connection()) {
using(SqlCommand cmd = cn.CreateCommand()) {
cmd.CommandText =
@"SELECT C.CustomerID, C.CompanyName
FROM Northwind.dbo.Customers C
WHERE C.CustomerID IN (SELECT id.custid FROM @custids id)";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@custids", SqlDbType.Structured);
cmd.Parameters["@custids"].Direction = ParameterDirection.Input;
cmd.Parameters["@custids"].TypeName = "custid_list_tbltype";
cmd.Parameters["@custids"].Value = custid_list;
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
using (DataSet ds = new DataSet()) {
da.Fill(ds);
PrintDataSet(ds);
}
}
}
}
Author by
KallDrexx
Updated on August 06, 2020Comments
-
KallDrexx almost 4 years
I seem to be confused on how to perform an
In
statement with aSqlParameter
. 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 aList<string>
. Whencmd.ExecuteReader()
is called, I get anArgumentException
due to not being able to map aList<string>
to "a known provider type".How do I (safely) perform an
In
query withSqlCommand
s?