有没有比在开头使用1 = 1更好的动态构建 SQLWHERE 子句的方法？

小开

在列表中保存条件:

List<string> conditions = new List<string>();


if (condition1) conditions.Add("Col1=0");
//...
if (conditions.Any())
Query += " WHERE " + string.Join(" AND ", conditions.ToArray());

小开

一种解决方案是不通过附加字符串手动编写查询。你可以使用 ORM，比如实体架构，并且使用 LINQtoEntity 使用语言和框架提供给你的特性:

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);
}

小开

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());

小开

还有另一种解决方案，也许不够优雅，但能够奏效并解决问题:

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 "
}

致:

空条件列表，结果将是简单的 SELECT * FROM Table1,
一个条件就是 SELECT * FROM Table1 WHERE cond1
每个以下条件将产生额外的 AND condN

小开

根据条件的不同，可以在查询中使用布尔逻辑:

string Query="SELECT * FROM Table1  " +
"WHERE (condition1 = @test1 AND Col1=0) "+
"AND (condition2 = @test2 AND Col2=1) "+
"AND (condition3 = @test3 AND Col3=2) ";

小开

如果这是 SQL Server，您可以使这个代码更清晰。

This also assumes a known number of parameters, which may be a poor assumption when I think about the possibilities.

在 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();
}

然后在 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

小开

在这个简单的例子中有点过分，但是我在过去使用过类似的代码。

创建一个函数

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);
}

像这样使用它

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);

这样，如果没有发现任何条件，甚至不必费心在查询中加载 where 语句，在 sql 服务器解析 sql 语句时，可以为它节省处理垃圾 where 子句的微秒时间。

小开

对于你的问题，我能想到的最快的解决办法是:

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);

当然，它看起来并不优雅，我建议您参考 CodeCaster 关于使用 ORM 的建议。但是如果你想想这是怎么回事，你真的不用担心“浪费”4个字符的内存，而且对于一台计算机来说，把一个指针移动到4个地方真的很快。

如果您有时间学习如何使用 ORM，它可以真正为您带来回报。但是关于这一点，如果您试图阻止这个附加条件触发 SQL 数据库，那么这将为您做到这一点。

小开

使用 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

我个人觉得很容易删除条件元素在最后，因为它的位置很容易预测。

小开

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 ", "");

例如。

SELECT * FROM Table1 WHERE 1=1 AND Col1=0 AND Col2=1 AND Col3=2

将成为

SELECT * FROM Table1 WHERE Col1=0 AND Col2=1 AND Col3=2

同时

SELECT * FROM Table1 WHERE 1=1

将成为

SELECT * FROM Table1

=====================================

谢谢你指出这个解决方案的一个缺陷:

“如果出于任何原因，其中一个条件包含文本“1 = 1 AND”或“ WHERE 1 = 1”，则可能中断查询。例如，如果条件包含子查询或尝试检查某个列是否包含此文本，则可能会出现这种情况。也许这对你来说不是个问题，但你应该记住...”

为了解决这个问题，我们需要区分“ main”其中1 = 1和子查询，这很简单:

简单地使“ main”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 ";

然后还要附加两行:

Query.Replace("WHERE$ 1=1 AND ", "WHERE ");
Query.Replace(" WHERE$ 1=1 ", "");

小开

我看到 Oracle 在构建存储程序中的动态 SQL 时一直使用这种方法。我在查询中也使用它来探索数据问题，只是为了更快地在不同的数据过滤器之间切换... 只需要注释掉一个条件或者轻松地将它添加回来。

我发现对于正在查看您的代码的人来说，这是非常常见和容易理解的。

小开

恕我直言，我认为你的方法是错误的:

Query the database by concatenating string is NEVER a good idea (risk of SQL 注入 and the code can easily be broken if you do some changes elsewhere).

您可以使用 ORM(我使用冬眠)或至少使用 SqlCommand.Parameters

如果你绝对想使用字符串串联，我会使用 StringBuilder(它是正确的字符串串联对象) :

var query = new StringBuilder("SELECT * FROM Table1 WHERE");
int qLength = query.Length;//if you don't want to count :D
if (Condition1) query.Append(" Col1=0 AND");
if (Condition2) query.Append(" Col2=0 AND");
....
//if no condition remove WHERE or AND from query
query.Length -= query.Length == qLength ? 6 : 4;

作为最后一个想法，Where 1=1是真的难看，但 SQL Server将优化它无论如何。

小开

就像这样做:

using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM Table1";


var conditions = "";
if (condition1)
{
conditions += "Col1=@val1 AND ";
command.AddParameter("val1", 1);
}
if (condition2)
{
conditions += "Col2=@val2 AND ";
command.AddParameter("val2", 1);
}
if (condition3)
{
conditions += "Col3=@val3 AND ";
command.AddParameter("val3", 1);
}
if (conditions != "")
command.CommandText += " WHERE " + conditions.Remove(conditions.Length - 5);
}

它是 SQL 注入和恕我直言，非常干净

如果没有设置任何条件，如果设置了一个条件，或者设置了多个条件，那么它都可以工作。

小开

我喜欢 stringBuilder 流畅的接口，所以我做了一些 ExtensionMethod。

var query = new StringBuilder()
.AppendLine("SELECT * FROM products")
.AppendWhereIf(!String.IsNullOrEmpty(name), "name LIKE @name")
.AppendWhereIf(category.HasValue, "category = @category")
.AppendWhere("Deleted = @deleted")
.ToString();


var p_name = GetParameter("@name", name);
var p_category = GetParameter("@category", category);
var p_deleted = GetParameter("@deleted", false);
var result = ExecuteDataTable(query, p_name, p_category, p_deleted);




// in a seperate static class for extensionmethods
public StringBuilder AppendLineIf(this StringBuilder sb, bool condition, string value)
{
if(condition)
sb.AppendLine(value);
return sb;
}


public StringBuilder AppendWhereIf(this StringBuilder sb, bool condition, string value)
{
if (condition)
sb.AppendLineIf(condition, sb.HasWhere() ? " AND " : " WHERE " + value);
return sb;
}


public StringBuilder AppendWhere(this StringBuilder sb, string value)
{
sb.AppendWhereIf(true, value);
return sb;
}


public bool HasWhere(this StringBuilder sb)
{
var seperator = new string [] { Environment.NewLine };
var lines = sb.ToString().Split(seperator, StringSplitOptions.None);
return lines.Count > 0 && lines[lines.Count - 1].Contains("where", StringComparison.InvariantCultureIgnoreCase);
}


// http://stackoverflow.com/a/4217362/98491
public static bool Contains(this string source, string toCheck, StringComparison comp)
{
return source.IndexOf(toCheck, comp) >= 0;
}

小开

I thought of a solution that, well, perhaps is somewhat more readable:

string query = String.Format("SELECT * FROM Table1 WHERE "
+ "Col1 = {0} AND "
+ "Col2 = {1} AND "
+ "Col3 = {2}",
(!condition1 ? "Col1" : "0"),
(!condition2 ? "Col2" : "1"),
(!condition3 ? "Col3" : "2"));

我只是不确定 SQL 解释器是否也会优化掉 Col1 = Col1条件(当 condition1为 false 时打印)。

小开

public static class Ext
{
public static string addCondition(this string str, bool condition, string statement)
{
if (!condition)
return str;


return str + (!str.Contains(" WHERE ") ? " WHERE " : " ") + statement;
}


public static string cleanCondition(this string str)
{
if (!str.Contains(" WHERE "))
return str;


return str.Replace(" WHERE AND ", " WHERE ").Replace(" WHERE OR ", " WHERE ");
}
}

使用扩展方法实现。

    static void Main(string[] args)
{
string Query = "SELECT * FROM Table1";


Query = Query.addCondition(true == false, "AND Column1 = 5")
.addCondition(18 > 17, "AND Column2 = 7")
.addCondition(42 == 1, "OR Column3 IN (5, 7, 9)")
.addCondition(5 % 1 > 1 - 4, "AND Column4 = 67")
.addCondition(Object.Equals(5, 5), "OR Column5 >= 0")
.cleanCondition();


Console.WriteLine(Query);
}

小开

Dapper SqlBuilder是一个非常好的选择，它甚至在 StackOverflow 的生产环境中使用。

阅读萨姆的博客日志。

As far as I know, it's not part of any Nuget package, so you'll need to copy paste its code into your project or download the Dapper source and build the SqlBuilder project. Either way, you'll also need to reference Dapper for the DynamicParameters class.

小开

这里有一种更优雅的方式:

    private string BuildQuery()
{
string MethodResult = "";
try
{
StringBuilder sb = new StringBuilder();


sb.Append("SELECT * FROM Table1");


List<string> Clauses = new List<string>();


Clauses.Add("Col1 = 0");
Clauses.Add("Col2 = 1");
Clauses.Add("Col3 = 2");


bool FirstPass = true;


if(Clauses != null && Clauses.Count > 0)
{
foreach(string Clause in Clauses)
{
if (FirstPass)
{
sb.Append(" WHERE ");


FirstPass = false;


}
else
{
sb.Append(" AND ");


}


sb.Append(Clause);


}


}


MethodResult = sb.ToString();


}
catch //(Exception ex)
{
//ex.HandleException()
}
return MethodResult;
}

小开

如前所述，通过连接创建 SQL 从来都不是一个好主意。不仅仅是因为 SQL 注入。主要是因为它只是 丑陋，难以维护，完全没有必要。您必须使用跟踪或调试来运行程序，以查看它生成的 SQL。如果您使用 QueryFirst(免责声明: 这是我写的) ，那么不愉快的诱惑就会消除，您可以直接在 SQL 中执行它。

此页全面介绍了用于动态添加搜索谓词的 TSQL 选项。下面的选项对于希望由用户选择搜索谓词的组合的情况非常方便。

select * from table1
where (col1 = @param1 or @param1 is null)
and (col2 = @param2 or @param2 is null)
and (col3 = @param3 or @param3 is null)
OPTION (RECOMPILE)

QueryFirst 将 C # NULL 赋予 db NULL，因此只需在适当的时候使用 NULL 调用 Execute ()方法，它就可以正常工作了。 <opinion>Why are C# devs so reluctant to do stuff in SQL, even when it's simpler. Mind boggles.</opinion>

小开

为什么不使用现有的查询生成器？就像 Sql Kata。

它支持复杂的条件、连接和子查询。

var query = new Query("Users").Where("Score", ">", 100).OrderByDesc("Score").Limit(100);


if(onlyActive)
{
query.Where("Status", "active")
}


// or you can use the when statement


query.When(onlyActive, q => q.Where("Status", "active"))

it works with Sql Server, MySql and PostgreSql.

小开

正如许多人所说，对于更长的过滤步骤，StringBuilder 是更好的方法。

对于你的情况，我会说:

StringBuilder sql = new StringBuilder();


if (condition1)
sql.Append("AND Col1=0 ");
if (condition2)
sql.Append("AND Col2=1 ");
if (condition3)
sql.Append("AND Col3=2 ");


string Query = "SELECT * FROM Table1 ";
if(sql.Length > 0)
Query += string.Concat("WHERE ", sql.ToString().Substring(4)); //avoid first 4 chars, which is the 1st "AND "

小开

简洁、优雅、甜美，如下图所示。