我如何参数化一个包含有可变数量参数的IN子句的查询,就像这样?
SELECT * FROM Tags
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC
在这个查询中,参数的数量可以是1到5之间的任意值。
我不喜欢使用专门的存储过程(或XML),但如果有一些特定于SQL Server 2008的优雅方式,我愿意接受。
我如何参数化一个包含有可变数量参数的IN子句的查询,就像这样?
SELECT * FROM Tags
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC
在这个查询中,参数的数量可以是1到5之间的任意值。
我不喜欢使用专门的存储过程(或XML),但如果有一些特定于SQL Server 2008的优雅方式,我愿意接受。
当前回答
您可以通过以下方法以可重用的方式完成此任务-
public static class SqlWhereInParamBuilder
{
public static string BuildWhereInClause<t>(string partialClause, string paramPrefix, IEnumerable<t> parameters)
{
string[] parameterNames = parameters.Select(
(paramText, paramNumber) => "@" + paramPrefix + paramNumber.ToString())
.ToArray();
string inClause = string.Join(",", parameterNames);
string whereInClause = string.Format(partialClause.Trim(), inClause);
return whereInClause;
}
public static void AddParamsToCommand<t>(this SqlCommand cmd, string paramPrefix, IEnumerable<t> parameters)
{
string[] parameterValues = parameters.Select((paramText) => paramText.ToString()).ToArray();
string[] parameterNames = parameterValues.Select(
(paramText, paramNumber) => "@" + paramPrefix + paramNumber.ToString()
).ToArray();
for (int i = 0; i < parameterNames.Length; i++)
{
cmd.Parameters.AddWithValue(parameterNames[i], parameterValues[i]);
}
}
}
要了解更多细节,请参阅这篇博客文章-参数化SQL WHERE IN子句c#
其他回答
对于SQL Server 2008,可以使用表值参数。这有点麻烦,但可以说比我的其他方法更干净。
首先,您必须创建一个类型
CREATE TYPE dbo.TagNamesTableType AS TABLE ( Name nvarchar(50) )
然后,你的ADO。NET代码如下所示:
string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
cmd.CommandText = "SELECT Tags.* FROM Tags JOIN @tagNames as P ON Tags.Name = P.Name";
// value must be IEnumerable<SqlDataRecord>
cmd.Parameters.AddWithValue("@tagNames", tags.AsSqlDataRecord("Name")).SqlDbType = SqlDbType.Structured;
cmd.Parameters["@tagNames"].TypeName = "dbo.TagNamesTableType";
// Extension method for converting IEnumerable<string> to IEnumerable<SqlDataRecord>
public static IEnumerable<SqlDataRecord> AsSqlDataRecord(this IEnumerable<string> values, string columnName) {
if (values == null || !values.Any()) return null; // Annoying, but SqlClient wants null instead of 0 rows
var firstRecord = values.First();
var metadata= new SqlMetaData(columnName, SqlDbType.NVarChar, 50); //50 as per SQL Type
return values.Select(v =>
{
var r = new SqlDataRecord(metadata);
r.SetValues(v);
return r;
});
}
更新 根据@Doug
请尽量避免var metadata = SqlMetaData。InferFromValue (firstRecord columnName);
它设置了第一个值的长度,所以如果第一个值是3个字符,那么它设置的最大长度为3,如果超过3个字符,其他记录将被截断。
因此,请尝试使用:var metadata= new SqlMetaData(columnName, SqlDbType. xml)。NVarChar maxLen);
注意:最大长度为-1。
你可以参数化每个值,就像这样:
string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
string cmdText = "SELECT * FROM Tags WHERE Name IN ({0})";
string[] paramNames = tags.Select(
(s, i) => "@tag" + i.ToString()
).ToArray();
string inClause = string.Join(", ", paramNames);
using (SqlCommand cmd = new SqlCommand(string.Format(cmdText, inClause))) {
for(int i = 0; i < paramNames.Length; i++) {
cmd.Parameters.AddWithValue(paramNames[i], tags[i]);
}
}
这将给你:
cmd.CommandText = "SELECT * FROM Tags WHERE Name IN (@tag0, @tag1, @tag2, @tag3)"
cmd.Parameters["@tag0"] = "ruby"
cmd.Parameters["@tag1"] = "rails"
cmd.Parameters["@tag2"] = "scruffy"
cmd.Parameters["@tag3"] = "rubyonrails"
不,这不是对SQL注入开放的。唯一注入到CommandText中的文本不是基于用户输入的。它完全基于硬编码的“@tag”前缀和数组的索引。索引总是一个整数,不是用户生成的,并且是安全的。
用户输入的值仍然被填充到参数中,因此不存在漏洞。
编辑:
除了注入问题外,要注意构造命令文本以容纳可变数量的参数(如上所述)会阻碍SQL服务器利用缓存查询的能力。最终的结果是,您几乎肯定会在第一时间失去使用参数的价值(而不是仅仅将谓词字符串插入SQL本身)。
并不是说缓存的查询计划没有价值,但在我看来,这个查询还没有复杂到可以从中看到很多好处。虽然编译成本可能接近(甚至超过)执行成本,但仍然是毫秒级的。
如果你有足够的RAM,我希望SQL Server也能缓存一个用于常见参数计数的计划。我认为你总是可以添加五个参数,并让未指定的标签为NULL -查询计划应该是相同的,但这对我来说似乎很难看,我不确定它是否值得进行微观优化(尽管,在Stack Overflow上-它可能非常值得)。
此外,SQL Server 7及后续版本将自动参数化查询,因此从性能的角度来看,使用参数并不是真正必要的——然而,从安全的角度来看,它是至关重要的——特别是对于这样的用户输入数据。
这是一个解决同样问题的交叉帖子。比保留分隔符更健壮-包括转义和嵌套数组,并理解null和空数组。
c# & T-SQL string[]打包/解包实用函数
然后可以连接到表值函数。
这是这个问题的另一个答案。
(新版本发布于6/4/13)。
private static DataSet GetDataSet(SqlConnectionStringBuilder scsb, string strSql, params object[] pars)
{
var ds = new DataSet();
using (var sqlConn = new SqlConnection(scsb.ConnectionString))
{
var sqlParameters = new List<SqlParameter>();
var replacementStrings = new Dictionary<string, string>();
if (pars != null)
{
for (int i = 0; i < pars.Length; i++)
{
if (pars[i] is IEnumerable<object>)
{
List<object> enumerable = (pars[i] as IEnumerable<object>).ToList();
replacementStrings.Add("@" + i, String.Join(",", enumerable.Select((value, pos) => String.Format("@_{0}_{1}", i, pos))));
sqlParameters.AddRange(enumerable.Select((value, pos) => new SqlParameter(String.Format("@_{0}_{1}", i, pos), value ?? DBNull.Value)).ToArray());
}
else
{
sqlParameters.Add(new SqlParameter(String.Format("@{0}", i), pars[i] ?? DBNull.Value));
}
}
}
strSql = replacementStrings.Aggregate(strSql, (current, replacementString) => current.Replace(replacementString.Key, replacementString.Value));
using (var sqlCommand = new SqlCommand(strSql, sqlConn))
{
if (pars != null)
{
sqlCommand.Parameters.AddRange(sqlParameters.ToArray());
}
else
{
//Fail-safe, just in case a user intends to pass a single null parameter
sqlCommand.Parameters.Add(new SqlParameter("@0", DBNull.Value));
}
using (var sqlDataAdapter = new SqlDataAdapter(sqlCommand))
{
sqlDataAdapter.Fill(ds);
}
}
}
return ds;
}
欢呼。
另一种可能的解决方案是,不向存储过程传递可变数量的参数,而是传递一个包含您要查找的名称的字符串,但通过将它们围绕在'<>'中使它们唯一。然后使用PATINDEX查找名称:
SELECT *
FROM Tags
WHERE PATINDEX('%<' + Name + '>%','<jo>,<john>,<scruffy>,<rubyonrails>') > 0