机房重构之模版方法实现组合查询

机房重构渐渐走向了尾声，师父的验收中却出现了很多的问题。下面，让我把这些问题一点点总结出来，细细说说。

上一次机房，我们是怎么实现的呢？是一个个挨着敲的，下面，看一下模版方法怎么实现组合查询的呢？在机房中，主要是锻炼我们的设计模式。由于机房收费系统中有很多一样的窗体，我们就把同一类的窗体抽象出来作为一个类，其他的窗体去继承抽象窗体就可以，这就用到了模版方法，大大减少了我们的代码量，提高了我们的效率。

那么我们是怎么实现的呢？

首先，建立一个组合查询的父窗体。

机房重构之模版方法实现组合查询

然后，我们让子窗体继承父窗体的方法，或者重写与父类不同的方法。

首先看一下实体层代码：

<span style="font-family:KaiTi_GB2312;font-size:18px;"><strong>    Private strcmbField1_text As String   
    Private strcmbField2_text As String
    Private strcmbField3_text As String
    Private strcmbOperation1_text As String
    Private strcmbOperation2_text As String
    Private strcmbOperation3_text As String
    Private strtxtContent1_text As String
    Private strtxtContent2_text As String
    Private strtxtContent3_text As String
    Private strcmbRelation1_text As String
    Private strcmbRelation2_text As String
    Private strGetTable As String
    '字段1
    Public Property cmbField1_text() As String    
        Get
            Return Me.strcmbField1_text
        End Get
        Set(value As String)
            Me.strcmbField1_text = value
        End Set
    End Property
    '字段2
    Public Property cmbField2_text() As String
        Get
            Return strcmbField2_text

        End Get
        Set(value As String)
            strcmbField2_text = value
        End Set
    End Property
    '字段3
    Public Property cmbField3_text() As String
        Get
            Return strcmbField3_text

        End Get
        Set(value As String)
            strcmbField3_text = value
        End Set
    End Property
    '操作符1
    Public Property cmbOperation1_text() As String
        Get
            Return strcmbOperation1_text

        End Get
        Set(value As String)
            strcmbOperation1_text = value
        End Set
    End Property
    '操作符2
    Public Property cmbOperation2_text() As String
        Get
            Return strcmbOperation2_text

        End Get
        Set(value As String)
            strcmbOperation2_text = value
        End Set
    End Property
    '操作符3
    Public Property cmbOperation3_text() As String
        Get
            Return strcmbOperation3_text

        End Get
        Set(value As String)
            strcmbOperation3_text = value
        End Set
    End Property
    '要查询内容的文本
    Public Property txtContent1_text() As String
        Get
            Return strtxtContent1_text

        End Get
        Set(value As String)
            strtxtContent1_text = value
        End Set
    End Property
    Public Property txtContent2_text() As String
        Get
            Return strtxtContent2_text

        End Get
        Set(value As String)
            strtxtContent2_text = value
        End Set
    End Property
    Public Property txtContent3_text() As String
        Get
            Return strtxtContent3_text

        End Get
        Set(value As String)
            strtxtContent3_text = value
        End Set
    End Property
    '组合关系
    Public Property cmbRelation1_text() As String
        Get
            Return strcmbRelation1_text

        End Get
        Set(value As String)
            strcmbRelation1_text = value
        End Set
    End Property
    Public Property cmbRelation2_text() As String
        Get
            Return strcmbRelation2_text

        End Get
        Set(value As String)
            strcmbRelation2_text = value
        End Set
    End Property
    Public Property GetTable() As String
        Get
            Return strGetTable

        End Get
        Set(value As String)
            strGetTable = value
        End Set
    End Property
</strong></span>

U层代码：

<span style="font-family:KaiTi_GB2312;font-size:18px;"><strong>    '可以被重写的方法
    '显示查询的内容到DataGridView控件中
    Protected Overridable Sub ToShow()
    End Sub
    ' 定义可被重写的虚函数GetDBName,获取不同数据库的字段名    
    Protected Overridable Function GetDBName(ByVal control As String) As String
        Return ""
    End Function
    ' 定义可被重写的虚函数GetDBName,获取不同数据库的表名    
    Protected Overridable Function GetTable() As String
        Return ""
    End Function
</strong></span>

查询按钮下的代码：

<span style="font-family:KaiTi_GB2312;font-size:18px;"><strong>        '给实体层传参  
        Dim enZuheCheck As New Entity.ZuheCheckEntity
        Dim mylist As New List(Of Entity.AllEntity)
        '给B层ZuheCheckBLL方法传递参数()
        Dim db As New BLL.ZuheCheckBLL
        mylist = db.SelectZuheCheck(enZuheCheck)
        Call ToShow()
</strong></span>

其余层代码都一样，看一下D层代码。

<span style="font-family:KaiTi_GB2312;font-size:18px;"><strong>Public Class ZuheCheckDAL : Implements IZuheCheck
    Public Function SelectZuheCheck(ByVal enZuhe As Entity.ZuheCheckEntity) As List(Of Entity.AllEntity) Implements IZuheCheck.SelectZuheCheck
        Dim conn As SqlConnection = New SqlConnection("Server=localhost;Database=Refactor;User ID=sa; Password=199312")
        Dim strText As String = "PROC_ZuheCheck" '调用存储过程  
        Dim cmdType As CommandType = CommandType.StoredProcedure   '命令类型  
        Dim Parameter As SqlParameter()
        '传参    
        Parameter = {New SqlParameter("@cmbField1", enZuhe.cmbField1_text),
                     New SqlParameter("@cmbField2", enZuhe.cmbField2_text),
                     New SqlParameter("@cmbField3", enZuhe.cmbField3_text),
                     New SqlParameter("@cmbOperation1", enZuhe.cmbOperation1_text),
                     New SqlParameter("@cmbOperation2", enZuhe.cmbOperation2_text),
                     New SqlParameter("@cmbOperation3", enZuhe.cmbOperation3_text),
                     New SqlParameter("@txtContent1", enZuhe.txtContent1_text),
                     New SqlParameter("@txtContent2", enZuhe.txtContent2_text),
                     New SqlParameter("@txtContent3", enZuhe.txtContent3_text),
                     New SqlParameter("@cmbRelation1", enZuhe.cmbRelation1_text),
                     New SqlParameter("@cmbRelation2", enZuhe.cmbRelation2_text),
                     New SqlParameter("@tableName", enZuhe.GetTable)} '设置参数 

        Dim SqlHelper As New Sqlhelper.SqlHelper()  '实例化SqlHelper这个类的一个对象  
        Dim dt As New DataTable
        Dim myList As New List(Of Entity.AllEntity)

        dt = SqlHelper.ExecuteReaderTable(strText, cmdType, Parameter) '调用sqlhelper中executereadertable的方法  
        myList = ConvertHelper.convertToList(Of Entity.AllEntity)(dt)
        Return myList
    End Function</strong></span>

看一下存储过程：

<span style="font-family:KaiTi_GB2312;font-size:18px;"><strong>USE [Refactor]
GO
/****** Object:  StoredProcedure [dbo].[PROC_ZuheCheck]    Script Date: 07/31/2015 20:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<王孟梅>
-- Create date: <2015.6.19>
-- Description:	<组合查询>
-- =============================================
ALTER PROCEDURE [dbo].[PROC_ZuheCheck] 
	-- Add the parameters for the stored procedure here
    @cmbField1 varchar(20),  
    @cmbOperation1 varchar(10),  
    @txtContent1 varchar(10),  
    @cmbField2 varchar(20),  
    @cmbOperation2 varchar(10),  
    @txtContent2 varchar(10),  
    @cmbField3 varchar(20),  
    @cmbOperation3 varchar(10),  
    @txtContent3 varchar(10),  
    @cmbRelation1 varchar(10),  
    @cmbRelation2 varchar(10),  
    @tableName varchar(20)  
AS
declare @TempSql varchar(500)--临时存放sql语句
BEGIN  
    SET @TempSql='SELECT * FROM '[email protected] +' WHERE ' [email protected] [email protected]+char(39) + @txtContent1 + char(39)    
    if @cmbRelation1 != ''  
    BEGIN    
        SET @[email protected][email protected]+CHAR(32)[email protected] [email protected]+CHAR(39)[email protected]+CHAR(39)    
        if @cmbRelation2!= ''  
        BEGIN    
        SET @[email protected][email protected]+CHAR(32)[email protected][email protected]+CHAR(39)[email protected]+CHAR(39)    
        END    
    END    
EXECUTE(@TempSql)   
END
</strong></span>

其余层的代码并无什么区别。

下面大家看一下一个子窗体的代码：

<span style="font-family:KaiTi_GB2312;font-size:18px;"><strong>    '重写方法，获得学生上机信息的字段名
    Protected Overrides Function GetDBName(ByVal control As String) As String
        Select Case (control)
            Case "卡号"
                Return "cardno"
            Case "学号"
                Return "studentno"
            Case "教师"
                Return "UserName"
            Case "上机日期"
                Return "ondate"
            Case "上机时间"
                Return "ontime"
            Case "下机时间"
                Return "offtime"
            Case "花费时间"
                Return "costtime"
            Case "花费金额"
                Return "cost"
            Case "是否结账"
                Return "IsCheck"
            Case "与"
                Return "and"
            Case "或"
                Return "or"
            Case Else
                Return ""
        End Select
    End Function

    '重写获得表名方法  
    Protected Overrides Function GetTable() As String
        enZuheCheck.GetTable = "Line"
        Return enZuheCheck.GetTable
    End Function
    Private Sub frmStuOnChe_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("下机时间")
        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("下机时间")
        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("下机时间")
        cmbField3.Items.Add("花费时间")
        cmbField3.Items.Add("花费金额")
        cmbField3.Items.Add("是否结账")

    End Sub

    Protected Overrides Sub ToShow()
        Dim enZuheCheck As New Entity.ZuheCheckEntity
        Dim mylist As New List(Of Entity.AllEntity)
        Dim db As New BLL.ZuheCheckBLL

        enZuheCheck.cmbField1_text = GetDBName(cmbField1.Text.Trim())
        enZuheCheck.cmbField2_text = GetDBName(cmbField2.Text.Trim())
        enZuheCheck.cmbField3_text = GetDBName(cmbField3.Text.Trim())
        enZuheCheck.cmbOperation1_text = cmbOperation1.Text.Trim()
        enZuheCheck.cmbOperation2_text = cmbOperation2.Text.Trim()
        enZuheCheck.cmbOperation3_text = cmbOperation3.Text.Trim()
        enZuheCheck.txtContent1_text = txtContent1.Text.Trim()
        enZuheCheck.txtContent2_text = txtContent2.Text.Trim()
        enZuheCheck.txtContent3_text = txtContent3.Text.Trim()
        enZuheCheck.cmbRelation1_text = GetDBName(cmbRelation1.Text.Trim())
        enZuheCheck.cmbRelation2_text = GetDBName(cmbRelation2.Text.Trim())
        enZuheCheck.GetTable = GetTable()
        mylist = db.SelectZuheCheck(enZuheCheck)
        If mylist.Count = 0 Then
            MsgBox("没有记录，请重新设置查询条件", vbOKOnly, vbExclamation)
            DataGridView1.DataSource = Nothing
            DataGridView1.Refresh()
        Else
            DataGridView1.DataSource = mylist
            ''将datagridview改为中文
            DataGridView1.Columns(0).HeaderText = "学号"
            DataGridView1.Columns(3).HeaderText = "卡号"
            DataGridView1.Columns(7).HeaderText = "上机日期"
            DataGridView1.Columns(8).HeaderText = "上机时间"
            DataGridView1.Columns(9).HeaderText = "下机时间"
            DataGridView1.Columns(10).HeaderText = "消费金额"
            DataGridView1.Columns(11).HeaderText = "是否结账"
            DataGridView1.Columns(12).HeaderText = "操作人"

            DataGridView1.Columns(1).Visible = False
            DataGridView1.Columns(2).Visible = False
            DataGridView1.Columns(4).Visible = False
            DataGridView1.Columns(5).Visible = False
            DataGridView1.Columns(6).Visible = False
      End If

    End Sub
</strong></span>

大家可以看到子窗体的代码，就比较不合理了，每一个特殊的都是自己添加的，这就不利于代码的维护了，师父在这里建议我，可以把那些添加相同项的东西封装，写成一个类或者方法，这样有利于维护。

机房重构的过程，就是我们走向面向对象的第一步。以后还会有很多机房重构中的问题要总结。

机房重构之模版方法实现组合查询

继续阅读

机房收费系统（一）登录

机房收费系统——SSTab

【机房收费系统】——下机

机房收费系统之报表浅谈

【机房收费系统】——小知识点总结

机房收费系统-总结篇

.Net中的早期绑定和后期绑定（二）早期绑定后期绑定早期绑定与后期绑定的区别总结

SqlHelper类的使用 SqlHelper类总结

【2015-2016，我在路上】

机房收费系统之查询系

【机房收费个人重构—思想篇】

三层架构在登录窗体中的实现（一）

三层架构在登录窗体中再实现（二）

用户'sa'登录失败（DataGridView）

机房收费系统之优化及问题汇总（来淘宝啦~）（一）

《机房收费系统》概要设计说明书1引言2总体设计3接口设计4运行设计5系统数据结构设计6系统出错处理设计