【机房重构】组合查询——模板方法

前言

    我们在学习第一次vb版机房收费系统的时候，面对组合查询，面对三个差异很小的窗体，我们的解决办法就是ctrl+c，ctrl+v。若干个月之后，我们学习了设计模式相关知识，再去解决组合查询时，才发现设计模式如此具有趣味性，它可以让我们的学习变得更加高效。

主要内容

1.模板方法模式

    大话设计模式中的实例是"考题抄错会做也白搭",其中大概意思是，如果很多内容类似，我们只需设置一个模板，让所有类似的内容使用这个模板，这时只需修改很少的部分。

    官话“模板方法模式，定义一个操作中的算法的骨架，而将一些步骤延迟到子类中。模板方法使得子类可以不改变一个算法的结构即可重定义该算法的某些特定步骤。”

    这里需要注意的是，模板方法很重要的思想是子类继承父类，通过重写实现功能。

2.实现

（1）建立父窗体，添加代码

 

UI层

Public Class frmGroupCheck
    '定义一个变量，用于接收文本框或者时间控件传递的内容
    Public txt1 As String = ""
    Public txt2 As String = ""
    Public txt3 As String = ""
    '定义实体
    Protected groupcheck As New Entity.GroupCheckEntity

    '窗体加载
    Private Sub frmGroupCheck_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("或")

        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 btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
        Me.Close()
    End Sub

    '查询
    Private Sub btnInquiry_Click(sender As Object, e As EventArgs) Handles btnInquiry.Click
        '判断组合框不为空  
        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

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

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

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

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

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

        '给外观层方法传递参数  
        Dim table As New DataTable
        Dim Fgroupcheck As New Facade.GroupCheckFacade
        table = Fgroupcheck.selectGroupCheck(groupcheck)
        If (table.Rows.Count = 0) Then
            MsgBox("没有符合条件的记录，请重新设置查询条件", , "提示")
            dgvGroupCheck.DataSource = Nothing
        Else
            Call Todatagridview()
        End If
    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

    '定义虚函数GetDBName，获取不同数据库的字段名  
    Public Overridable Function GetDBName(ByVal control As String) As String
        Return ""
    End Function

    '定义虚函数GetTable，获取不同数据库的表名  
    Protected Overridable Function GetTable() As String
        Return ""
    End Function
    '把表显示到dgvGroupCheck中  
    Protected Overridable Sub Todatagridview()
        dgvGroupCheck.AutoSizeColumnsMode = DataGridViewAutoSizeColumnMode.AllCells
    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 = "下机日期" Then
                txt1 = Format(dtp1.Value, "yyyy-MM-dd")
            ElseIf cmbField1.Text = "上机时间" Or cmbField1.Text = "下机时间" Then
                dtp1.Format = DateTimePickerFormat.Time
                txt1 = Format(dtp1.Value, "hh:mm:ss")
            End If
        Else
            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")
            ElseIf cmbField2.Text = "上机时间" Or cmbField2.Text = "下机时间" Then
                dtp2.Format = DateTimePickerFormat.Time
                txt2 = Format(dtp2.Value, "hh:mm:ss")
            End If
        Else
            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
            If cmbField3.Text = "上机日期" Or cmbField3.Text = "下机日期" Then
                txt3 = Format(dtp3.Value, "yyyy-MM-dd")
            ElseIf cmbField3.Text = "上机时间" Or cmbField3.Text = "下机时间" Then
                dtp3.Format = DateTimePickerFormat.Time
                txt3 = Format(dtp3.Value, "hh:mm:ss")
            End If
        Else
            txt3 = txtContent3.Text
        End If
    End Sub
End Class

Facade层

Public Function selectGroupCheck(GroupCheck As Entity.GroupCheckEntity) As DataTable
        Dim GroupCheckBLL As New BLL.GroupCheckBLL
        Dim table As New DataTable
        table = GroupCheckBLL.selectGroupCheck(GroupCheck)
        Return table
End Function

B层

Public Function selectGroupCheck(ByVal GroupCheck As Entity.GroupCheckEntity) As DataTable
        Dim Factory As New Factory.Fac
        Dim IGroupCheck As IDAL.IGroupCheckDAL = Factory.GroupCheck
        Dim table As New DataTable
        table = IGroupCheck.selectGroupCheck(GroupCheck)
        If table.Rows.Count = 0 Then
            Return Nothing
        Else
            Return table
        End If
End Function

D层

Imports System.Data.SqlClient
Public Class GroupCheckDAL : Implements IDAL.IGroupCheckDAL
    Private SqlHelper As SQLHelper.sqlhelper = New SQLHelper.sqlhelper()
    Public Function selectGroupCheck(ByVal GroupCheck As Entity.GroupCheckEntity) As DataTable Implements IDAL.IGroupCheckDAL.selectGroupCheck
        Dim sql As String = "PROC_GroupCheck" '调用存储过程  
        Dim table As DataTable
        Dim sqlparams As SqlParameter() = {New SqlParameter("@cmbField1", GroupCheck.cmbField1),
                                           New SqlParameter("@cmbField2", GroupCheck.cmbField2),
                                           New SqlParameter("@cmbField3", GroupCheck.cmbField3),
                                           New SqlParameter("@cmbOperator1", GroupCheck.cmbOperator1),
                                           New SqlParameter("@cmbOperator2", GroupCheck.cmbOperator2),
                                           New SqlParameter("@cmbOperator3", GroupCheck.cmbOperator3),
                                           New SqlParameter("@txtContent1", GroupCheck.txtContent1),
                                           New SqlParameter("@txtContent2", GroupCheck.txtContent2),
                                           New SqlParameter("@txtContent3", GroupCheck.txtContent3),
                                           New SqlParameter("@cmbRelations1", GroupCheck.cmbRelations1),
                                           New SqlParameter("@cmbRelations2", GroupCheck.cmbRelations2),
                                           New SqlParameter("@tableName", GroupCheck.tableName)} '设置参数  
        table = SqlHelper.GetDataTable(sql, CommandType.StoredProcedure, sqlparams)
        Return table
End Function

Entity层

    Private _cmbField1 As String
    Private _cmbField2 As String
    Private _cmbField3 As String
    Private _cmbOperator1 As String
    Private _cmbOperator2 As String
    Private _cmbOperator3 As String
    Private _txtContent1 As String
    Private _txtContent2 As String
    Private _txtContent3 As String
    Private _cmbRelations1 As String
    Private _cmbRelations2 As String
    Private _tableName As String

存储过程

USE [charge]
GO
/****** Object:  StoredProcedure [dbo].[PROC_GroupCheck]    Script Date: 04/28/2017 17:27:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================  
-- Author:     李佳丽
-- Create date: 2017-4-19 
-- Description: 用于组合查询时和数据库交互  
-- =============================================  
ALTER PROCEDURE [dbo].[PROC_GroupCheck]            
    @cmbField1 varchar(20),  
    @cmbOperator1 varchar(20),  
    @txtContent1 varchar(20),  
    @cmbField2 varchar(20),  
    @cmbOperator2 varchar(20),  
    @txtContent2 varchar(20),  
    @cmbField3 varchar(20),  
    @cmbOperator3 varchar(20),  
    @txtContent3 varchar(20),  
    @cmbRelations1 varchar(20),  
    @cmbRelations2 varchar(20),  
    @tableName varchar(20)  
AS    
    declare @TempSql varchar(500) 
BEGIN    
    SET @TempSql='SELECT * FROM '+@tableName +' WHERE ' +@cmbField1 +@cmbOperator1+char(39) + @txtContent1 + char(39)     
     if (@cmbRelations1!='')  
    BEGIN      
        SET @TempSql=@TempSql+@cmbRelations1+CHAR(32)+@cmbField2 +@cmbOperator2+CHAR(39)+@txtContent2+CHAR(39)   
        if (@cmbRelations2!='')  
        BEGIN      
        SET @TempSql=@TempSql+@cmbRelations2+CHAR(32)+@cmbField3+@cmbOperator3+CHAR(39)+@txtContent3+CHAR(39)      
        END      
  END  
EXECUTE(@TempSql)     
END  

（2）建立子窗体，添加代码

    右击命名空间（例如UI）-添加-类-已安装-常用项-Windows Forms-继承的窗体-选择继承的父类窗体

  UI层代码

Public Class frmOpWorkRecord
    '单例模式
    Private Shared frmOpWorkRecord As New frmOpWorkRecord
    Private Sub New()
        ' 此调用是设计器所必需的。
        InitializeComponent()
        ' 在 InitializeComponent() 调用之后添加任何初始化。
    End Sub
    Public Shared Function Getinstance() As frmOpWorkRecord
        If frmOpWorkRecord Is Nothing OrElse frmOpWorkRecord.IsDisposed Then
            frmOpWorkRecord = New frmOpWorkRecord
            frmOpWorkRecord.MdiParent = frmMain
        End If
        Return frmOpWorkRecord
    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
        groupcheck.tableName = "WorkLog_Info"
        Return groupcheck.tableName
    End Function

    '窗体加载
    Private Sub frmOpWorkRecord_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 frmGroupCheck As New frmGroupCheck
        Dim FacadeGroupCheck As New Facade.GroupCheckFacade
        Try
            table = FacadeGroupCheck.selectGroupCheck(groupcheck)
            If table.Rows.Count = 0 Then
                table.Clear()
                dgvGroupCheck.DataSource = Nothing
                dgvGroupCheck.Refresh()
                MsgBox("记录为空")
            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(5).HeaderText = "机器名"
                dgvGroupCheck.Columns(5).HeaderText = "状态"
            End If
        Catch ex As Exception
            MsgBox(ex.Message, vbOKOnly, "提示")
        End Try
    End Sub
End Class

3.遇到的问题

（1）@cmbRelations1!='' vs  @cmbRelations1 is not null（存储过程中）

    这个问题的关注点就在于“”与null的区别，可以查看一下数据库默认添加的值是空还是null。

（2）日期怎么传参？

    当我们查询的内容是字符串类型时，直接将text值作为参数传递就可以。当我们查询的内容为日期（时间）时，需要将DateTimePicker的值作为参数传递，由于两个控件只可能传递一个参数，所以我们可以设一个变量去接收参数。

（3）子窗体的控件可以被编辑吗？

    当子窗体继承父窗体的控件时，控件左上角会有个小锁的图标，此时的子窗体不能再修改自己的属性，原来问题出在父窗体控件的属性（Modifiers），此时只需将属性值改为“Protected”，即可在子窗体上重新编辑控件。

（4）D层可以用泛型吗？

     第一次使用模板方法的时候，D层我使用的是泛型，结果就是可以查到记录的行数，却无法显示，标头显示的是实体名。

     那么D层可以用泛型吗？答案是可以，由于三个子窗体同时继承一个父窗体，因此需要有三个实体集供一个方法转换，此时datatable转泛型需要重写三次。

小结

    问题越多，收获越多。