我在我的WCF Web服务中有以下代码片段,根据提供的字典值的格式构建一组where条件。
public static Dictionary<string, string>[] VehicleSearch(Dictionary<string, string> searchParams, int maxResults)
{
string condition = "";
foreach (string key in searchParams.Keys)
{
//Split out the conditional in case multiple options have been set (i.e. SUB;OLDS;TOY)
string[] parameters = searchParams[key].Split(';');
if (parameters.Length == 1)
{
//If a single condition (no choice list) check for wildcards and use a LIKE if necessary
string predicate = parameters[0].Contains('%') ? " AND {0} LIKE @{0}" : " AND {0} = @{0}";
condition += String.Format(predicate, key);
}
else
{
//If a choice list, split out query into an IN condition
condition += string.Format(" AND {0} IN({1})", key, string.Join(", ", parameters));
}
}
SqlCommand cmd = new SqlCommand(String.Format(VEHICLE_QUERY, maxResults, condition));
foreach (string key in searchParams.Keys)
cmd.Parameters.AddWithValue("@" + key, searchParams[key]);
cmd.Prepare();
请注意,字典中的值明确设置为字符串,并且它们是被发送到
AddWithValue
语句中的唯一项。这将生成类似于以下的SQL代码:SELECT TOP 200 MVINumber AS MVINumber
, LicensePlateNumber
, VIN
, VehicleYear
, MakeG
, ModelG
, Color
, Color2
FROM [Vehicle_Description_v]
WHERE 1=1 AND VIN LIKE @VIN
出现错误,报告如下:
System.InvalidOperationException: SqlCommand.Prepare方法要求所有参数都具有明确定义的类型。
我所做的所有搜索都说我需要告诉AddWithValue
准备的值的类型,但是我准备的所有值都是字符串,并且我看到的所有示例在处理字符串时没有执行任何额外操作。我错过了什么吗?
1=1
?考虑它总是为真,而且你使用了AND
,它总是受Like @vin
的影响,这意味着1=1
完全不必要。 - Gonzalo.-