动态WHERE子句中的一个SqlDataSource [英] Dynamic WHERE clauses in a SqlDataSource

问题描述

我用一个非常简单的应用一个SqlDataSource。我允许用户设置多个搜索参数，通过文本框的SDS的选择命令，每个参数一个TextBox（想想txtFirstName，txtLastName等）。我打算用一个按钮单击事件处理程序来设置SqlDataSource控件的SelectCommand属性在默认情况下将返回所有记录（为我的目的这里）。我想完善这一选择命令可能添加一个或多个WHERE取决于用户是否在我的任何文本框输入搜索条件的条款。

I'm using a SqlDataSource in a very simple application. I'm allowing the user to set several search parameters for the SDS's select command via TextBoxes, one TextBox per parameter (think txtFirstName, txtLastName, etc). I'm planning on using a button click event handler to set the SqlDataSource's SelectCommand property which by default will return all records (for my purposes here). I want to refine this select command to possibly add one or more WHERE clauses depending on if the user enters search criteria in any of my TextBoxes.

例的情况下，我没有说清楚：

Example in case I'm not being clear:

默认情况下，我的SqlDataSource的SelectCommand属性将是这样的：

By default, my SqlDataSource's SelectCommand property will be something like this:

SELECT * FROM MyTable

如果用户在txtFirstName进入鲍勃，我要SelectCommand属性看起来是这样的：

If the user enters "Bob" in txtFirstName, I want to SelectCommand property to look like this:

SELECT * FROM MyTable WHERE [FirstName]='Bob'

如果用户输入的txtLastName奇兵，我想SelectCommand属性看起来是这样的：

If the user enters "Jones" in txtLastName, I want to SelectCommand property to look like this:

SELECT * FROM MyTable WHERE [FirstName]='Bob' AND [LastName]='Jones'

我的问题：结果
有没有一种方法可以动态地创建这些WHERE子句没有我不必测试空文本框和构建WHERE手工条款？

My question:
Is there a way to dynamically create these WHERE clauses without me having to test for empty TextBoxes and constructing a WHERE clause by hand?

我的小应用程序只有三个参数，使暴力破解我的方式，通过这不会是痛苦的，但我想知道如果有这样做一个简单的方法，再加上它可能我需要在未来增加更多的参数。另外，我可能需要添加通配符搜索。

My little application only has three parameters so brute forcing my way through this wouldn't be painful, but I've wondered if there was an easier way to do this plus it's possible I'll need to add more parameters in the future. Plus I may want to add wildcard searching.

推荐答案

正如你所说，这不是太困难的，因为你要构建查询始终AND运算字段中的where子句。

As you said, it's not too hard to construct that query since you're always ANDing the fields in the where clause.

如果你这样做，不格式化字符串注意。使用SqlParameters避免SQL注入： http://en.wikipedia.org/wiki/SQL_injection

Be aware if you do that, don't format the string. Use SqlParameters to avoid SQL Injection: http://en.wikipedia.org/wiki/SQL_injection

所以，你可以用WHERE和具有价值每个文本框，添加启动[（字段名）] = @（字段名），并绑定该SQL参数。

So, you can start with WHERE and for each text box that has a value, append [(fieldName)] = @(fieldname) and bind that sql parameter.

请参阅：的 http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.aspx

如果你愿意使用LINQ到SQL或实体框架，看到这个谓词建设者： HTTP：//www.albahari .COM /一言以蔽之/ predicatebuilder.aspx

If you're willing to use Linq to SQL or the entity framework, see this predicate builder: http://www.albahari.com/nutshell/predicatebuilder.aspx

这篇关于动态WHERE子句中的一个SqlDataSource的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！