机房重构到组合查询了，发现要用到组合查询的窗体有三个，如果还是每个窗体都写一遍会发现重复的代码有很多，有重复的代码的时候我们会想到抽象出一个发现。那这个窗体的功能基本相同我们也应该抽象出一个父窗体，这时候就用到模板方法，下面就说说模板方法的使用。
模板方法模式 ：定义了一个操作中的算法的骨架，把一些步骤延迟到子类当中。它使得子类可以不改变一个算法的结构即可重定义该算法的某些特定步骤。它提供了一个代码复用的平台，消除代码冗余带来的弊端，使系统更易于维护。
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>
