参数中的动态where子句 [英] Dynamic where clause in parameter

问题描述

我目前正在尝试构建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 :

SqlException'@whereClause'附近的语法不正确

我想做这样的事情是因为我想避免对数据库的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子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！