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

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

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

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

下面是代码实现过程：

实体类：

''' <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 PropertyEnd Class

U层代码：

''' <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

B层代码：

''' <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

D层代码：

''' <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

上面就是完整的组合查询实现过程，还望大家多多指教！！ 总结

以上是内存溢出为你收集整理的VB.NET 机房收费系统之组合查询全部内容，希望文章能够帮你解决VB.NET 机房收费系统之组合查询所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。