sql-server - VB.NET & SQL Server：使用 Like 在具有多个值的多个列中搜索

Question

我正在尝试使用多个 TextBox 值从 VB 表单中获取搜索结果，以便使用Like运算符在 SQL Server 数据库中进行搜索

    Dim mobile As String = "%" & TextBox1.Text & "%"
    Dim name As String = "%" & TextBox2.Text & "%"
    Dim add1 As String = "%" & TextBox3.Text & "%"
    Dim add2 As String = "%" & TextBox4.Text & "%"
    Dim add3 As String = "%" & TextBox5.Text & "%"
    Dim add4 As String = "%" & TextBox6.Text & "%"
    Dim city As String = "%" & ComboBox1.Text & "%"
    Dim pin As String = "%" & TextBox7.Text & "%"
    Dim phone As String = "%" & TextBox8.Text & "%"
    Dim email As String = "%" & TextBox9.Text & "%"
    Dim gst As String = "%" & TextBox10.Text & "%"
    Dim pan As String = "%" & TextBox11.Text & "%"
    Dim mobile2 As String = "%" & TextBox12.Text & "%"
    Dim dob As Date = DateTimePicker1.Value.Date
    Dim doa As Date = DateTimePicker2.Value.Date
    Dim sms As Integer = If(CheckBox1.Checked, 1, 0)
    Dim district As String = "%" & ComboBox2.Text & "%"
    Dim state As String = "%" & ComboBox3.Text & "%"
    Dim remark As String = "%" & TextBox13.Text & "%"
    SQL.AddParam("@mobile", mobile)
    SQL.AddParam("@name", name)
    SQL.AddParam("@add1", add1)
    SQL.AddParam("@add2", add2)
    SQL.AddParam("@add3", add3)
    SQL.AddParam("@add4", add4)
    SQL.AddParam("@city", city)
    SQL.AddParam("@pin", pin)
    SQL.AddParam("@phone", phone)
    SQL.AddParam("@mobile2", mobile2)
    SQL.AddParam("@email", email)
    SQL.AddParam("@district", district)
    SQL.AddParam("@state", state)
    SQL.AddParam("@gst", gst)
    SQL.AddParam("@pan", pan)
    SQL.AddParam("@sms", sms)
    SQL.AddParam("@dob", dob)
    SQL.AddParam("@doa", doa)
    SQL.AddParam("@remark", remark)
    SQL.ExecQuery("SELECT * FROM customerdata where mobile LIKE @mobile OR name LIKE @name OR add1 LIKE @add1 OR add2 LIKE @add2 OR add3 LIKE @add3 OR add4 LIKE @add4 OR city LIKE @city OR pin LIKE @pin OR phone LIKE @phone OR mobile2 LIKE @mobile2 OR email LIKE @email OR dist LIKE @district OR state LIKE @state OR gst LIKE @gst OR pan LIKE @pan OR dob=@dob OR doa=@doa OR remark LIKE @remark OR smsok=@sms; ")
    If SQL.HasException(True) Then Exit Sub
    DataGridView1.DataSource = SQL.DBDT

我正在使用类来创建SqlConnection对象，因此 SQL.AddParam 和 SQL.ExecQuery 是其中的一部分。

问题是用户只填写了几个文本框，其余的都是“”，上面命令抛出的结果会获取所有记录。

仅捕获具有输入并忽略休息的字段的最佳方法是什么？

标签： sql-servervb.net

Answer 1

这是我为动态搜索构建参数集合和选择查询的方式，用户可以在其中选择一个或部分或全部条件。

Public Shared Function DynamicSearchCoffees(itgRoaster As Integer, strRoast As String, strType As String, strRating As String, bolBold As Boolean, bolFavorite As Boolean) As DataTable
    Dim dt As New DataTable
    Dim bolNeedAnd As Boolean = False
    Dim sb As New Text.StringBuilder
    sb.Append("SELECT Coffees.ID, Coffees.[Name], Coffees.RoasterID, Roasters.[Name] As 'RoasterName', Coffees.[Type],Coffees.Rating, Coffees.Comment, Coffees.Description, Coffees.Roast, Coffees.IsExtraBold, Coffees.IsFavorite
                From Coffees Inner Join Roasters on Coffees.RoasterID = Roasters.ID Where ")

    Using cn As New SqlConnection(conStr),
            cmd As New SqlCommand()
        If itgRoaster <> 0 Then
            sb.Append("RoasterID = @RoasterID ")
            cmd.Parameters.Add("@RoasterID", SqlDbType.Int).Value = itgRoaster
            bolNeedAnd = True
        End If
        If strRoast <> "" Then
            If bolNeedAnd Then
                sb.Append($"AND ")
            End If
            sb.Append($"Roast = @Roast ")
            cmd.Parameters.Add("@Roast", SqlDbType.VarChar, 100).Value = strRoast
            bolNeedAdd = True
        End If
        If strType <> "" Then
            If bolNeedAnd Then
                sb.Append($"AND ")
            End If
            sb.Append($"Type = @Type ")
            cmd.Parameters.Add("@Type", SqlDbType.VarChar, 20).Value = strType
            bolNeedAnd = True
        End If
        If strRating <> "" Then
            If bolNeedAnd Then
                sb.Append($"AND ")
            End If
            sb.Append($"Rating = @Rating ")
            cmd.Parameters.Add("@Rating", SqlDbType.VarChar, 20).Value = strRating
            bolNeedAnd = True
        End If
        If bolBold Then
            If bolNeedAnd Then
                sb.Append($"AND ")
            End If
            sb.Append("IsExtraBold = 1 ")
            bolNeedAnd = True
        End If
        If bolFavorite Then
            If bolNeedAnd Then
                sb.Append($"AND ")
            End If
            sb.Append("IsFavorite = 1 ")
        End If
        sb.Append("Order By Coffees.[Name], RoasterName;")
        cmd.CommandText = sb.ToString
        cmd.Connection = cn
        cn.Open()
        dt.Load(cmd.ExecuteReader)
    End Using
    Return dt
End Function