机房重构之模板方法模式

约定不等于承诺〃 2022-08-11 11:59 187阅读 0赞

在设计模式讲课之后，对模板方法模式有了更深的理解！这次机房重构的时候，用模板方法模式也是比较顺手的！

机房系统的三个地方都需要用到模板方法！

一：

![Center][]

二：

![Center 1][]

第三个地方就是大家都会想到的---组合查询，在这就不上图了！

组合查询时这三个中最难、最复杂的，那就以组合查询为例，给大家介绍一下我是如何使用模板方法的！

首先，手动建一个Windows窗体，作为父窗体；之后，添加对应的子窗体！

添加子窗体的方法：添加---选择“继承的窗体”---确定---弹出第二张图---选择对应的其父窗体即可。

![Center 2][]

![Center 3][]

下面给大家展示一下我的代码：

U层：父窗体时用来写所有子窗体的相同部分和被子窗体重写的方法（也就是不同的部分，不同的部分只写一个框架）！那就思考一下组合查询中相同的部分是什么？什么方法需要被子类重写？

相同的部分包括：相同的属性，相同的判断，还有一个公用的实体。被子类重写的方法包括：查询时非公共的部分，得到数据库表名（每个子窗体应用到数据库中的表示不同的），将组合查询中的字段名转化成英文。

Private Sub FrmGroupQuery_Load(sender As Object, e As EventArgs) Handles MyBase.Load

'写出公共部分
 cmbOperA.Items.Add("=")
 cmbOperA.Items.Add("<")
 cmbOperA.Items.Add(">")
 cmbOperA.Items.Add("<>")
 
 cmbOperB.Items.Add("=")
 cmbOperB.Items.Add("<")
 cmbOperB.Items.Add(">")
 cmbOperB.Items.Add("<>")
 
 cmbOperC.Items.Add("=")
 cmbOperC.Items.Add("<")
 cmbOperC.Items.Add(">")
 cmbOperC.Items.Add("<>")
 
 cmbRelationA.Items.Add("与")
 cmbRelationA.Items.Add("或")
 
 cmbRelationB.Items.Add("与")
 cmbRelationB.Items.Add("或")
 
 '窗体加载后，默认后两组选项不能用
 cmbFieldB.Enabled = False
 cmbOperB.Enabled = False
 txtContentB.Enabled = False
 cmbRelationB.Enabled = False
 
 cmbFieldC.Enabled = False
 cmbOperC.Enabled = False
 txtContentC.Enabled = False
 End Sub
 
 '第一个关系不为空
 Private Sub cmbRelationA_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbRelationA.SelectedIndexChanged
 cmbFieldB.Enabled = True
 cmbOperB.Enabled = True
 txtContentB.Enabled = True
 cmbRelationB.Enabled = True
 End Sub
 
 '第二个关系不为空
 Private Sub cmbRelationB_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbRelationB.SelectedIndexChanged
 cmbFieldC.Enabled = True
 cmbOperC.Enabled = True
 txtContentC.Enabled = True
 End Sub
 
 
 '单击查询执行的共同操作
 Private Sub btnQuery_Click(sender As Object, e As EventArgs) Handles btnQuery.Click
 
 '首先第一组选项不能为空
 If cmbRelationA.Text = "" Then
 If cmbFieldA.Text = "" Or cmbOperA.Text = "" Or txtContentA.Text = "" Then
 MsgBox("查询条件不完整，请补充！", vbOKOnly, "提示信息")
 Exit Sub
 End If
 End If
 
 '若第一个关系选择不是空，则第二组选项不能为空
 If cmbRelationA.Text <> "" Then
 If cmbFieldB.Text = "" Or cmbOperB.Text = "" Or txtContentB.Text = "" Then
 MsgBox("查询条件不完整，请补充！", vbOKOnly, "提示信息")
 Exit Sub
 End If
 End If
 
 '若关系第二个关系选项不是空，则第三组选项不能为空
 If cmbRelationB.Text <> "" Then
 If cmbFieldC.Text = "" Or cmbOperC.Text = "" Or txtContentC.Text = "" Then
 MsgBox("查询条件不完整，请补充！", vbOKOnly, "提示信息")
 Exit Sub
 End If
 End If
 
 '按一般的查询操作进行
 Dim engroup As New Entity.GroupQueryEntity '实例化实体
 
 engroup.cmbFieldA = GetEnglish(cmbFieldA.Text) '将参数传入实体
 engroup.cmbFieldB = GetEnglish(cmbFieldB.Text)
 engroup.cmbFieldC = GetEnglish(cmbFieldC.Text)
 engroup.cmbOperA = cmbOperA.Text
 engroup.cmbOperB = cmbOperB.Text
 engroup.cmbOperC = cmbOperC.Text
 engroup.cmbRelationA = GetEnglish(cmbRelationA.Text)
 engroup.cmbRelationB = GetEnglish(cmbRelationB.Text)
 engroup.txtContextA = txtContentA.Text
 engroup.txtContextB = txtContentB.Text
 engroup.txtContextC = txtContentC.Text
 
 '将得到的表名也传给实体
 engroup.GetTable = GetTable()
 
 '调用子类的方法，继续执行余下的查询的操作
 QueryClick(engroup)
 
 End Sub
 
 
 ''' <summary> 
 ''' 模板方法：定义虚函数GetEnglish，查询字段转化为数据库字段（英文） 
 ''' </summary> 
 ''' <param name="cmbField "></param> 
 ''' <returns></returns> 
 ''' <remarks></remarks> 
 
 Public Overridable Function GetEnglish(cmbField As String) As String
 Return ""
 End Function
 
 '''<summary>
 ''' 定义虚函数GetTable，获取不同数据库的表名
 ''' </summary>
 ''' <returns></returns>
 ''' <remarks></remarks>
 
 Public Overridable Function GetTable() As String
 Return ""
 End Function
 
 ''' <summary> 
 ''' 查询时非公共的部分 
 ''' </summary> 
 ''' <remarks></remarks> 
 
 Protected Overridable Sub QueryClick(ByVal engroup As Entity.GroupQueryEntity)
 
 End Sub

子窗体需要重写父窗体的方法和自己的一些基本信息。

Private Sub FrmOperatorWorklog_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Me.Text = "操作员工作记录"
            '填写基本信息
            cmbFieldA.Items.Add("教师")
            cmbFieldA.Items.Add("登陆日期")
            cmbFieldA.Items.Add("登陆时间")
            cmbFieldA.Items.Add("退出日期")
            cmbFieldA.Items.Add("退出时间")
            cmbFieldA.Items.Add("机器名")
    
            cmbFieldB.Items.Add("教师")
            cmbFieldB.Items.Add("登陆日期")
            cmbFieldB.Items.Add("登陆时间")
            cmbFieldB.Items.Add("退出日期")
            cmbFieldB.Items.Add("退出时间")
            cmbFieldB.Items.Add("机器名")
    
            cmbFieldC.Items.Add("教师")
            cmbFieldC.Items.Add("登陆日期")
            cmbFieldC.Items.Add("登陆时间")
            cmbFieldC.Items.Add("退出日期")
            cmbFieldC.Items.Add("退出时间")
            cmbFieldC.Items.Add("机器名")
        End Sub
    
        '重写字段名到数据库字段名的转换
        Public Overrides Function GetEnglish(cmbField As String) As String
            Select Case cmbField
                Case "教师"
                    GetEnglish = "userID"
                Case "登陆日期"
                    GetEnglish = "loginDate"
                Case "登陆时间"
                    GetEnglish = "loginTime"
                Case "退出日期"
                    GetEnglish = "logoutDate"
                Case "退出时间"
                    GetEnglish = "logoutTime"
                Case "机器号"
                    GetEnglish = "computer"
                Case "与"
                    GetEnglish = "and"
                Case "或"
                    GetEnglish = "or"
                Case Else
                    GetEnglish = ""
            End Select
        End Function
    
        '重写得到数据库表名
        Public Overrides Function GetTable() As String
            Return "Worklog_Info"
        End Function
    
        '继父类中的btnQuery_Click()事件
        Protected Overrides Sub QueryClick(ByVal engroup As Entity.GroupQueryEntity)
    
    
            '返回实体
            Dim myList As IList(Of Entity.WorklogEntity)
            Dim Boperator As New BLL.GroupQueryBLL
    
            myList = Boperator.OperatorWorklog(engroup)
    
            If myList.Count = 0 Then
                MsgBox("没有记录！", vbOKOnly, "提示信息")
            Else
                myDataGrid.DataSource = myList
    
                myDataGrid.Columns.Remove("logoutDate")
                myDataGrid.Columns.Remove("logoutTime")
                myDataGrid.Columns.Remove("status")
    
                myDataGrid.Columns(0).HeaderText = "系列号"
                myDataGrid.Columns(1).HeaderText = "用户名"
                myDataGrid.Columns(2).HeaderText = "用户级别"
                myDataGrid.Columns(3).HeaderText = "登陆日期"
                myDataGrid.Columns(4).HeaderText = "登陆时间"
                myDataGrid.Columns(5).HeaderText = "机器名"
    
            End If
    
        End Sub

B层比较简单，就是一个传递的过程。

Public Function OperatorWorklog(ByVal engroup As Entity.GroupQueryEntity) As IList(Of Entity.WorklogEntity)
 Dim myList As IList(Of Entity.WorklogEntity)
 Dim sqlFactory As New DataAccess.SqlFactory
 Dim iGroupQuery As IDAL.IGroupQuery
 
 iGroupQuery = sqlFactory.CreateGroupQuery
 
 myList = iGroupQuery.OperatorWorklog(engroup)
 
 Return myList
 End Function

D层只需调用一个存储过程，其他的都一样。

'下面是相同部分
 Dim dataTable As New DataTable
 Dim strText As String = "PROC_GroupQuery"
 Dim sqlParams As SqlParameter() = {New SqlParameter("@cmbFieldA", engroup.cmbFieldA),
 New SqlParameter("@cmbFieldB", engroup.cmbFieldB),
 New SqlParameter("@cmbFieldC", engroup.cmbFieldC),
 New SqlParameter("@cmbOperA", engroup.cmbOperA),
 New SqlParameter("@cmbOperB", engroup.cmbOperB),
 New SqlParameter("@cmbOperC", engroup.cmbOperC),
 New SqlParameter("@txtContextA", engroup.txtContextA),
 New SqlParameter("@txtContextB", engroup.txtContextB),
 New SqlParameter("@txtContextC", engroup.txtContextC),
 New SqlParameter("@cmbRelationA", engroup.cmbRelationA),
 New SqlParameter("@cmbRelationB", engroup.cmbRelationB),
 New SqlParameter("@GetTable", engroup.GetTable)}
 
 Dim helper As New SqlHelper
 
 dataTable = helper.ExecuteQuery(strText, CommandType.StoredProcedure, sqlParams)
 
 '下面是不同的部分
 Dim myList As IList(Of Entity.WorklogEntity)
 
 myList = EntitySetGeneric.ConvertToList(Of Entity.WorklogEntity)(dataTable)
 Return myList

公用的实体类，这个实体是以组合窗体上的几个控件为依据，负责传递控件中的数据：

Public Class GroupQueryEntity
 Private cmbField1 As String
 Private cmbField2 As String
 Private cmbField3 As String
 Private cmbOper1 As String
 Private cmbOper2 As String
 Private cmbOper3 As String
 Private txtContext1 As String
 Private txtContext2 As String
 Private txtContext3 As String
 Private cmbRelation1 As String
 Private cmbRelation2 As String
 
 Private EGetTable As String
 
 Public Property cmbFieldA() As String
 Get
 Return cmbField1
 End Get
 Set(value As String)
 cmbField1 = value
 End Set
 End Property
 
 Public Property cmbFieldB() As String
 Get
 Return cmbField2
 End Get
 Set(value As String)
 cmbField2 = value
 End Set
 End Property
 
 Public Property cmbFieldC() As String
 Get
 Return cmbField3
 End Get
 Set(value As String)
 cmbField3 = value
 End Set
 End Property
 
 Public Property cmbOperA() As String
 Get
 Return cmbOper1
 End Get
 Set(value As String)
 cmbOper1 = value
 End Set
 End Property
 
 Public Property cmbOperB() As String
 Get
 Return cmbOper2
 End Get
 Set(value As String)
 cmbOper2 = value
 End Set
 End Property
 
 Public Property cmbOperC() As String
 Get
 Return cmbOper3
 End Get
 Set(value As String)
 cmbOper3 = value
 End Set
 End Property
 
 Public Property txtContextA() As String
 Get
 Return txtContext1
 End Get
 Set(value As String)
 txtContext1 = value
 End Set
 End Property
 
 Public Property txtContextB() As String
 Get
 Return txtContext2
 End Get
 Set(value As String)
 txtContext2 = value
 End Set
 End Property
 
 Public Property txtContextC() As String
 Get
 Return txtContext3
 End Get
 Set(value As String)
 txtContext3 = value
 End Set
 End Property
 
 Public Property cmbRelationA() As String
 Get
 Return cmbRelation1
 End Get
 Set(value As String)
 cmbRelation1 = value
 End Set
 End Property
 
 Public Property cmbRelationB() As String
 Get
 Return cmbRelation2
 End Get
 Set(value As String)
 cmbRelation2 = value
 End Set
 End Property
 
 Public Property GetTable As String
 Get
 Return EGetTable
 End Get
 Set(value As String)
 EGetTable = value
 End Set
 End Property
 
 End Class

重点说一下D层调用的存储过程。一定要注意存储过程中SQL语句的空格。在select \* from 后面有一空格，where前后都要有空格。

ALTER PROCEDURE [dbo].[PROC_GroupQuery]
 	@cmbFieldA varchar(50),
 	@cmbFieldB varchar(50),
 	@cmbFieldC varchar(50),
 	@cmbOperA varchar(50),
 	@cmbOperB varchar(50),
 	@cmbOperC varchar(50),
 	@txtContextA varchar(50),
 	@txtContextB varchar(50),
 	@txtContextC varchar(50),
 	@cmbRelationA varchar(50),
 	@cmbRelationB varchar(50),
 	@GetTable varchar(50)
 AS
 	--定义一个临时变量
 	declare @temp as varchar(500)
 BEGIN
 	set @temp ='select * from '+@gettable+' where '+@cmbFieldA+@cmbOperA+CHAR(39)+@txtContextA+CHAR(39)
 	if @cmbRelationA !=''
 	BEGIN
 		set @temp =@temp+CHAR(32)+@cmbRelationA +CHAR(32)+@cmbFieldB+@cmbOperB+CHAR(39)+@txtContextB+CHAR(39)
 	if @cmbRelationB !=''
 		set @temp =@temp+CHAR(32)+@cmbRelationB+CHAR(32)+@cmbFieldC+@cmbOperC+CHAR(39)+@txtContextC+CHAR(39)
 	END
 
 	--返回查询到的表
 	exec (@temp)
 
 END

提醒：存储过程要有返回值！上述代码返回的是查询到的表。另外，select  变量名 （例：select @temp）返回的是查询语句。

简单提一下前两种使用模板的窗体。

第一种：将查询卡号是否存在和导出Excel表放在父窗体中。

第二种：将导出Excel表放在父窗体中，建立一个公用的实体，存放起始和终止日期。

只要会做组合查询了，另外两种就是小case。

最后，说一句使用模板方法有遗憾事，本想着组合查询这么多窗体只需要B层和D层中的一个类，做得时候才发现不是那么一回事，有几个子窗体就需要几个B层和D层中的类。不知道有没有人能解决这个问题，这样用让我感觉还是有点麻烦！（注：返回的是泛型集合才行！）

[Center]: /images/20220810/0ba7527195bb4368b0961fd14daa4d4c.png
[Center 1]: /images/20220810/103d572c4a6f4d049763531c7865f521.png
[Center 2]: /images/20220810/952a8771d51a47b58cda81126b96a5da.png
[Center 3]: /images/20220810/e0df1b60aee2423795a247a7850998aa.png