C# 有没有比在开始时使用 1=1 更好的方法来动态构建 SQL WHERE 子句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17321281/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a better way to dynamically build an SQL WHERE clause than by using 1=1 at its beginning?
提问by RRM
I'm building some SQLquery in C#. It will differ depending on some conditions stored as variables in the code.
我正在用 C#构建一些SQL查询。它会根据在代码中作为变量存储的某些条件而有所不同。
string Query="SELECT * FROM Table1 WHERE 1=1 ";
if (condition1)
Query += "AND Col1=0 ";
if (condition2)
Query += "AND Col2=1 ";
if (condition3)
Query += "AND Col3=2 ";
It works, but testing 1=1 doesn't seem elegant. If I didn't use it, I would have to remember and check every time if "where" keyword was already added or not to the query.
它有效，但测试 1=1 似乎并不优雅。如果我不使用它，我将不得不每次都记住并检查“where”关键字是否已添加到查询中。
Is there a nicer solution?
有更好的解决方案吗？
回答by Ahmed KRAIEM
Save the conditions in a list:
将条件保存在列表中：
List<string> conditions = new List<string>();
if (condition1) conditions.Add("Col1=0");
//...
if (conditions.Any())
Query += " WHERE " + string.Join(" AND ", conditions.ToArray());
回答by CodeCaster
One solution is to simply not write queries manually by appending strings. You could use an ORM, like Entity Framework, and with LINQ to Entities use the features the language and framework offer you:
一种解决方案是不通过附加字符串来手动编写查询。您可以使用 ORM，例如Entity Framework，并通过 LINQ to Entities 使用该语言和框架为您提供的功能：
using (var dbContext = new MyDbContext())
{
IQueryable<Table1Item> query = dbContext.Table1;
if (condition1)
{
query = query.Where(c => c.Col1 == 0);
}
if (condition2)
{
query = query.Where(c => c.Col2 == 1);
}
if (condition3)
{
query = query.Where(c => c.Col3 == 2);
}
PrintResults(query);
}
回答by Anshuman
Use this:
用这个：
string Query="SELECT * FROM Table1 WHERE ";
string QuerySub;
if (condition1) QuerySub+="AND Col1=0 ";
if (condition2) QuerySub+="AND Col2=1 ";
if (condition3) QuerySub+="AND Col3=2 ";
if (QuerySub.StartsWith("AND"))
QuerySub = QuerySub.TrimStart("AND".ToCharArray());
Query = Query + QuerySub;
if (Query.EndsWith("WHERE "))
Query = Query.TrimEnd("WHERE ".ToCharArray());
回答by Dariusz
There is another solution, which may also not be elegant, but works and solves the problem:
还有另一种解决方案，它也可能不优雅，但可以解决问题：
String query = "SELECT * FROM Table1";
List<string> conditions = new List<string>();
// ... fill the conditions
string joiner = " WHERE ";
foreach (string condition in conditions) {
query += joiner + condition;
joiner = " AND "
}
For:
为了：
- empty conditions list, the result will be simply
SELECT * FROM Table1, - a single condition it will be
SELECT * FROM Table1 WHERE cond1 - each following condition will generate additional
AND condN
- 空条件列表，结果将很简单
SELECT * FROM Table1， - 一个单一的条件，它将是
SELECT * FROM Table1 WHERE cond1 - 以下每个条件都会产生额外的
AND condN
回答by Rémi
Depending on the condition, it might be possible to use boolean logic in the query. Something like this :
根据条件，可能可以在查询中使用布尔逻辑。像这样的事情：
string Query="SELECT * FROM Table1 " +
"WHERE (condition1 = @test1 AND Col1=0) "+
"AND (condition2 = @test2 AND Col2=1) "+
"AND (condition3 = @test3 AND Col3=2) ";
回答by mckeejm
If this is SQL Server, you can make this code much cleaner.
如果这是SQL Server，您可以使此代码更清晰。
This also assumes a known number of parameters, which may be a poor assumption when I think about the possibilities.
这也假设了已知数量的参数，当我考虑可能性时，这可能是一个糟糕的假设。
In C#, you would use:
在 C# 中，您将使用：
using (SqlConnection conn = new SqlConnection("connection string"))
{
conn.Open();
SqlCommand command = new SqlCommand()
{
CommandText = "dbo.sample_proc",
Connection = conn,
CommandType = CommandType.StoredProcedure
};
if (condition1)
command.Parameters.Add(new SqlParameter("Condition1", condition1Value));
if (condition2)
command.Parameters.Add(new SqlParameter("Condition2", condition2Value));
if (condition3)
command.Parameters.Add(new SqlParameter("Condition3", condition3Value));
IDataReader reader = command.ExecuteReader();
while(reader.Read())
{
}
conn.Close();
}
And then on the SQL side:
然后在 SQL 端：
CREATE PROCEDURE dbo.sample_proc
(
--using varchar(50) generically
-- "= NULL" makes them all optional parameters
@Condition1 varchar(50) = NULL
@Condition2 varchar(50) = NULL
@Condition3 varchar(50) = NULL
)
AS
BEGIN
/*
check that the value of the parameter
matches the related column or that the
parameter value was not specified. This
works as long as you are not querying for
a specific column to be null.*/
SELECT *
FROM SampleTable
WHERE (Col1 = @Condition1 OR @Condition1 IS NULL)
AND (Col2 = @Condition2 OR @Condition2 IS NULL)
AND (Col3 = @Condition3 OR @Condition3 IS NULL)
OPTION (RECOMPILE)
--OPTION(RECOMPILE) forces the query plan to remain effectively uncached
END
回答by Alan Barber
A slight bit of overkill in this simple case but I've used code similar to this in the past.
在这个简单的例子中有点矫枉过正，但我过去使用过类似的代码。
Create a function
创建函数
string AddCondition(string clause, string appender, string condition)
{
if (clause.Length <= 0)
{
return String.Format("WHERE {0}",condition);
}
return string.Format("{0} {1} {2}", clause, appender, condition);
}
Use it like this
像这样使用它
string query = "SELECT * FROM Table1 {0}";
string whereClause = string.Empty;
if (condition 1)
whereClause = AddCondition(whereClause, "AND", "Col=1");
if (condition 2)
whereClause = AddCondition(whereClause, "AND", "Col2=2");
string finalQuery = String.Format(query, whereClause);
This way if no conditions are found you don't even bother loading a where statement in the query and save the sql server a micro-second of processing the junk where clause when it parses the sql statement.
这样，如果没有找到条件，您甚至不必费心在查询中加载 where 语句，并在解析 sql 语句时为 sql server 节省处理垃圾 where 子句的微秒时间。
回答by trevorgrayson
The quickest literal solution to what you're asking that I can think of is this:
我能想到的最快的文字解决方案是：
string Query="SELECT * FROM Table1";
string Conditions = "";
if (condition1) Conditions+="AND Col1=0 ";
if (condition2) Conditions+="AND Col2=1 ";
if (condition3) Conditions+="AND Col3=2 ";
if (Conditions.Length > 0)
Query+=" WHERE " + Conditions.Substring(3);
It doesn't seem elegant, sure, to which I would refer you to CodeCaster's recommendation of using an ORM. But if you think about what this is doing here, you're really not worried about 'wasting' 4 characters of memory, and it's really quick for a computer to move a pointer 4 places.
当然，这看起来并不优雅，我会向您推荐 CodeCaster 使用 ORM 的建议。但是如果你想想这里做了什么，你真的不担心“浪费”4个字符的内存，而且计算机将指针移动4个位置真的很快。
If you have the time to learn how to use an ORM, it could really pay off for you. But in regards to this, if you're trying to keep that additional condition from hitting the SQL db, this will do it for you.
如果您有时间学习如何使用 ORM，它真的可以为您带来回报。但就此而言，如果您试图阻止该附加条件命中 SQL 数据库，这将为您做到。
回答by NeverHopeless
Using stringfunction you can also do it this way:
使用string函数你也可以这样做：
string Query = "select * from Table1";
if (condition1) WhereClause += " Col1 = @param1 AND "; // <---- put conditional operator at the end
if (condition2) WhereClause += " Col1 = @param2 OR ";
WhereClause = WhereClause.Trim();
if (!string.IsNullOrEmpty(WhereClause))
Query = Query + " WHERE " + WhereClause.Remove(WhereClause.LastIndexOf(" "));
// else
// no condition meets the criteria leave the QUERY without a WHERE clause
I personally feel easy to to remove the conditional element(s) at the end, since its position is easy to predict.
我个人觉得在最后删除条件元素很容易，因为它的位置很容易预测。
回答by milesma
Just append two lines at back.
只需在后面附加两行。
string Query="SELECT * FROM Table1 WHERE 1=1 ";
if (condition1) Query+="AND Col1=0 ";
if (condition2) Query+="AND Col2=1 ";
if (condition3) Query+="AND Col3=2 ";
Query.Replace("1=1 AND ", "");
Query.Replace(" WHERE 1=1 ", "");
E.g.
例如
SELECT * FROM Table1 WHERE 1=1 AND Col1=0 AND Col2=1 AND Col3=2
will become to
将成为
SELECT * FROM Table1 WHERE Col1=0 AND Col2=1 AND Col3=2
While
尽管
SELECT * FROM Table1 WHERE 1=1
will become to
将成为
SELECT * FROM Table1
=====================================
======================================
Thanks for pointing out a flaw of this solution:
感谢您指出此解决方案的缺陷：
"This could break the query if, for any reason, one of the conditions contains the text "1=1 AND " or " WHERE 1=1 ". This could be the case if the condition contains a subquery or tries to check if some column contains this text, for example. Maybe this isn't a problem in your case but you should keep it in mind… "
“如果出于任何原因，其中一个条件包含文本“1=1 AND”或“WHERE 1=1”，这可能会中断查询。如果条件包含子查询或尝试检查某些例如，列包含此文本。也许这在您的情况下不是问题，但您应该记住……”
In order to get rid of this issue, we need to distinguish the "main" WHERE 1=1and those from subquery, which is easy:
为了摆脱这个问题，我们需要区分“主” WHERE 1=1和子查询，这很容易：
Simply make the "main" WHEREspecial: I would append a "$" sign
只需让“主要” WHERE特殊：我会附加一个“$”符号
string Query="SELECT * FROM Table1 WHERE$ 1=1 ";
if (condition1) Query+="AND Col1=0 ";
if (condition2) Query+="AND Col2=1 ";
if (condition3) Query+="AND Col3=2 ";
Then still append two lines:
然后仍然附加两行：
Query.Replace("WHERE$ 1=1 AND ", "WHERE ");
Query.Replace(" WHERE$ 1=1 ", "");
