機房重構之模版方法實作組合查詢

機房重構漸漸走向了尾聲，師父的驗收中卻出現了很多的問題。下面，讓我把這些問題一點點總結出來，細細說說。

上一次機房，我們是怎麼實作的呢？是一個個挨着敲的，下面，看一下模版方法怎麼實作組合查詢的呢？在機房中，主要是鍛煉我們的設計模式。由于機房收費系統中有很多一樣的窗體，我們就把同一類的窗體抽象出來作為一個類，其他的窗體去繼承抽象窗體就可以，這就用到了模版方法，大大減少了我們的代碼量，提高了我們的效率。

那麼我們是怎麼實作的呢？

首先，建立一個組合查詢的父窗體。

機房重構之模版方法實作組合查詢

然後，我們讓子窗體繼承父窗體的方法，或者重寫與父類不同的方法。

首先看一下實體層代碼：

<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系統出錯處理設計