如何翻译一个List＆LT;串＆GT;成一个SQL在语句的SqlParameter？ [英] How do I translate a List<string> into a SqlParameter for a Sql In statement?

问题描述

我似乎对如何使用的SqlParameter 执行在语句混淆。到目前为止，我有以下的code：

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 是列表＆LT;串＆GT; 。当 cmd.ExecuteReader（）被调用时，我得到一个的ArgumentException ，由于不能够映射列表＆LT;字符串方式＆gt; 来已知供应商类型

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".

我如何（安全）与执行在查询的SqlCommand S'

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

推荐答案

您可以尝试这样的事：

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

这篇关于如何翻译一个List＆LT;串＆GT;成一个SQL在语句的SqlParameter？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！