Vb.Net程序设计：简单的查询条件组合(更新版)

2011.08最近想到另外一个方法：

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim cd As String
        Dim cmd As New SqlClient.SqlCommand
        Dim strWhere As String = String.Empty
        Dim dt As DataTable
        If Me.RdoOr.Checked Then
            cd = " or "
        Else
            cd = " and "
        End If
        If String.IsNullOrEmpty(Me.TxtItemNo.Text.Trim) = False Then
            strWhere &= (IIf(String.IsNullOrEmpty(strWhere), "", cd) & "strItemNo like (@strItemNo)")
            cmd.Parameters.Add(New SqlClient.SqlParameter("@strItemNo", Me.TxtItemNo.Text.Trim & "%"))
        End If
        If String.IsNullOrEmpty(Me.TxtItmeName.Text.Trim) = False Then
            strWhere &= (IIf(String.IsNullOrEmpty(strWhere), "", cd) & "strItemName like (@strItemName)")
            cmd.Parameters.Add(New SqlClient.SqlParameter("@strItemName", Me.TxtItmeName.Text.Trim & "%"))
        End If

        If String.IsNullOrEmpty(Me.TxtPrice.Text.Trim) = False Then
            strWhere &= (IIf(String.IsNullOrEmpty(strWhere), "", cd) & "dblPrice >= (@dblPrice)")
            '这里可以设置查询条件数字对比的连接符号：>= = <=
            cmd.Parameters.Add(New SqlClient.SqlParameter("@dblPrice", CDbl(Me.TxtPrice.Text.Trim)))
        End If

        If String.IsNullOrEmpty(Me.TxtSize.Text.Trim) = False Then
            strWhere &= (IIf(String.IsNullOrEmpty(strWhere), "", cd) & "strItemNo like (@strItemSize)")
            '这样可以查询包含有特殊符号的内容了。
            cmd.Parameters.Add(New SqlClient.SqlParameter("@strItemSize", Me.TxtSize.Text.Trim & "%"))
        End If

        cmd.CommandText = "select * from tbl " & IIf(String.IsNullOrEmpty(strWhere), "", " where " & strWhere)
        'cmd.CommandText ="sp_QueryItem"
        'cmd.CommandType = CommandType.StoredProcedure 

        dt = SQLHelper.GetDataTable(cmd)
        '查询结果：dt 

        'FillTB2MsgDvListSearch(dt)
        '填充表内容到DataGridView里面。

    End Sub

很久很久以前的：查询条件组合方法，主要有StringBuilder，感觉比用vb6、vba方便多了。

Imports System.Text

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim s As New StringBuilder
        Dim cd As String
        If Me.RadioButton1.Checked = True Then
            cd = " and "
        Else
            cd = " or "
        End If
        If String.IsNullOrEmpty(Me.TextBox1.Text.Trim) = False Then
            s.Append("货号 like '" & Me.TextBox1.Text.Trim & "%'" & cd)
        End If
        If String.IsNullOrEmpty(Me.TextBox2.Text.Trim) = False Then
            s.Append("年份 like '" & Me.TextBox2.Text.Trim & "%'" & cd)
        End If
        If String.IsNullOrEmpty(Me.TextBox3.Text.Trim) = False Then
            s.Append("大类别 like '" & Me.TextBox3.Text.Trim & "%'" & cd)
        End If
        If String.IsNullOrEmpty(Me.TextBox4.Text.Trim) = False Then
            s.Append("小类别 like '" & Me.TextBox4.Text.Trim & "%'" & cd)
        End If
        Me.TextBox5.Text = s.ToString.Remove(s.ToString.LastIndexOf(cd), cd.Length)
    End Sub

End Class

界面：

界面2

---

2011.06 使用到程序中的代码：主要用到：Dictionary ，分开SQLParameter或者SQLiteParameter参数

        Dim sb As New StringBuilder
        Dim where, cd As String
        Dim p As SQLite.SQLiteParameter
        Dim tb As DataTable
        Dim dics As New Dictionary(Of String, SQLite.SQLiteParameter)
        Dim iKey As Integer = 0
        Me.DvMsgListSearch.DataSource = Nothing
        If Me.RdoOr.Checked Then
            cd = " or "
        Else
            cd = " and "
        End If
        If String.IsNullOrEmpty(Me.TxtStrSubject1.Text.Trim) = False Then
            sb.Append("strSubject like (@strSubject)" & cd)
            p = db.MakeParameter("@strSubject", DbType.String, Me.TxtStrSubject1.Text.Trim & "%")
            dics.Add("strSubject", p)
        End If
        If String.IsNullOrEmpty(Me.TxtStrTag1.Text.Trim) = False Then
            sb.Append("strTag like (@strTag)" & cd)
            p = db.MakeParameter("@strTag", DbType.String, Me.TxtStrTag1.Text.Trim & "%")
            dics.Add("strTag", p)
        End If
        If String.IsNullOrEmpty(Me.TxtStrMsgAuthor1.Text.Trim) = False Then
            sb.Append("strMsgAuthor like (@strTag)" & cd)
            p = db.MakeParameter("@strMsgAuthor", DbType.String, Me.TxtStrMsgAuthor1.Text.Trim & "%")
            dics.Add("strMsgAuthor", p)
        End If
        If String.IsNullOrEmpty(Me.TxtStrMsgFrom1.Text.Trim) = False Then
            sb.Append("strMsgFrom like (@strMsgFrom)" & cd)
            p = db.MakeParameter("@strMsgFrom", DbType.String, Me.TxtStrMsgFrom1.Text.Trim & "%")
            dics.Add("strMsgFrom", p)
            's.Append("strMsgFrom like '" & Me.TxtStrMsgFrom1.Text.Trim & "%'" & cd)
        End If
        If String.IsNullOrEmpty(Me.TxtStrContext1.Text.Trim) = False Then
            sb.Append("strContext like (@strContext)" & cd)
            p = db.MakeParameter("@strContext", DbType.String, Me.TxtStrContext1.Text.Trim & "%")
            dics.Add("strContext", p)
            's.Append("strContext like '" & Me.TxtStrContext1.Text.Trim & "%'" & cd)
        End If
        If String.IsNullOrEmpty(Me.TxtStrReporter1.Text.Trim) = False Then
            sb.Append("strReporter like (@strReporter)" & cd)
            p = db.MakeParameter("@strReporter", DbType.String, Me.TxtStrReporter1.Text.Trim & "%")
            dics.Add("strReporter", p)
            's.Append("strReporter like '" & Me.TxtStrReporter1.Text.Trim & "%'" & cd)
        End If
        If dics.Count > 0 Then
            where = sb.ToString.Remove(sb.ToString.LastIndexOf(cd), cd.Length)
            Dim pr(dics.Count - 1) As SQLite.SQLiteParameter
            iKey = 0
            For Each key As KeyValuePair(Of String, SQLite.SQLiteParameter) In dics
                pr(iKey) = key.Value
                iKey += 1
            Next
            tb = MsgCls.GetSimpleList(where, pr)
            FillTB2MsgDvListSearch(tb)
        End If