问题描述
我目前正在尝试构建SqlCommand
的where子句.
I am currently trying to build up the where clause of an SqlCommand
.
类似的东西
myCommand.CommandText = "SELECT * " +
"FROM TABLE1 " +
"@whereClause";
//I build up the where clause with a StringBuilder
myCommand.Parameters.AddWithValue("@whereClause" theClause.ToString());
但是似乎不可能做到这一点.我有一个例外:
But it doesn't seem like this is possible. I got the exception :
我想做这样的事情是因为我想避免对数据库的X调用,这样我就将排序和过滤留给了服务器.
The reason I want to do something like this is because I want to avoid X call to the database and this way I leave sorting and filtering to the server.
反正有做类似的事情吗?
Is there anyway to do something similar to this?
/edit:where子句看起来像这样WHERE (TABLE1.COL1 = 'the value' OR TABLE1.COL1 = 'another value' OR TABLE1.COL1 = 'this value' ... )
/edit : The where clause would look something like this WHERE (TABLE1.COL1 = 'the value' OR TABLE1.COL1 = 'another value' OR TABLE1.COL1 = 'this value' ... )
/edit最后,这是由于一个愚蠢的错字错误...在我更改为不使用参数查询之后.我将对那些帮助我解决问题的答案表示敬意.我会标记为答案,即使它没有解决我(愚蠢的)错误,更能解决我的情况
/edit Finaly this was due to a stupid typo error... after I changed to not use the parametrize query. I'll upvote those answer who helped my out. I will mark as answer what was to closer to fix my situation even if it didn't fixed my (stupid) bug
推荐答案
似乎您正在尝试将整个WHERE子句添加为参数-不起作用!
It seems you're trying to add the entire WHERE clause as a parameter - that won't work!
所以假设您需要构建这样的东西
So suppose you need to build something like this
SELECT * from TABLE1 WHERE Field1=@Field1Value and Field2=@Field2Value
并假设
- 您有一个
List<WhereField>
字段要包含在WHERE子句中 - 并且所有子句都被AND在一起
- 和
WhereField
看起来像这样
- you have a
List<WhereField>
of fields to include in the WHERE clause - and that all clauses are ANDed together
- and
WhereField
looks something like this
public class WhereField
{
public string FieldName{get;set;}
public object FieldValue{get;set;}
public string ComparisonOperator{get;set;}
}
然后您会看到以下内容:
then you have something like this:
var whereClause = new StringBuilder();
foreach (var field in WhereFields)
{
whereClause.Append(field.FieldName)
.Append(field.ComparisonOperator)
.Append("@")
.Append(field.FieldName).Append("Value")
.Append (" AND ");
//add the parameter as well:
myCommand.Parameters.AddWithValue("",field.FieldName+"Value");
}
//cleanly close the where clause
whereClause.Append("1=1");
现在您可以执行
myCommand.CommandText = "SELECT * " +
"FROM TABLE1 WHERE " + whereClause.ToString();
这篇关于参数中的动态where子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!