asp.net - 高级数据库搜索示例

Question

我正在寻找示例脚本。我昨天看到了一个，但今天我再也找不到了。

我的任务是允许用户通过 aspx 页面上的输入控件搜索 1 个数据库表，他们可以在其中选择和，或，等于组合字段，使用 concat/stringbuilder 或类似工具即时生成 sql。 (它在公司防火墙后面运行)

请有人能给我指出示例或教程的正确方向

我一直在处理页面，但遇到了问题。这是 Page_load;

 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Dim sql As String = ("Select * From Table Where ")

    'variables to hold the and or values between fields
    Dim andor1v As String = AndOr1.SelectedValue.ToString()
    Dim andor2v As String = AndOr2.SelectedValue.ToString()
    Dim andor3v As String = AndOr3.SelectedValue.ToString()
    Dim andor4v As String = AndOr4.SelectedValue.ToString()
    Dim andor5v As String = AndOr5.SelectedValue.ToString()
    Dim andor6v As String = AndOr6.SelectedValue.ToString()

    'variables to stop web control inputs going direct to sql 
    Dim name As String = NameSearch.Text.ToString()
    Dim email As String = EmailSearch.Text.ToString()
    Dim city As String = CitySearchBox.Text.ToString()
    Dim province As String = ProvinceSelect.SelectedValue.ToString()
    Dim qualifications As String = QualificationsObtained.Text.ToString()
    Dim competencies As String = CompetenciesDD.SelectedValue.ToString()
    Dim expertise As String = Expertiselist.SelectedValue.ToString()

    If NameSearch.Text IsNot String.Empty Then
        sql += "Surname LIKE '%" & name & "%' "
    End If

    If EmailSearch.Text IsNot String.Empty Then
        sql += andor1v & " Email LIKE '%" & email & "%' "
    End If

    If CitySearchBox.Text IsNot String.Empty Then
        sql += andor2v & " City LIKE '%" & city & "%' "
    End If

    If QualificationsObtained.Text IsNot String.Empty Then
        sql += andor3v & " (institutionquals1 LIKE '%" & qualifications & "%') OR " & _
        "(institutionquals2 LIKE '%" & qualifications & "%') OR " & _
        "(institutionquals3 LIKE '%" & qualifications & "%') OR " & _
        "(institutionquals4 LIKE '%" & qualifications & "%') "
    End If

    Dim selectedrow As String = CompetenciesDD.SelectedValue.ToString
    Dim selectedquals As String = NQFlevel.SelectedValue.ToString
    If CompetenciesDD.SelectedValue.ToString IsNot "0" And selectedquals = 0 Then
        sql += (selectedrow & " = 1 ")

    ElseIf selectedrow = "assessortrue" And selectedquals IsNot "0" Then
        sql += andor4v & (" assessortrue=1  and assessorlvl=" & selectedquals)
    ElseIf selectedrow = "coordinatortrue" And selectedquals IsNot "0" Then
        sql += andor4v & ("coordinatortrue=1 and coordinatorlvl=" & selectedquals)
    ElseIf selectedrow = "facilitatortrue" And selectedquals IsNot "0" Then
        sql += andor4v & ("facilitatortrue=1 and facilitatorlvl=" & selectedquals)
    ElseIf selectedrow = "moderatortrue" And selectedquals IsNot "0" Then
        sql += andor4v & ("moderatortrue=1 and moderatorlvl=" & selectedquals)
    ElseIf selectedrow = "productdevelopertrue" And selectedquals IsNot "0" Then
        sql += andor4v & ("productdevelopertrue=1 and productdeveloperlvl=" & selectedquals)
    ElseIf selectedrow = "projectmanagertrue" And selectedquals IsNot "0" Then
        sql += andor4v & ("projectmanagertrue=1 and projectmanagerlvl=" & selectedquals)
    End If

    Response.Write(sql)

End Sub

经过一个小时的修改，代码现在看起来和上面一样了 ^

现在我面临的问题是，如果用户没有输入姓氏(第一个字段)的值，但确实输入了电子邮件(或任何后续字段)的值，则生成的 sql 有一个额外的，像这样；

Select * From Table Where And Email LIKE '%test%' 

我也在寻找一种方法来考虑 OR 选项。您是否认为这应该像 Martin 所说的那样完成，其中整个查询是 and 或 or 和 not mix of the 2？那么我应该能够取出所有和/或下拉菜单？

谢谢。

注意:我并不是真的在寻找关于我应该如何参数化或关于 sql 注入(inject)的评论。

Answer 1

关于用户未选择选项的问题，您可以删除“请选择”并将其默认为“和”

另外，如果他们选择 AND 和 OR 的组合，期望的行为是什么？

默认情况下，如果没有任何括号，将首先评估 AND

http://msdn.microsoft.com/en-us/library/ms186992.aspx

所以如果他们进入

name="Fred" or email="blah" and city="london" and province="xyz" or qualifications="Degree"

我不太确定所需的语义是什么？

是吗

(name="Fred" or email="blah") and city="london" and (province="xyz" or qualifications="Degree")

或

(name="Fred" or (email="blah" and city="london") and province="xyz") or qualifications="Degree"

还是有什么不同？也许您应该将它们限制为整个查询的 AND 或 OR，或者允许它们通过输入带括号的高级搜索语法或通过提供查询构建器 UI 来消除歧义。