如何实现更高效的搜索功能？ [英] How to implement a more efficient search feature?

问题描述

在我的数据库中有3列，即姓名，年龄，性别。
在程序中，我只想使用1个搜索按钮。当按钮被点击时，程序确定文本框中的哪个输入并且搜索正确的数据。

如何使用查询？例如，如果Name和Gender具有文本，则查询：

从表中选择*其中（Name = @name）AND（Gender = @性别）

当只输入名称时，我只查询名称。我必须通过文本框检查文本框是否有用户输入，然后为它们写多个查询？

编辑（29/5/16）：我试着这样做另一种方式

myCommand = New MySqlCommand（Select * from project_record其中
（FloatNo = @floatNo或FloatNo ='None'）AND
= @devCompanyName OR DevCompanyName ='None'）AND
（DevType = @devType OR DevType ='None'）AND
（LotPt = @lotPt或LotPt ='None'）AND
Mukim = @mukim OR Mukim ='None'）AND
（Daerah = @daerah或Daerah ='None'）AND
（Negeri = @negeri或Negeri ='None'）AND
（TempReference = @tempRef OR TempReference ='None'），sqlConn）


已经不能有效地工作，因为如果我只输入 DevType 并留下其他文本框空白，查询将不会拉起所有的记录 DevType 。

解决方案

原始答案

（向下滚动以查看更新）

您可以尝试以下操作：

• 建立只包含输入的文本框值的列表


• 设置连接该列表项目的字符串以及AND字符串


• 将此字符串附加到标准SELECT语句

代码如下所示：

  Private Sub Button1_Click（sender As Object，e As EventArgs）Handles Button1.Click 
 
 Dim Predicate1 As String = Me.TextBox1.Text 
 Dim Predicate2 As String = Me.TextBox2.Text 
 Dim Predicate3 As String = Me.TextBox3.Text 
 Dim PredicateList As New List（Of String）
 Dim WhereClause As String 
 Dim Query As String 
 
如果Predicate1<> String.Empty Then 
 PredicateList.Add（Name =& Predicate1&）
如果
结束如果Predicate2& String.Empty then 
 PredicateList.Add（Age =& Predicate2&）
 End If 
如果Predicate3& String.Empty Then 
 PredicateList.Add（Gender =& predicate3&）
如果
 
结束WhereClause = String.Join ，PredicateList.ToArray）
 Query =SELECT * FROM TABLE WHERE& WhereClause 
 MessageBox.Show（Query）
 
 End Sub 
  

更新

下面是一个更新的示例。

  Dim Command As SqlClient.SqlCommand 
 Dim Predicate1 As String = Me.TextBox1.Text 
 Dim Predicate2 As String = Me.TextBox2.Text 
 Dim Predicate3 As String = Me.TextBox2 .text 
 Dim ParameterList As New List（SqlClient.SqlParameter）
 Dim PredicateList As New List（Of String）
 Dim BaseQuery As String =SELECT * FROM TABLE WHERE
 
如果Predicate1<> String.Empty然后
 PredicateList.Add（name = @name）
 ParameterList.Add（新的SqlClient.SqlParameter（@ name，Predicate1））
 End If 
如果Predicate2<> String.Empty然后
 PredicateList.Add（age = @age）
 ParameterList.Add（新的SqlClient.SqlParameter（@ age，Predicate2））
结束如果
如果Predicate3<> String.Empty然后
 PredicateList.Add（gender = @gender）
 ParameterList.Add（新的SqlClient.SqlParameter（@ gender，Predicate3））
 End If 
 
 Command = New SqlClient.SqlCommand（BaseQuery& String.Join（AND，PredicateList.ToArray））
 Command.Parameters.AddRange（ParameterList.ToArray）
  

In my database there are 3 column which is Name, Age, Gender. In the program, I only want to use 1 search button. When the button is clicked, the program determine which 3 of the textbox has input and search for the right data.

How do you work with the query? For example if Name and Gender has text, the query :

"Select * from table Where (Name = @name) AND (Gender = @gender)"

And when only name is entered, I only query for the name. Must I check textbox by textbox whether there is user input and then write multiple query for each of them? Or is there a better way to do this?

Edit (29/5/16) : I tried doing this another way like this

myCommand = New MySqlCommand("Select * from project_record Where
                       (FloatNo = @floatNo OR FloatNo = 'None') AND 
                       (DevCompanyName = @devCompanyName OR DevCompanyName = 'None') AND 
                       (DevType = @devType OR DevType = 'None') AND 
                       (LotPt = @lotPt OR LotPt = 'None') AND
                       (Mukim = @mukim OR Mukim = 'None') AND
                       (Daerah = @daerah OR Daerah = 'None') AND
                       (Negeri = @negeri OR Negeri = 'None') AND
                       (TempReference = @tempRef OR TempReference = 'None')", sqlConn)

But as you can guess already it will not work efficiently as well because if I only enter input for DevType and leave other textboxes blank, the query will not pull up all the records for DevType only. It will just display as no records.

解决方案

Original answer

(scroll down to see update)

Can you try the following:

• build a list only including values of the textboxes that have an input
• set a string of the join the items of that list together with the " AND " string
• append that string to your standard SELECT statement

The code looks like this:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim Predicate1 As String = Me.TextBox1.Text
    Dim Predicate2 As String = Me.TextBox2.Text
    Dim Predicate3 As String = Me.TextBox3.Text
    Dim PredicateList As New List(Of String)
    Dim WhereClause As String
    Dim Query As String

    If Predicate1 <> String.Empty Then
        PredicateList.Add("Name=""" & Predicate1 & """")
    End If
    If Predicate2 <> String.Empty Then
        PredicateList.Add("Age=""" & Predicate2 & """")
    End If
    If Predicate3 <> String.Empty Then
        PredicateList.Add("Gender=""" & Predicate3 & """")
    End If

    WhereClause = String.Join(" AND ", PredicateList.ToArray)
    Query = "SELECT * FROM TABLE WHERE " & WhereClause
    MessageBox.Show(Query)

End Sub

Update

Further to the comments re SQL injection, here is an updated sample.

Dim Command As SqlClient.SqlCommand
Dim Predicate1 As String = Me.TextBox1.Text
Dim Predicate2 As String = Me.TextBox2.Text
Dim Predicate3 As String = Me.TextBox2.Text
Dim ParameterList As New List(Of SqlClient.SqlParameter)
Dim PredicateList As New List(Of String)
Dim BaseQuery As String = "SELECT * FROM TABLE WHERE "

If Predicate1 <> String.Empty Then
    PredicateList.Add("name = @name")
    ParameterList.Add(New SqlClient.SqlParameter("@name", Predicate1))
End If
If Predicate2 <> String.Empty Then
    PredicateList.Add("age = @age")
    ParameterList.Add(New SqlClient.SqlParameter("@age", Predicate2))
End If
If Predicate3 <> String.Empty Then
    PredicateList.Add("gender = @gender")
    ParameterList.Add(New SqlClient.SqlParameter("@gender", Predicate3))
End If

Command = New SqlClient.SqlCommand(BaseQuery & String.Join(" AND ", PredicateList.ToArray))
Command.Parameters.AddRange(ParameterList.ToArray)

这篇关于如何实现更高效的搜索功能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！