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

前言

    我们在学习第一次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 SubEnd 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 tableEnd 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 IfEnd Function

D层

Imports System.Data.SqlClientPublic 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 tableEnd 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 ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================  -- 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 SubEnd Class

3.遇到的问题

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

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

（2）日期怎么传参？

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

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

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

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

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

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

小结

    问题越多，收获越多。