机房重构组合查询

      进行了机房重构组合查询，涉及到一个模板设计模式。很简单的理解，就像先制作好一个模板样式，然后再创建继承这个窗体的子窗体。

       1、为什么要用模板

        有了模板窗体之后，很大程度上方便了我们创造与之相似度很大（窗体界面相同，控件属性相同，公共的方法抽象出来放在模板窗体中）的继承子窗体（子窗体这时候可以有自己私有的属性和方法）。减少了重复的代码量，和相同方法的声明定义（只需重写即可），在继承子窗体中可以更清楚的明白个体继承子窗体之间的区别在那。

        2、如何创建模板并使用模板

       他们的实体界面在未运行前有一定的区别，如何创建如下操作：

       1）首先在UI层添加一个普通的窗体，把窗体的界面设置好，对控件的属性和抽象方法写在代码层。

        

Imports System.Data
Imports System.IO
Imports Entity

Public Class frmGroupQuery
    '定义一个实体，用于接收文本框或者时间空间传递的内容
    Public txt1 As String = ""
    Public txt2 As String = ""
    Public txt3 As String = ""
    Protected GroupQueryInfo As New Entity.E_GroupQueryEntity

    '窗体加载
    Private Sub frmGroupQuery_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        cmbOperator1.Items.Add(">")
        cmbOperator1.Items.Add("<")
        cmbOperator1.Items.Add("=")
        cmbOperator1.Items.Add("<>")

        cmbOperator2.Items.Add(">")
        cmbOperator2.Items.Add("<")
        cmbOperator2.Items.Add("=")
        cmbOperator2.Items.Add("<>")

        cmbOperator3.Items.Add(">")
        cmbOperator3.Items.Add("<")
        cmbOperator3.Items.Add("=")
        cmbOperator3.Items.Add("<>")

        '关系    
        cmbRelations1.Items.Add("与")
        cmbRelations1.Items.Add("或")
        cmbRelations1.Items.Add("")

        cmbRelations2.Items.Add("与")
        cmbRelations2.Items.Add("或")
        cmbRelations2.Items.Add("")

        'DateTimePicker控件的可见性  
        dtp1.Visible = False
        dtp2.Visible = False
        dtp3.Visible = False

        '在加载的时候，只有第一行控件可用，后两行控件不可用    
        cmbField2.Enabled = False
        cmbField3.Enabled = False
        cmbOperator2.Enabled = False
        cmbOperator3.Enabled = False
        cmbRelations2.Enabled = False
        txtContent2.Enabled = False
        txtContent3.Enabled = False

        '当选中dgvGroupCheck控件就选中行    
        dgvGroupCheck.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        Dim i As Integer
        For i = 0 To dgvGroupCheck.Columns.Count - 1
            dgvGroupCheck.Columns(i).Width = DataGridViewAutoSizeColumnMode.AllCells
        Next

    End Sub

    '清除功能
    Private Sub btnClear_Click(sender As Object, e As EventArgs) Handles btnClear.Click
        cmbField1.Text = ""
        cmbField2.Text = ""
        cmbField3.Text = ""

        cmbOperator1.Text = ""
        cmbOperator2.Text = ""
        cmbOperator3.Text = ""

        txtContent1.Text = ""
        txtContent2.Text = ""
        txtContent3.Text = ""

        cmbRelations1.Text = ""
        cmbRelations2.Text = ""

        dgvGroupCheck.DataSource = ""
    End Sub

    Private Sub btnQuery_Click(sender As Object, e As EventArgs) Handles btnQuery.Click



        dgvGroupCheck.DataSource = ""

        '判断组合框不为空    
        If cmbRelations1.Text = "" Then '如果第一个组合关系框为空    
            If cmbField1.Text = "" Or cmbOperator1.Text = "" Or txt1 = "" Then
                MsgBox("第一行查询条件不能为空！", , "提示")
                Exit Sub
            End If
        End If

        If cmbRelations1.Text <> "" Then '如果选择了第一个组合关系    
            If cmbField2.Text = "" Or cmbOperator2.Text = "" Or txt2 = "" Then
                MsgBox("第二行查询条件不能为空！", , "提示")
                Exit Sub
            End If
        Else
            If cmbRelations2.Text <> "" Then '选择了第二个组合关系    
                If cmbField1.Text = "" Or cmbOperator1.Text = "" Or txt1 = "" Or
                    cmbField2.Text = "" Or cmbField2.Text = "" Or txt2 = "" Or
                    cmbField3.Text = "" Or cmbField3.Text = "" Or txt3 = "" Then
                    MsgBox("第三行查询条件不能为空", , "提示")
                    Exit Sub
                End If
            End If
        End If

        '给实体赋值    
        GroupQueryInfo.tableName = GetTable()

        GroupQueryInfo.cmbField1 = GetDBName(cmbField1.Text.Trim())
        GroupQueryInfo.cmbField2 = GetDBName(cmbField2.Text.Trim())
        GroupQueryInfo.cmbField3 = GetDBName(cmbField3.Text.Trim())

        GroupQueryInfo.cmbOperator1 = cmbOperator1.Text.Trim()
        GroupQueryInfo.cmbOperator2 = cmbOperator2.Text.Trim()
        GroupQueryInfo.cmbOperator3 = cmbOperator3.Text.Trim()

        GroupQueryInfo.txtContent1 = txt1
        GroupQueryInfo.txtContent2 = txt2
        GroupQueryInfo.txtContent3 = txt3

        GroupQueryInfo.cmbRelations1 = GetDBName(cmbRelations1.Text.Trim())
        GroupQueryInfo.cmbRelations2 = GetDBName(cmbRelations2.Text.Trim())

        '给外观层方法传递参数    
        Dim table As DataTable
        Dim Fgroupcheck As New Facade.Fe_GroupQueryFacade
        Call Rdim()
        If CheckSpecialString(arrayControl) Then
            Exit Sub
        End If

        table = Fgroupcheck.GroupQuery(GroupQueryInfo)
        If (table.Rows.Count = 0) Then
            MsgBox("没有符合条件的记录，请重新设置查询条件", , "提示")
            dgvGroupCheck.DataSource = Nothing
        Else
            Call Todatagridview()
        End If
    End Sub

    '定义虚函数获取字段名
    Public Overridable Function GetDBName(ByVal control As String) As String
        Return ""
    End Function
    '定义虚函数获取数据库名
    Protected Overridable Function GetTable() As String
        Return ""
    End Function
    '把表显示到dgvGroupCheck中    
    Protected Overridable Sub Todatagridview()
        dgvGroupCheck.AutoSizeColumnsMode = DataGridViewAutoSizeColumnMode.AllCells
    End Sub

    '当一个组合关系框的内容发生改变时，设置第二行的查询框状态
    Private Sub cmbRelations1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbRelations1.SelectedIndexChanged
        cmbField2.Enabled = True
        cmbOperator2.Enabled = True
        txtContent2.Enabled = True
        cmbRelations2.Enabled = True
    End Sub

    '当第二个组合关系框的内容发生改变时,设置第三行查询框的状态（是否可用）  
    Private Sub cmbRelations2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbRelations2.SelectedIndexChanged
        cmbField3.Enabled = True
        cmbOperator3.Enabled = True
        txtContent3.Enabled = True
    End Sub

    '针对查询与日期、时间有关的问题  
    Private Sub cmbField1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbField1.SelectedIndexChanged

        If cmbField1.Text = "上机日期" Or cmbField1.Text = "下机日期" Or cmbField1.Text = "上机时间" Or cmbField1.Text = "下机时间" Then
            dtp1.Format = DateTimePickerFormat.Custom
            dtp1.Visible = True
            txtContent1.Visible = False

            If (cmbField1.Text = "上机日期" Or cmbField1.Text = "下机日期") = True Then
                txt1 = Format(dtp1.Value, "yyyy-MM-dd")
            Else
                dtp1.Format = DateTimePickerFormat.Time
                txt1 = Format(dtp1.Value, "hh:mm:ss")
            End If
        Else
            dtp1.Visible = False
            txtContent1.Visible = True
            txt1 = txtContent1.Text
        End If
 
    End Sub

    '针对查询与日期、时间有关的问题  
    Private Sub cmbField2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbField2.SelectedIndexChanged

        If cmbField2.Text = "上机日期" Or cmbField2.Text = "下机日期" Or cmbField2.Text = "上机时间" Or cmbField2.Text = "下机时间" Then
            dtp2.Format = DateTimePickerFormat.Custom
            dtp2.Visible = True
            txtContent2.Visible = False
            If cmbField2.Text = "上机日期" Or cmbField2.Text = "下机日期" Then
                txt2 = Format(dtp2.Value, "yyyy-MM-dd")
            Else

                dtp2.Format = DateTimePickerFormat.Time
                txt2 = Format(dtp2.Value, "hh:mm:ss")
            End If
        Else
            dtp2.Visible = False
            txtContent2.Visible = True
            txt2 = txtContent2.Text
        End If
    End Sub

    '针对查询与日期、时间有关的问题  
    Private Sub cmbField3_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbField3.SelectedIndexChanged

        If cmbField3.Text = "上机日期" Or cmbField3.Text = "下机日期" Or cmbField3.Text = "上机时间" Or cmbField3.Text = "下机时间" Then
            dtp3.Format = DateTimePickerFormat.Custom
            dtp3.Visible = True
            txtContent3.Visible = False
            Exit Sub
            If cmbField3.Text = "上机日期" Or cmbField3.Text = "下机日期" Then
                txt3 = Format(dtp3.Value, "yyyy-MM-dd")
                Exit Sub
            ElseIf cmbField3.Text = "上机时间" Or cmbField3.Text = "下机时间" Then
                dtp3.Format = DateTimePickerFormat.Time
                txt3 = Format(dtp3.Value, "hh:mm:ss")
                Exit Sub
            End If
        Else
            dtp3.Visible = False
            txtContent3.Visible = True
            txt3 = txtContent3.Text
        End If

    End Sub

    Private Sub txtContent1_TextChanged(sender As Object, e As EventArgs) Handles txtContent1.TextChanged
        txt1 = txtContent1.Text
    End Sub

    Private Sub txtContent2_TextChanged(sender As Object, e As EventArgs) Handles txtContent2.TextChanged
        txt2 = txtContent2.Text
    End Sub

    Private Sub txtContent3_TextChanged(sender As Object, e As EventArgs) Handles txtContent3.TextChanged
        txt3 = txtContent3.Text
    End Sub

    Private Sub btnToExcel_Click(sender As Object, e As EventArgs) Handles btnToExcel.Click
        Call ImportToExcel.ImportToExcel(dgvGroupCheck)
    End Sub

          2）创建继承的窗体

           

          继承的窗体与创建的模板在于继承的窗体上所有控件为锁着状态，即不可以更改属性。
          继承的窗体在代码层面可以定义自己私有的方法和变量。

          

Public Class frmWorkQuery
    '单例模式  
    Private Shared frmWorkQuery As New frmWorkQuery

    Private Sub New()
        ' 此调用是设计器所必需的。
        InitializeComponent()
        ' 在 InitializeComponent() 调用之后添加任何初始化。
    End Sub
    '单例模式当主窗体点击按钮时获取该窗体的方法
    Public Shared Function Getinstance() As frmWorkQuery
        If frmWorkQuery Is Nothing OrElse frmWorkQuery.IsDisposed Then
            frmWorkQuery = New frmWorkQuery
        End If
        Return frmWorkQuery
    End Function

    Public Overrides Function GetDBName(control As String) As String
        Select Case (control)
            Case "教师"
                Return "UserID"
            Case "级别"
                Return "Level"
            Case "上机日期"
                Return "LoginDate"
            Case "上机时间"
                Return "LoginTime"
            Case "下机日期"
                Return "LogoutDate"
            Case "下机时间"
                Return "LogoutTime"
            Case "机器名"
                Return "Computer"
            Case "状态"
                Return "Online"
            Case "与"
                Return "and"
            Case "或"
                Return "or"
            Case Else
                Return ""
        End Select
    End Function

    Protected Overrides Function GetTable() As String
        GroupQueryInfo.tableName = "WorkLog_Info"
        Return GroupQueryInfo.tableName
    End Function

    Private Sub frmWorkRecord_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        cmbField1.Items.Add("教师")
        cmbField1.Items.Add("上机日期")
        cmbField1.Items.Add("上机时间")
        cmbField1.Items.Add("下机日期")
        cmbField1.Items.Add("下机时间")
        cmbField1.Items.Add("机器名")

        cmbField2.Items.Add("教师")
        cmbField2.Items.Add("上机日期")
        cmbField2.Items.Add("上机时间")
        cmbField2.Items.Add("下机日期")
        cmbField2.Items.Add("下机时间")
        cmbField2.Items.Add("机器名")

        cmbField3.Items.Add("教师")
        cmbField3.Items.Add("上机日期")
        cmbField3.Items.Add("上机时间")
        cmbField3.Items.Add("下机日期")
        cmbField3.Items.Add("下机时间")
        cmbField3.Items.Add("机器名")

    End Sub

    Protected Overrides Sub Todatagridview()
        Dim table As New DataTable
        Dim frmGroupQuery As New frmGroupQuery
        Dim FacadeGroupQuery As New Facade.Fe_GroupQueryFacade

        Try
            table = FacadeGroupQuery.GroupQuery(GroupQueryInfo)
            If table.Rows.Count = 0 Then
                table.Clear()
                dgvGroupCheck.DataSource = Nothing
                dgvGroupCheck.Refresh()
                MessageBox.Show("记录为空")
            Else
                dgvGroupCheck.DataSource = table

                dgvGroupCheck.Columns(0).HeaderText = "用户名"
                dgvGroupCheck.Columns(1).HeaderText = "级别"
                dgvGroupCheck.Columns(2).HeaderText = "上机日期"
                dgvGroupCheck.Columns(3).HeaderText = "上机时间"
                dgvGroupCheck.Columns(4).HeaderText = "下机日期"
                dgvGroupCheck.Columns(5).HeaderText = "下机时间"
                dgvGroupCheck.Columns(6).HeaderText = "机器名"
                dgvGroupCheck.Columns(7).HeaderText = "状态"
            End If

        Catch ex As Exception
            MsgBox(ex.Message, vbOKOnly, "提示")
        End Try

    End Sub

End Class