VB.NET 机房收费系统之组合查询

纵观机房收费系统，组合查询算是比较复杂的部分，相信大家实现这个功能用的方法也各不相同，下面我介绍一下我是如何实现的，可能我的做法不是很完美，希望大家给我提一些建议，同时也很希望大家留下您的做法，让我也欣赏、学习一下。下面以学生基本信息维护为例，介绍一下我的做法：

下面是学生基本信息维护的界面

下面是代码实现过程：

实体类：

''' <summary>
''' 专门用户组合查询的实体类
''' </summary>
''' <remarks></remarks>
Public Class QueryCompositon
    Private e_FiledName1 As String '字段1
    Public Property FiledName1 As String
        Get
            Return Me.e_FiledName1
        End Get
        Set(ByVal value As String)
            Me.e_FiledName1 = value
        End Set
    End Property

    Private e_FiledName2 As String '字段名2
    Public Property FiledName2 As String
        Get
            Return Me.e_FiledName2
        End Get
        Set(ByVal value As String)
            Me.e_FiledName2 = value
        End Set
    End Property

    Private e_FiledName3 As String '字段名3
    Public Property FiledName3 As String
        Get
            Return Me.e_FiledName3
        End Get
        Set(ByVal value As String)
            Me.e_FiledName3 = value
        End Set
    End Property

    Private e_Operator1 As String '操作符1
    Public Property Operator1 As String
        Get
            Return Me.e_Operator1
        End Get
        Set(ByVal value As String)
            Me.e_Operator1 = value
        End Set
    End Property

    Private e_Operator2 As String '操作符2
    Public Property Operator2 As String
        Get
            Return Me.e_Operator2
        End Get
        Set(ByVal value As String)
            Me.e_Operator2 = value
        End Set
    End Property

    Private e_Operator3 As String '操作符3
    Public Property Operator3 As String
        Get
            Return Me.e_Operator3
        End Get
        Set(ByVal value As String)
            Me.e_Operator3 = value
        End Set
    End Property

    Private e_QueryContent1 As String '查询内容1
    Public Property QueryContent1 As String
        Get
            Return Me.e_QueryContent1
        End Get
        Set(ByVal value As String)
            Me.e_QueryContent1 = value
        End Set
    End Property

    Private e_QueryContent2 As String '查询内容2
    Public Property QueryContent2 As String
        Get
            Return Me.e_QueryContent2
        End Get
        Set(ByVal value As String)
            Me.e_QueryContent2 = value
        End Set
    End Property

    Private e_QueryContent3 As String '查询内容3
    Public Property QueryContent3 As String
        Get
            Return Me.e_QueryContent3
        End Get
        Set(ByVal value As String)
            Me.e_QueryContent3 = value
        End Set
    End Property

    Private e_CompositionRelation1 As String '组合关系1
    Public Property CompositionRelation1 As String
        Get
            Return Me.e_CompositionRelation1
        End Get
        Set(ByVal value As String)
            Me.e_CompositionRelation1 = value
        End Set
    End Property

    Private e_CompositionRelation2 As String '组合关系2
    Public Property CompositionRelation2 As String
        Get
            Return Me.e_CompositionRelation2
        End Get
        Set(ByVal value As String)
            Me.e_CompositionRelation2 = value
        End Set
    End Property

End Class

''' <summary>
    ''' 查询学生基本信息
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub btnQuery_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles btnQuery.Click
        Dim queryComposition As New Entity.QueryCompositon
        Dim studentManager As New BLLManger.StudentManager
        Dim table As New DataTable
        Dim controlArray(2) As Control

        Try
            '*****************************************************
            '第一行查询条件都不能为空，当第一个组合关系为空时，
            '后面的条件都无效。当第一个组合关系不为空时，确定
            '第二行查询条件不能为空，当第二个组合关系不为空时
            '第三行查询条件不能为空。第二个组合关心为空时，第三行
            '查询条件无效
            '******************************************************
            controlArray(0) = comboField1 '字段一
            controlArray(1) = comboOperator1 '操作符一
            controlArray(2) = txtContent1 '查询内容一

            If UICommonFunction.IsSomeEmptyText(controlArray) Then '输入用空时
                Exit Sub
            End If

            If comboRelation1.Text.Trim <> "" Then
                controlArray(0) = comboField2 '字段二
                controlArray(1) = comboOperator2 '操作符二
                controlArray(2) = txtContent2 '查询内容二

                If UICommonFunction.IsSomeEmptyText(controlArray) Then '输入用空时
                    Exit Sub
                End If

                If comboRelation2.Text.Trim <> "" Then '第二个组合关系不为空
                    controlArray(0) = comboField3 '字段三
                    controlArray(1) = comboOperator3 '操作符三
                    controlArray(2) = txtContent3 '查询内容三

                    If UICommonFunction.IsSomeEmptyText(controlArray) Then '输入用空时
                        Exit Sub
                    End If
                End If
            Else
                comboRelation2.Text = "" '第一个组合关系为空时，第二个组合关系为空
            End If

            queryComposition.FiledName1 = comboField1.Text '字段一
            queryComposition.FiledName2 = comboField2.Text '字段二
            queryComposition.FiledName3 = comboField3.Text '字段三
            queryComposition.Operator1 = comboOperator1.Text '操作符一
            queryComposition.Operator2 = comboOperator2.Text '操作符二
            queryComposition.Operator3 = comboOperator3.Text '操作符三
            queryComposition.QueryContent1 = txtContent1.Text '查询内容一
            queryComposition.QueryContent2 = txtContent2.Text '查询内容二
            queryComposition.QueryContent3 = txtContent3.Text '查询内容三
            queryComposition.CompositionRelation1 = comboRelation1.Text '组合关心一
            queryComposition.CompositionRelation2 = comboRelation2.Text '组合关心二

            table = studentManager.QueryStudent(queryComposition) '查询学生基本信息

            DataGridView1.Rows.Clear() '清空DataGridView中的数据
            'DataGridView1.DataSource = table
            For i = 0 To table.Rows.Count - 1 '将table表中的内容显示在DataGridView中
                DataGridView1.Rows.Add()
                DataGridView1.Rows(i).Cells(0).Value = table.Rows(i)(0) '卡号
                DataGridView1.Rows(i).Cells(1).Value = table.Rows(i)(2) '姓名
                DataGridView1.Rows(i).Cells(2).Value = table.Rows(i)(1) '学号
                DataGridView1.Rows(i).Cells(3).Value = table.Rows(i)(9) '金额
                DataGridView1.Rows(i).Cells(4).Value = table.Rows(i)(5) '系别
                DataGridView1.Rows(i).Cells(5).Value = table.Rows(i)(4) '年级
                DataGridView1.Rows(i).Cells(6).Value = table.Rows(i)(6) '班级
                DataGridView1.Rows(i).Cells(7).Value = table.Rows(i)(3) '性别
                DataGridView1.Rows(i).Cells(8).Value = table.Rows(i)(7) '状态
                DataGridView1.Rows(i).Cells(9).Value = table.Rows(i)(8) '备注

            Next i
            DataGridView1.Rows(0).Selected = False

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

下面是U层代码中用到的判断文本框为空的函数：

    ''' <summary>
    ''' 判断窗体中所有文本框、组合框输入内容是否为空，若窗体中有允许为空的文本框或组合框，则不能使用此函数
    ''' 有空时返回true 没有空时返回false
    ''' </summary>
    ''' <param name="frm"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function IsAllEmptyText(ByVal frm As Form) As Boolean
        Dim control As New Control

        For Each control In frm.Controls '遍历窗体中所有的控件
            'MsgBox(frm.Controls.Count)
            If TypeOf control Is TextBox Then '判断控件是不是文本框
                If control.Text.Trim = "" Then '判断文本框内容是否为空
                    MsgBox(control.Tag.ToString + "不能为空！",vbOKOnly,"温馨提示")
                    control.Focus()
                    Return True
                    Exit Function
                End If
            ElseIf TypeOf control Is ComboBox Then '判断控件是不是组合框
                If control.Text.Trim = "" Then
                    MsgBox(control.Tag.ToString + "不能为空！","温馨提示")
                    Return True
                    Exit Function
                End If
            End If
        Next

        Return False
    End Function

    ''' <summary>
    ''' 判断控件数组中的控件的Text属性是否为空,有空时返回true
    ''' </summary>
    ''' <param name="arrayControl"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function IsSomeEmptyText(ByVal arrayControl() As Control) As Boolean
        Dim control As New Control

        For Each control In arrayControl '遍历数组中所有元素
            If TypeOf control Is TextBox Then '判断控件是不是文本框
                If control.Text.Trim = "" Then '判断文本框内容是否为空
                    MsgBox(control.Tag.ToString + "不能为空！","温馨提示")
                    Return True
                    Exit Function
                End If
            End If
        Next

        Return False
    End Function

''' <summary>
    ''' 组合查询学生信息
    ''' </summary>
    ''' <param name="queryComposition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function QueryStudent(ByVal queryComposition As Entity.QueryCompositon) As DataTable
        Dim tempQueryComposition As New Entity.QueryCompositon '临时的组合查询实体
        Dim IStudent As dataAccess.IStudent
        Dim table As New DataTable

        Try
            queryComposition = ModifyFileds(queryComposition) '改变queryComposition中的字段

            IStudent = dataAccess.DataAccess.CreateStudent()

            '生成可直接传到D层的queryComposition对象
            tempQueryComposition = CommonFunction.CreateQueryContent(queryComposition)

            table = IStudent.GroupQuery(tempQueryComposition) '按照条件查询，这个方法在D层实现
            Return table

        Catch ex As Exception
            Throw ex
        End Try
    End Function

这是B层用到的两个方法：

 ''' <summary>
    ''' 将queryComposition中的属性改成数据库中对应的字段名
    ''' </summary>
    ''' <param name="queryComposition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function ModifyFileds(ByVal queryComposition As Entity.QueryCompositon) As Entity.QueryCompositon
        Dim tempString As String

        '改变第一个字段
        Select Case queryComposition.FiledName1
            Case "卡号"
                tempString = "Card_NO"
            Case "学号"
                tempString = "Student_NO"
            Case "姓名"
                tempString = "Name"
            Case "性别"
                tempString = "Sex"
            Case "系别"
                tempString = "Department"
            Case "年级"
                tempString = "Grade"
            Case "班号"
                tempString = "Class"
            Case Else
                tempString = ""
        End Select
        queryComposition.FiledName1 = tempString


        '改变第二个字段
        Select Case queryComposition.FiledName2
            Case "卡号"
                tempString = "Card_NO"
            Case "学号"
                tempString = "Student_NO"
            Case "姓名"
                tempString = "Name"
            Case "性别"
                tempString = "Sex"
            Case "系别"
                tempString = "Department"
            Case "年级"
                tempString = "Grade"
            Case "班号"
                tempString = "Class"
            Case Else
                tempString = ""
        End Select
        queryComposition.FiledName2 = tempString

        '改变第三个字段
        Select Case queryComposition.FiledName3
            Case "卡号"
                tempString = "Card_NO"
            Case "学号"
                tempString = "Student_NO"
            Case "姓名"
                tempString = "Name"
            Case "性别"
                tempString = "Sex"
            Case "系别"
                tempString = "Department"
            Case "年级"
                tempString = "Grade"
            Case "班号"
                tempString = "Class"
            Case Else
                tempString = ""
        End Select
        queryComposition.FiledName3 = tempString

        '改变组合关系字段一
        Select Case queryComposition.CompositionRelation1
            Case "与"
                tempString = "and"
            Case "或"
                tempString = "or"
            Case Else
                tempString = "No"'用No标记没有选择组合关系

        End Select
        queryComposition.CompositionRelation1 = tempString

        '改变组合关系字段一
        Select Case queryComposition.CompositionRelation2
            Case "与"
                tempString = "and"
            Case "或"
                tempString = "or"
            Case Else
                tempString = "No"

        End Select
        queryComposition.CompositionRelation2 = tempString

        Return queryComposition
    End Function

下面是CommonFunction类里面的一个方法：

 ''' <summary>
    ''' 生成查询条件，这里生成的QueryComposition对象可直接传到D层使用
    ''' </summary>
    ''' <param name="queryComposition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function CreateQueryContent(ByVal queryComposition As Entity.QueryCompositon) As Entity.QueryCompositon
        Dim tempQueryComposition As New Entity.QueryCompositon '临时的组合查询实体

        Try

            '将第一行查询内容写到queryContent1属性中
            tempQueryComposition.QueryContent1 = queryComposition.FiledName1 + " " + queryComposition.Operator1 + _
                                                "'" + queryComposition.QueryContent1 + "'"

            '将第二行查询内容写到queryContent2属性中
            tempQueryComposition.QueryContent2 = queryComposition.FiledName2 + " " + queryComposition.Operator2 + _
                                                "'" + queryComposition.QueryContent2 + "'"

            '将第三行查询内容写到queryContent3属性中
            tempQueryComposition.QueryContent3 = queryComposition.FiledName3 + " " + queryComposition.Operator3 + _
                                                "'" + queryComposition.QueryContent3 + "'"
            '给第一个组合关系赋值
            tempQueryComposition.CompositionRelation1 = queryComposition.CompositionRelation1

            '给第二个组合关系赋值
            tempQueryComposition.CompositionRelation2 = queryComposition.CompositionRelation2

            Return tempQueryComposition

        Catch ex As Exception
            Throw ex
        End Try
    End Function

''' <summary>
    ''' 组合查询,需要queryComposition的两个compositionRelation属性,该属性为空时，
    ''' 用No标记，三个queryContent属性，属性应为查询条件，如“Card_NO = '1'”。
    ''' </summary>
    ''' <param name="queryComposition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GroupQuery(ByVal queryComposition As Entity.QueryCompositon) As System.Data.DataTable Implements dataAccess.IStudent.GroupQuery
        Dim sqlString As String
        Dim tableName As String
        Dim table As New DataTable

        Try
            'If queryComposition.CompositionRelation1.Trim = "No" Then '第一个组合关系为空，查询条件只有第一个
            '    sqlString = "Select * from Student_Info Where " + queryComposition.QueryContent1
            'Else
            '    '查询条件的前两个有效
            '    sqlString = "Select * from Student_Info Where " + queryComposition.QueryContent1 + _
            '                queryComposition.CompositionRelation1 + queryComposition.QueryContent2
            'End If

            'If queryComposition.CompositionRelation1.Trim <> "No" Then '第二个组合关系不为空，查询条件需满足为三个
            '    sqlString = sqlString + queryComposition.CompositionRelation2 + queryComposition.QueryContent3

            'End If
            tableName = "Student_Info" '需要操作的表

            '生成需要查询的sql语句
            sqlString = CreateSqlString.CreateSqlString(queryComposition,tableName)

            table = SQLHelper.GetDataTable(sqlString)'这里的代码就不展示了，都差不多
            Return table

        Catch ex As Exception
            Throw ex
        End Try
    End Function

下面是D层用到的函数 ：

 ''' <summary>
    ''' 根据组合查询实体得到查询字符串
    ''' </summary>
    ''' <param name="queryComposition">
    ''' 从B层传到D层的queryComposition对象
    ''' </param>
    ''' <param name="tableName">
    ''' 要查询的表名
    ''' </param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function CreateSqlString(ByVal queryComposition As Entity.QueryCompositon,ByVal tableName As String) As String
        Dim sqlString As String

        Try
            If queryComposition.CompositionRelation1.Trim = "No" Then '第一个组合关系为空，查询条件只有第一个
                sqlString = "Select * from " + tableName + " Where " + queryComposition.QueryContent1
            Else
                '查询条件的前两个有效
                sqlString = "Select * from " + tableName + " Where " + queryComposition.QueryContent1 + _
                            queryComposition.CompositionRelation1 + " " + queryComposition.QueryContent2
            End If

            If queryComposition.CompositionRelation2.Trim <> "No" Then '第二个组合关系不为空，查询条件需满足为三个
                sqlString = sqlString + queryComposition.CompositionRelation2 + " " + queryComposition.QueryContent3

            End If

            Return sqlString

        Catch ex As Exception
            Throw ex
        End Try
    End Function

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!