C# 以动态/编程方式将 WHERE 子句添加到 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11329823/
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
Add WHERE clauses to SQL dynamically / programmatically
提问by mbigun
How can I add search condition to SQL Stored Procedure programmatically? In my application(C#) I'm using stored procedure (SQL Server 2008R2)
如何以编程方式向 SQL 存储过程添加搜索条件？在我的应用程序（C#）中，我使用了存储过程（SQL Server 2008R2）
ALTER PROCEDURE [dbo].[PROC001]
@userID varchar(20),
@password varchar(20)
AS
SELECT * FROM tUsers WHERE RTRIM(Name) = @userID AND RTRIM(Password) = @password
I want to extend this query by more conditions, and now I don't know how many conditions will use this query due program execution.. 2, 3, 6 OR 20. I want to add these conditions programmatically like:
我想通过更多条件扩展这个查询，现在我不知道有多少条件将使用这个查询由于程序执行.. 2, 3, 6 OR 20. 我想以编程方式添加这些条件，如：
SELECT * FROM tUsers WHERE RTRIM(Name) = @userID AND RTRIM(Password) = @password
AND Field2 = '1' AND Field3 = '0' OR Field4 <> '8' AND Field5 < '100' ....
Is it possible to sent conditions to stored procedure dynamically?
是否可以动态地向存储过程发送条件？
采纳答案by StuartLC
Edit - Preference for LINQ based ORM's, if possible
编辑 - 如果可能，首选基于 LINQ 的 ORM
If you don't need to do this in ADO, a better solution is to use an ORM which will ultimately build parameterized ad-hoc sql. This is the best of both worlds - you get the flexibility of a dynamic query, with no redundant filters to upset the optimizer, the query plan itself is cacheable, and you are safe from nasties like injection attacks. And a Linq-based ORM query makes for easy reading:
如果您不需要在 ADO 中执行此操作，更好的解决方案是使用 ORM，它最终将构建参数化的 ad-hoc sql。这是两全其美的 - 您可以获得动态查询的灵活性，没有多余的过滤器来扰乱优化器，查询计划本身是可缓存的，并且您可以免受注入攻击之类的麻烦。基于 Linq 的 ORM 查询使阅读变得容易：
// Build up a non-materialized IQueryable<>
var usersQuery = db.Users;
if (!string.IsNullOrEmpty(userID))
{
usersQuery = usersQuery.Where(u => u.Name == userId);
}
// Of course, you wouldn't dream of storing passwords in cleartext.
if (!string.IsNullOrEmpty(anotherField))
{
usersQuery = usersQuery.Where(u => u.AnotherColumn == anotherField);
}
...
// Materialize (and execute) the query
var filteredUsers = usersQuery.ToList();
For complex queries, you may want to look at PredicateBuilder
对于复杂的查询，您可能需要查看PredicateBuilder
ADO / manual query building
ADO/手动查询构建
You can use sp_executesqlto build up SQL dynamically as per below. Provided that you parameterize the variables you should be safe from issues like SQL injection and escaping quotes etc will be handled for you.
您可以使用sp_executesql如下动态构建 SQL。如果您对变量进行参数化，您应该可以避免 SQL 注入和转义引号等问题，将为您处理。
CREATE PROCEDURE [dbo].[PROC001]
@userID varchar(20),
@pwdHash varchar(20),
@optionalParam1 NVARCHAR(50) = NULL -- Other optional parameters
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQL NVARCHAR(MAX)
-- Mandatory / Static part of the Query here.
-- Cleartext passwords are verboten, and RTRIM is redundant in filters
SET @SQL = N'SELECT * FROM tUsers WHERE Name = @userID AND PwdHash = @pwdHash'
IF @OptionalParam1 IS NOT NULL
BEGIN
SET @SQL = @SQL + N' AND AnotherField = @OptionalParam1'
END
EXEC sp_executesql @SQL,
N'@userID varchar(20),
@pwdHash varchar(20),
@optionalParam1 NVARCHAR(50)'
,@userID = @userID
,@pwdHash = @pwdHash
,@optionalParam1 = @optionalParam1
END
Re, why is WHERE (@x IS NULL OR @x = Column)a bad idea?
Re，为什么是WHERE (@x IS NULL OR @x = Column)个坏主意？
(From my comment below)
（来自我下面的评论）
Although the 'optional parameter' pattern works well as a 'swiss army knife' for querying a multitude of permutations of optional filters when used on small tables, unfortunately, for large tables, this results in a single query plan for all permutations of filters for the query, which can result in poor query performance with certain permutations of optional parameters due to the parameter sniffing problem. If possible, you should eliminate redundant filters entirely.
尽管“可选参数”模式作为“瑞士军刀”在小表上使用时可以很好地查询可选过滤器的多种排列，但不幸的是，对于大表，这会导致所有过滤器排列的单一查询计划用于查询，由于参数嗅探问题，这可能会导致查询性能不佳，可选参数的某些排列。如果可能，您应该完全消除冗余过滤器。
Re: Why is applying functions in predicates a bad idea
回复：为什么在谓词中应用函数是个坏主意
e.g.
例如
WHERE SomeFunction(Column) = @someParameter
Use of functions in predicates frequently disqualifies the use of indexes by the RDBMS ("non-sargable").
在谓词中使用函数经常会取消 RDBMS 使用索引的资格（“non-sargable”）。
In this instance, RTRIMis unnecessary as Sql server ignorestrailing spaces during comparison.
回答by Mahmoud Gamal
You can do this in sql only, like this:
您只能在 sql 中执行此操作，如下所示：
SELECT *
FROM tUsers
WHERE 1 = 1
AND (@userID IS NULL OR RTRIM(Name) = @userID )
AND (@password IS NULL OR RTRIM(Password) = @password)
AND (@field2 IS NULL OR Field2 = @field2)
....
If any parameter passed to the stored procedure with a NULLvalue then the whole condition will be ignored.
如果传递给存储过程的任何参数带有NULL值，则整个条件将被忽略。
Note that: I added WHERE 1 = 1in order to make the query work in case no parameter passed to the query and in this case alll the result set will be returned, since 1 = 1is always true.
请注意：我添加WHERE 1 = 1是为了在没有参数传递给查询的情况下使查询工作，在这种情况下，将返回所有结果集，因为1 = 1它始终为真。
回答by Gonzalo.-
you could have your procedure as string, and send a string with the conditions, concatenate and exec.
你可以将你的过程作为字符串，并发送一个带有条件的字符串，连接和执行。
ALTER PROCEDURE [dbo].[PROC001] @userID varchar(20), @password varchar(20), @WhereToAdd varchar(MAX) AS
exec ('SELECT * FROM tUsers WHERE RTRIM(Name) = @userID AND RTRIM(Password) = @password AND ' + @WhereToAdd)
