机房重构（5）——模板方法实现组合查询

    在敲机房收费过程中，我们会发现很多窗体除了一些细微的差别外，基本是一模一样的，功能的实现也是大同小异。在第一次机房收费的时候，我们都是“好学生”，尽管代码重复率极高，还是按部就班的一个个的实现。但在学习了设计模式，机房重构的现在，再傻傻的重复代码，就不是明智之举了。

    整个收费系统中，总计有四个组合查询的功能（界面如图），为了提高代码复用率，提高效率，就引入了模板方法实现。

    

    模板方法模式，定义了一个操作中的算法的骨架，把一些步骤延迟到子类当中。它使得子类可以不改变一个算法的结构即可重定义该算法的某些特定步骤。它提供了一个代码复用的平台，消除代码冗余带来的弊端，使系统更易于维护。

    那下面就通过机房收费上机学生的组合查询来体会一下模板方法的魅力。

    1、建立模板父窗体

     添加Windows窗体，设计模板界面（如上图），并在模板窗体里写入抽象出来的类和方法的代码。

Public Class frmGroupQuery

   
    Protected enGroupQuery As New GroupQueryEntity

    Protected Sub frmGroupQuery_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        '将参数传递给实体，赋初值
        '因为不同窗体字段不同，所以赋“”，子窗体重写它
        enGroupQuery.cboName1 = ""
        enGroupQuery.cboName2 = ""
        enGroupQuery.cboName3 = ""

        '操作符
        cboMark1.Items.Add(">")
        cboMark1.Items.Add("<")
        cboMark1.Items.Add("=")
        cboMark1.Items.Add("<>")

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

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

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

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

        '窗体加载后，后两组控件默认不可用
        cboName2.Enabled = False
        cboName3.Enabled = False
        cboMark2.Enabled = False
        cboMark3.Enabled = False

        cboRelation2.Enabled = False
        txtContent2.Enabled = False
        txtContent3.Enabled = False

        '设置选中单元格就选中行
        dgvRecord.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        Dim i As Integer

        For i = 0 To dgvRecord.Columns.Count - 1
            dgvRecord.Columns(i).Width = DataGridViewAutoSizeColumnsMode.AllCells
        Next

    End Sub

    Protected Sub btnQuery_Click(sender As Object, e As EventArgs) Handles btnQuery.Click
        '判断组合框不为空

        If cboRelation1.Text = "" Then
            If cboName1.Text = "" Or cboMark1.Text = "" Or txtContent1.Text = "" Then
                MsgBox("第一行查询条件不能为空，请完善查询信息！", CType(vbOKOnly + MsgBoxStyle.Exclamation, MsgBoxStyle), "提示")
                Exit Sub
            End If
        End If

        If cboRelation1.Text <> "" Then
            If cboName1.Text = "" Or cboMark1.Text = "" Or txtContent1.Text = "" Or
                cboName2.Text = "" Or cboMark2.Text = "" Or txtContent2.Text = "" Then
                MsgBox("第二行查询条件不能为空，请完善查询信息！", CType(vbOKOnly + MsgBoxStyle.Exclamation, MsgBoxStyle), "提示")
                Exit Sub
            End If
        Else
            If cboRelation2.Text <> "" Then
                If cboName1.Text = "" Or cboMark1.Text = "" Or txtContent1.Text = "" Or
                cboName2.Text = "" Or cboMark2.Text = "" Or txtContent2.Text = "" Or
                cboName3.Text = "" Or cboMark3.Text = "" Or txtContent3.Text = "" Then

                    MsgBox("第三行查询条件不能为空，请完善查询信息！", CType(vbOKOnly + MsgBoxStyle.Exclamation, MsgBoxStyle), "提示")
                    Exit Sub
                End If

            End If

        End If



        '将参数传给实体
        enGroupQuery.dbName = GetdbName()
        enGroupQuery.cboName1 = ToEnglish(cboName1.Text)
        enGroupQuery.cboName2 = ToEnglish(cboName2.Text)
        enGroupQuery.cboName3 = ToEnglish(cboName3.Text)

        enGroupQuery.cboMark1 = cboMark1.Text.Trim
        enGroupQuery.cboMark2 = cboMark2.Text.Trim
        enGroupQuery.cboMark3 = cboMark3.Text.Trim

        enGroupQuery.txtContent1 = txtContent1.Text.Trim
        enGroupQuery.txtContent2 = txtContent2.Text.Trim
        enGroupQuery.txtContent3 = txtContent3.Text.Trim
        ''前者还是后者
        enGroupQuery.cboRelation1 = ToEnglish(cboRelation1.Text)
        enGroupQuery.cboRelation2 = ToEnglish(cboRelation2.Text)


       
        Dim dt As New DataTable
        Dim GroupQueryBLL As New BLL.GroupQueryBLL

        Call Todgv()


    End Sub
    ''' <summary>
    ''' 模板方法，定义虚函数ToEnglish，查询字段转化为数据库字段
    ''' </summary>
    ''' <param name="cboName"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Overridable Function ToEnglish(cboName As String) As String
        Return ""
    End Function


    ''' <summary>
    ''' 获得数据库表名
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Overridable Function GetdbName() As String
        Return ""
    End Function

    ''' <summary>
    ''' 把表显示到datagridview中
    ''' </summary>
    ''' <remarks></remarks>
    Protected Overridable Sub Todgv()

    End Sub

    ''' <summary>
    ''' 拼接字符串
    ''' </summary>
    ''' <param name="frm"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function Query(frm As frmGroupQuery) As String
        Dim cmdText As String = "" & frm.ToEnglish(frm.cboName1.Text) & frm.cboMark1.Text & "'" & frm.txtContent1.Text & "'"
        '非组合查询
        If frm.cboRelation1.Text = "" Then
            cmdText = cmdText
        Else
            '关系1为空，关系2不为空
            If frm.cboRelation1.Text <> "" Then
                cmdText = cmdText & frm.ToEnglish(frm.cboRelation1.Text) & "" &
                    frm.ToEnglish(frm.cboName2.Text) & frm.cboMark2.Text & "'" & frm.txtContent2.Text & "'"
            Else
                '关系1、2 都不为空
                cmdText = cmdText & frm.ToEnglish(frm.cboRelation1.Text) & "" &
                    frm.ToEnglish(frm.cboName2.Text) & frm.cboMark2.Text & "'" & frm.txtContent2.Text & "'" & "" &
                    frm.ToEnglish(frm.cboRelation2.Text) & "" &
                    frm.ToEnglish(frm.cboName3.Text) & frm.cboMark3.Text & "'" & frm.txtContent2.Text & "'"
            End If
        End If
        Return cmdText
    End Function

    ''' <summary>
    ''' 第一个组合关系不为空
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Protected Sub cboRelation1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboRelation1.SelectedIndexChanged
        If cboRelation1.Text = "" Then
            cboName2.Enabled = False
            cboName3.Enabled = False
            cboMark2.Enabled = False
            cboMark3.Enabled = False

            cboRelation2.Enabled = False
            txtContent2.Enabled = False
            txtContent3.Enabled = False
        Else
            cboName2.Enabled = True
            cboMark2.Enabled = True
            cboRelation2.Enabled = True
            txtContent2.Enabled = True

        End If

    End Sub


    ''' <summary>
    ''' 第二个组合关系不为空
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Protected Sub cboRelation2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboRelation2.SelectedIndexChanged
        If cboRelation2.Text = "" Then
            cboName3.Enabled = False
            cboMark3.Enabled = False
            txtContent3.Enabled = False
        Else
            cboName3.Enabled = True
            cboMark3.Enabled = True
            txtContent3.Enabled = True
        End If
    End Sub

    Protected Sub btnCancel_Click(sender As Object, e As EventArgs) Handles btnCancel.Click
        Me.Close()
    End Sub

End Class

    2、建立子窗体

    如下图建立子窗体，选择继承创建的父窗体模板，然后就可以得到一模一样的子窗体了。通过在子窗体里重写一些方法和类，以实现不同的功能就可以了。

    

    

    实现上机学生查询的代码如下：

Public Class frmOnlineQuery
    ''' <summary>
    ''' 加载combo的item
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub frmOnlineQuery_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        cboName1.Items.Add("卡号")
        cboName1.Items.Add("姓名")
        cboName1.Items.Add("上机日期")
        cboName1.Items.Add("上机时间")
        cboName1.Items.Add("机器名")

        cboName2.Items.Add("卡号")
        cboName2.Items.Add("姓名")
        cboName2.Items.Add("上机日期")
        cboName2.Items.Add("上机时间")
        cboName2.Items.Add("机器名")

        cboName3.Items.Add("卡号")
        cboName3.Items.Add("姓名")
        cboName3.Items.Add("上机日期")
        cboName3.Items.Add("上机时间")
        cboName3.Items.Add("机器名")

    End Sub
    ''' <summary>
    ''' 把加载的汉字转换成数据库的字段
    ''' </summary>
    ''' <param name="cboName"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Overrides Function ToEnglish(cboName As String) As String
        Select Case cboName
            Case "卡号"
                ToEnglish = "CardID"
            Case "姓名"
                ToEnglish = "StuID"
            Case "上机日期"
                ToEnglish = "StuLoginDate"
            Case "上机时间"
                ToEnglish = "StuLoginTime"
            Case "机器名"
                ToEnglish = "Computer"
            Case "与"
                ToEnglish = "and"
            Case "或"
                ToEnglish = "or"
            Case Else
                ToEnglish = ""
        End Select
    End Function

    ''' <summary>
    ''' 传数据库表名
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Overrides Function GetdbName() As String
        Return "T_OnLineInfo"
    End Function

    ''' <summary>
    ''' 把数据显示到datagridview中
    ''' </summary>
    ''' <remarks></remarks>
    Protected Overrides Sub Todgv()
        Dim dt As New DataTable
        Dim frmGropQuery As New frmGroupQuery
        Dim GroupQueryBLL As New BLL.GroupQueryBLL

        Try
            dt = GroupQueryBLL.GroupQuery(enGroupQuery)

            If dt.Rows.Count = 0 Then
                dt.Clear()
                dgvRecord.DataSource = Nothing
                dgvRecord.Refresh()
            Else
                dgvRecord.DataSource = dt
                dgvRecord.Columns(0).Visible = False
                dgvRecord.Columns(1).HeaderText = "卡号"
                dgvRecord.Columns(2).HeaderText = "姓名"
                dgvRecord.Columns(3).Visible = False
                dgvRecord.Columns(4).HeaderText = "上机日期"
                dgvRecord.Columns(5).HeaderText = "上机时间"
                dgvRecord.Columns(6).Visible = False
                dgvRecord.Columns(7).Visible = False
                dgvRecord.Columns(8).Visible = False
                dgvRecord.Columns(9).Visible = False
                dgvRecord.Columns(10).Visible = False
                dgvRecord.Columns(11).Visible = False
                dgvRecord.Columns(12).HeaderText = "机器名"

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

End Class

    其余的三个窗体也就可以同样的轻松实现了，可以很好减少代码重复，大大提高编程效率，不妨试一试。