SqlDataSource中的动态WHERE子句
本文关键字:WHERE 子句 动态 SqlDataSource | 更新日期: 2023-09-27 18:23:55
我在一个非常简单的应用程序中使用SqlDataSource。我允许用户通过TextBoxes为SDS的select命令设置几个搜索参数,每个参数一个TextBox(比如txtFirstName、txtLastName等)。我计划使用按钮单击事件处理程序来设置SqlDataSource的SelectCommand属性,默认情况下,该属性将返回所有记录(出于我的目的)。我想完善这个select命令,根据用户是否在我的任何TextBox中输入搜索条件,可能添加一个或多个WHERE子句。
如果我不清楚,示例:
默认情况下,SqlDataSource的SelectCommand属性如下所示:
SELECT * FROM MyTable
如果用户在txtFirstName中输入"Bob",我希望SelectCommand属性如下所示:
SELECT * FROM MyTable WHERE [FirstName]='Bob'
如果用户在txtLastName中输入"Jones",我希望SelectCommand属性如下所示:
SELECT * FROM MyTable WHERE [FirstName]='Bob' AND [LastName]='Jones'
我的问题:
有没有一种方法可以动态创建这些WHERE子句,而不必测试空的TextBoxes和手工构建WHERE子句?
我的小应用程序只有三个参数,所以强行通过它不会很痛苦,但我想知道是否有更简单的方法可以做到这一点,而且我将来可能需要添加更多的参数。另外,我可能想添加通配符搜索。
如果您使用的是SqlDataSource控件,并且参数值来自页面控件,则可以提供ControlParameters并使用带有短路参数的静态where子句。这可能只是快速编写一些代码的门票。
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:AdventureworksConnectionString %>"
SelectCommand=" SELECT FirstName, LastName, Age
FROM Contacts
WHERE (FirstName=@FirstName or @FirstName = '')
AND (LastName = @LastName or @LastName = ''
AND (Age = @Age or @Age = 0" >
<SelectParameters>
<asp:ControlParameter Name="FirstName" ControlID="TBFirstName" Type="String" />
<asp:ControlParameter Name="LastName" ControlID="TBLastName" Type="String" />
<asp:ControlParameter Name="Age" ControlID="TBAge" Type="Int16" />
</SelectParameters>
</asp:SqlDataSource>
正如您所说,构建该查询并不太困难,因为您总是对where子句中的字段进行AND运算。
请注意,如果您这样做,请不要格式化字符串。使用SqlParameters避免SQL注入:http://en.wikipedia.org/wiki/SQL_injection
因此,您可以从WHERE开始,对于每个有值的文本框,追加[(fieldName)]=@(fieldName)并绑定该sql参数。
请参阅:http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.aspx
如果您愿意使用Linq to SQL或实体框架,请参阅以下谓词生成器:http://www.albahari.com/nutshell/predicatebuilder.aspx
您可以设置ConvertEmptyStringToNull="false"
并执行:
SELECT *
FROM MyTable
WHERE [FirstName] = CASE @firstname WHEN '' THEN [FirstName] END AND
[LastName] = CASE @lastname WHEN '' THEN [LastName] END
或者您可以将ConvertEmptyStringToNull设置为"true"并执行:
SELECT *
FROM MyTable
WHERE [FirstName] = ISNULL(@firstname, [FirstName]) AND
[LastName] = ISNULL(@lastname,[LastName])
在这两种情况中,如果用户将文本框留空,则CASE
或ISNULL
语句会将where语句的每一部分与自身进行比较,从而返回TRUE
并生成相同的记录,就好像where子句的这一部分根本不存在一样。这是一个很好且简单的解决方案,可以保持查询和参数的静态,并将逻辑推送到SQL端。
然而,与只说"SELECT * FROM MyTable"
相比,它的性能会受到(小)影响。那些CASE
和ISNULL
操作不是免费的;)如果这是一个令人担忧的问题,那么Bryanmac的解决方案是完全可以接受的。
您可以使用CancelSelectOnNullParameter=false
,如本例