如何翻译列表<字符串>为 Sql In 语句转换为 SqlParameter?

How do I translate a Listlt;stringgt; into a SqlParameter for a Sql In statement?(如何翻译列表lt;字符串gt;为 Sql In 语句转换为 SqlParameter?)

问题描述

我似乎对如何使用 SqlParameter 执行 In 语句感到困惑.到目前为止，我有以下代码:

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 是一个 List.当调用 cmd.ExecuteReader() 时，由于无法将 List 映射到已知提供者类型".

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

我如何(安全地)使用 SqlCommands 执行 In 查询?

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

这篇关于如何翻译列表<字符串>为 Sql In 语句转换为 SqlParameter?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！