机房重构之组合查询

    机房重构到组合查询了，发现要用到组合查询的窗体有三个，如果还是每个窗体都写一遍会发现重复的代码有很多，有重复的代码的时候我们会想到抽象出一个发现。那这个窗体的功能基本相同我们也应该抽象出一个父窗体，这时候就用到模板方法，下面就说说模板方法的使用。

   模板方法模式 ：定义了一个操作中的算法的骨架，把一些步骤延迟到子类当中。它使得子类可以不改变一个算法的结构即可重定义该算法的某些特定步骤。它提供了一个代码复用的平台，消除代码冗余带来的弊端，使系统更易于维护。

1、建立模板父窗体
  添加Windows窗体，设计模板界面，并在模板窗体里写入抽象出来的类和方法的代码。

  父窗体写完整的七层，子窗体在继承的时候只要在U层写出自己的实体和把抽象方法补充完整就行了。

  U层

     <span style="font-family:KaiTi_GB2312;font-size:18px;">   <span style="font-family:KaiTi_GB2312;">Dim enMulQuery As New Entity.ModelInfo

        enMulQuery.cmdField1 = GetEnglish(cmdField1.Text.Trim())
        enMulQuery.cmdField2 = GetEnglish(cmdFielld2.Text.Trim())
        enMulQuery.cmdField3 = GetEnglish(cmdFielld3.Text.Trim())
        enMulQuery.cmdOPeration1 = cmdOperation1.Text.Trim()
        enMulQuery.cmdOperation2 = cmdOperation2.Text.Trim()
        enMulQuery.cmdOperation3 = cmdOperation3.Text.Trim()
        enMulQuery.cmdrelation1 = GetEnglish(cmdRelation1.Text.Trim())
        enMulQuery.cmdRelation2 = GetEnglish(cmdRelation2.Text.Trim())
        enMulQuery.txtContent1 = TxtContent1.Text.Trim()
        enMulQuery.txtContent2 = TxtContent2.Text.Trim()
        enMulQuery.txtContent3 = TxtContent3.Text.Trim()
        enMulQuery.tablename = GetTable() '传实体表


        '实例化外观层
        Dim facade As New Facade.MulQueryFacade
        '定义临时表
        Dim table As New DataTable()
        '获取外观层 
        table = facade.MulQuery(enMulQuery)
        '将数据显示在控件中
        DataGridView1.DataSource = table

    End Sub


    Private Sub FrmModelQuery_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        '操作符
        cmdOperation1.Items.Add("=")
        cmdOperation1.Items.Add("<")
        cmdOperation1.Items.Add(">")
        cmdOperation1.Items.Add("<>")

        cmdOperation2.Items.Add("=")
        cmdOperation2.Items.Add("<")
        cmdOperation2.Items.Add(">")
        cmdOperation2.Items.Add("<>")

        cmdOperation3.Items.Add("=")
        cmdOperation3.Items.Add("<")
        cmdOperation3.Items.Add(">")
        cmdOperation3.Items.Add("<>")

        '组合关系
        cmdRelation1.Items.Add("与")
        cmdRelation1.Items.Add("或")
        cmdRelation2.Items.Add("与")
        cmdRelation2.Items.Add("或")
        '窗体加载后，后两组查询默认不能用
        cmdFielld2.Enabled = False
        cmdOperation2.Enabled = False
        TxtContent2.Enabled = False

        cmdRelation2.Enabled = False

        cmdFielld3.Enabled = False
        cmdOperation3.Enabled = False
        TxtContent3.Enabled = False
    End Sub
    ''' <summary>
    ''' 模板方法，定义虚函数GetEnglish，查询字段转化为数据库字段
    ''' </summary>
    ''' <param name="cmdString"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Overridable Function GetEnglish(cmdString As String) As String
        Return ""
    End Function

    ''' <summary>
    ''' 获得数据库表名
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Overridable Function GetTable() As String
        Return ""
    End Function

    ''' <summary>
    ''' 第一个组合关系是否为空
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub cmdRelation1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmdRelation1.SelectedIndexChanged
        If cmdRelation1.Text = "" Then
            cmdFielld2.Enabled = False
            cmdFielld3.Enabled = False
            cmdOperation2.Text = False
            cmdOperation3.Enabled = False

            cmdRelation2.Enabled = False
            TxtContent2.Enabled = False
            TxtContent3.Enabled = False


        Else
            cmdFielld2.Enabled = True
            cmdOperation2.Enabled = True
            cmdRelation2.Enabled = True
            TxtContent2.Enabled = True

        End If
    End Sub

    ''' <summary>
    ''' 第二个组合关系是否为空
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub cmdRelation2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmdRelation2.SelectedIndexChanged
        If cmdRelation2.Text = "" Then
            cmdFielld3.Enabled = False
            cmdOperation2.Enabled = False
            TxtContent3.Enabled = False
        Else
            cmdFielld3.Enabled = True
            cmdOperation3.Enabled = True
            TxtContent3.Enabled = True
        End If
    End Sub

    ''' <summary>
    ''' 关闭窗体
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub btnCancel_Click(sender As Object, e As EventArgs) Handles btnCancel.Click
        Me.Close()
    End Sub

    Private Sub btnImport_Click(sender As Object, e As EventArgs) Handles btnImport.Click
        '要先添加引用才能用到Microsoft.office.interoper.Excel.Application()
        Dim MyExcel As New Microsoft.Office.Interop.Excel.Application()
        MyExcel.Application.Workbooks.Add(True)
        MyExcel.Visible = True

        '去除Datagridview的编号列（也可以不用）
        Dim m As Integer
        For m = 0 To DataGridView1.ColumnCount - 1
            MyExcel.Cells(1, m + 1) = Me.DataGridView1.Columns(m).HeaderText
        Next

        '往excel表中添加数据
        Dim i As Integer
        For i = 0 To Me.DataGridView1.ColumnCount - 1
            Dim j As Integer
            For j = 0 To DataGridView1.ColumnCount - 1
                If Me.DataGridView1(j, i).Value Is System.DBNull.Value Then
                    MyExcel.Cells(i + 2, j + 1) = ""
                Else
                    MyExcel.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString
                End If
            Next
        Next
    End Sub
End Class</span></span>
 D层：查询的过程用到的字符串的拼接：

<span style="font-family:KaiTi_GB2312;font-size:18px;"><span style="font-family:KaiTi_GB2312;">Public Function SelectQuery(enMulQuery As Entity.ModelInfo) As DataTable Implements JFIDAL.MulQueryIDAL.SelectQuery

        Dim SqlHelperObject As New SQLHelper.Sqlhelper
        Dim dt As DataTable
        Dim Sql As String

        If Trim(enMulQuery.cmdrelation1) = "" Then  '有一组查询条件  
            Sql = "select * from " & enMulQuery.tablename & " where " & enMulQuery.cmdField1 & enMulQuery.cmdOPeration1 & "'" & enMulQuery.txtContent1 & "'"
        Else
            If Trim(enMulQuery.cmdRelation2) = "" Then '有两组查询条件  
                Sql = "select * from " & enMulQuery.tablename & " where " & enMulQuery.cmdField1 & enMulQuery.cmdOPeration1 & "'" & enMulQuery.txtContent1 & "'" & enMulQuery.cmdrelation1 & " " & enMulQuery.cmdField2 & enMulQuery.cmdOperation2 & "'" & enMulQuery.txtContent2 & "'"
            Else
                Sql = "select * from " & enMulQuery.tablename & " where " & enMulQuery.cmdField1 & enMulQuery.cmdOPeration1 & "'" & enMulQuery.txtContent1 & "'" & enMulQuery.cmdrelation1 & " " & enMulQuery.cmdField2 & enMulQuery.cmdOperation2 & "'" & enMulQuery.txtContent2 & "'" & enMulQuery.cmdRelation2 & " " & enMulQuery.cmdField3 & enMulQuery.cmdOperation3 & "'" & enMulQuery.txtContent3 & "'"
            End If
        End If

        '模板的无参查询  
        dt = SQLHelper.Sqlhelper.GetDataTable(Sql, CommandType.Text)

        Return dt
        
    End Function</span></span>
二、子窗体的创建

  如下图建立子窗体，选择继承创建的父窗体模板，然后就可以得到一模一样的子窗体了。通过在子窗体里重写一些方法和类，以实现不同的功能就可以了。

U层以操作员工作记录为例

 <span style="font-family:KaiTi_GB2312;font-size:18px;"><span style="font-family:KaiTi_GB2312;font-size:18px;">Private Sub FrmWorkLog_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        cmdField1.Items.Add("用户名")
        cmdField1.Items.Add("上机日期")
        cmdField1.Items.Add("上机时间")
        cmdField1.Items.Add("机器名")


        cmdFielld2.Items.Add("用户名")
        cmdFielld2.Items.Add("上机日期")
        cmdFielld2.Items.Add("上机时间")
        cmdFielld2.Items.Add("机器名")

        cmdFielld3.Items.Add("用户名")
        cmdFielld3.Items.Add("上机日期")
        cmdFielld3.Items.Add("上机时间")
        cmdFielld3.Items.Add("机器名")


    End Sub
    Public Overrides Function GetTable() As String
        Return "T_WorkLog"
    End Function
    Protected Overrides Function GetEnglish(cmdString As String) As String
        Select Case cmdString
            Case "用户名"
                GetEnglish = "UserID"
            Case "上机日期"
                GetEnglish = "OnDate"
            Case "上机时间"
                GetEnglish = "OnTime"
            Case "机器名"
                GetEnglish = "Computer"
            Case "与"
                GetEnglish = "and"
            Case "或"
                GetEnglish = "or"
            Case Else
                GetEnglish = ""
        End Select
    End Function</span></span>