机房收费系统中，一个比较让人纠结功能就是组合查询，不仅仅是代码比较多，而且大多都是重复的代码，也正是因为如此，才比较适合模版方法模式。
一、基本介绍
模版方式模式是定义一个操作中的算法的骨架，而将步骤延迟到子类中。
模板方法使得子类可以不改变一个算法的结构即可重定义算法的某些特定步骤。
类图
二、具体实现
1、建立模板父窗体
添加Windows窗体，设计模板界面（如下图），并在模板窗体里写入抽象出来的类和方法的代码。
'************************************************* '作者：崔晓光 '小组： '说明：组合查询模板 '创建日期：2014.9.9 '版本号： '**********************************************/ Imports Entity Imports Microsoft.Office.Interop.Excel Imports Microsoft.Office.Interop Imports System.Data Imports System.IO '组合查询父窗体 Public Class FrmComQueryParent '实例化一个组合查询的实体 Protected comQueryEntity As New ComQueryEntity ’加载 Protected Sub FrmComQueryParent_Load(sender As Object,e As EventArgs) Handles MyBase.Load '将参数传递给实体，赋初值 '字段名 comQueryEntity.CmbName1 = "" comQueryEntity.CmbName2 = "" comQueryEntity.CmbName3 = "" '操作符 cmbMark1.Items.Add(">") cmbMark1.Items.Add("<") cmbMark1.Items.Add("=") cmbMark1.Items.Add("<>") cmbMark2.Items.Add(">") cmbMark2.Items.Add("<") cmbMark2.Items.Add("=") cmbMark2.Items.Add("<>") cmbMark3.Items.Add(">") cmbMark3.Items.Add("<") cmbMark3.Items.Add("=") cmbMark3.Items.Add("<>") '关系 cmbRelation1.Items.Add("与") cmbRelation1.Items.Add("或") cmbRelation2.Items.Add("与") cmbRelation2.Items.Add("或") '窗体加载后，后两组查询默认不能用 cmbName2.Enabled = False cmbName3.Enabled = False cmbMark2.Enabled = False cmbMark3.Enabled = False cmbRelation2.Enabled = False txtContent2.Enabled = False txtContent3.Enabled = False Dim i As Integer For i = 0 To dgvRecord.Columns.Count - 1 dgvRecord.Columns(i).Width = DataGridViewAutoSizeColumnMode.AllCells '调整列宽为根据内容自动调整 Next End Sub '查询 Private Sub btQuery_Click(sender As Object,e As EventArgs) Handles btQuery.Click Try '判断组合框不为空 If cmbRelation1.Text = "" Then If cmbName1.Text = "" Or cmbMark1.Text = "" Or txtContent1.Text = "" Then MsgBox("第一行查询条件不能为空，请完善查询信息！",CType(vbOKOnly + MsgBoxStyle.Exclamation,MsgBoxStyle),"提示") Exit Sub End If End If If cmbRelation1.Text <> "" Then If cmbName1.Text = "" Or cmbMark1.Text = "" Or txtContent1.Text = "" Or cmbName2.Text = "" Or cmbMark2.Text = "" Or txtContent2.Text = "" Then MsgBox("所输入的查询条件不能为空，请完善查询信息！","提示") Exit Sub End If End If If cmbRelation2.Text <> "" Then If cmbName1.Text = "" Or cmbMark1.Text = "" Or txtContent1.Text = "" Or cmbName2.Text = "" Or cmbMark2.Text = "" Or txtContent2.Text = "" Or cmbName3.Text = "" Or cmbMark3.Text = "" Or txtContent3.Text = "" Then MsgBox("所输入的查询条件不能为空，请完善查询信息！","提示") Exit Sub End If End If '将参数传给实体 comQueryEntity.DbName = GetdbName() comQueryEntity.CmbName1 = ToEnglish(cmbName1.Text) comQueryEntity.CmbName2 = ToEnglish(cmbName2.Text) comQueryEntity.CmbName3 = ToEnglish(cmbName3.Text) comQueryEntity.CmbMark1 = cmbMark1.Text.Trim comQueryEntity.CmbMark2 = cmbMark2.Text.Trim comQueryEntity.CmbMark3 = cmbMark3.Text.Trim '在查询时非数字要加上'' If IsNumeric(txtContent1.Text) Then comQueryEntity.TxtContent1 = txtContent1.Text.Trim Else comQueryEntity.TxtContent1 = "'" & txtContent1.Text.Trim & "'" End If If IsNumeric(txtContent2.Text) Then comQueryEntity.TxtContent2 = txtContent2.Text.Trim Else comQueryEntity.TxtContent2 = "'" & txtContent2.Text.Trim & "'" End If If IsNumeric(txtContent3.Text) Then comQueryEntity.TxtContent3 = txtContent3.Text.Trim Else comQueryEntity.TxtContent3 = "'" & txtContent3.Text.Trim & "'" End If '前者还是后者 comQueryEntity.CmbRelation1 = ToEnglish(cmbRelation1.Text) comQueryEntity.CmbRelation2 = ToEnglish(cmbRelation2.Text) Dim dt As New Data.DataTable Dim facadeGeneral As New Facade.Facade.FacadeGeneral ' 把表显示到datagridview中 Call Todgv(comQueryEntity) Catch ex As Exception MsgBox(ex.Message) End Try End Sub ''' <summary> ''' 模板方法，定义虚函数ToEnglish，查询字段转化为数据库字段 ''' </summary> ''' <param name="cmbName"></param> ''' <returns></returns> ''' <remarks></remarks> Public Overridable Function ToEnglish(cmbName As String) As String Return "" End Function ''' <summary> ''' 获得数据库表名 ''' </summary> ''' <returns></returns> ''' <remarks></remarks> Protected Overridable Function GetdbName() As String Return "" End Function ''' <summary> ''' 把表显示到datagridview中 ''' </summary> ''' <remarks></remarks> Protected Overridable Sub Todgv(ByVal comQueryEntity As ComQueryEntity) End Sub ''' <summary> ''' 拼接字符串 ''' </summary> ''' <param name="frm"></param> ''' <returns></returns> ''' <remarks></remarks> Public Function Query(frm As FrmComQueryParent,ByVal comQueryEntity As ComQueryEntity) As String Dim cmdText As String = "" & frm.ToEnglish(frm.cmbName1.Text) & frm.cmbMark1.Text & "" & comQueryEntity.TxtContent1 & "" '非组合查询 If frm.cmbRelation1.Text = "" Then cmdText = cmdText '关系2为空，关系1不为空 ElseIf frm.cmbRelation2.Text = "" Then cmdText = cmdText & frm.ToEnglish(frm.cmbRelation1.Text) & "" & frm.ToEnglish(frm.cmbName2.Text) & frm.cmbMark2.Text & "" & comQueryEntity.TxtContent2 & "" Else '关系1，2都不为空 cmdText = cmdText & frm.ToEnglish(frm.cmbRelation1.Text) & "" & _ frm.ToEnglish(frm.cmbName2.Text) & frm.cmbMark2.Text & "'" & comQueryEntity.TxtContent2 & "'" & "" & _ frm.ToEnglish(frm.cmbRelation2.Text) & "" & _ frm.ToEnglish(frm.cmbName3.Text) & frm.cmbMark3.Text & "'" & comQueryEntity.TxtContent3 & "'" End If Return cmdText End Function ''' <summary> ''' 第一个组合关系是否为空 ''' </summary> ''' <param name="sender"></param> ''' <param name="e"></param> ''' <remarks></remarks> Private Sub cmbRelation1_SelectedIndexChanged(sender As Object,e As EventArgs) Handles cmbRelation1.SelectedIndexChanged If cmbRelation1.Text = "" Then cmbName2.Enabled = False cmbName3.Enabled = False cmbMark2.Enabled = False cmbMark3.Enabled = False cmbRelation2.Enabled = False txtContent2.Enabled = False txtContent3.Enabled = False Else cmbName2.Enabled = True cmbMark2.Enabled = True cmbRelation2.Enabled = True txtContent2.Enabled = True End If End Sub ''' <summary> ''' 第二个组合关系是否为空 ''' </summary> ''' <param name="sender"></param> ''' <param name="e"></param> ''' <remarks></remarks> Private Sub cmbRelation2_SelectedIndexChanged(sender As Object,e As EventArgs) Handles cmbRelation2.SelectedIndexChanged If cmbRelation2.Text = "" Then cmbName3.Enabled = False cmbMark3.Enabled = False txtContent3.Enabled = False Else cmbName3.Enabled = True cmbMark3.Enabled = True txtContent3.Enabled = True End If End Sub ''' <summary> ''' 关闭该窗体 ''' </summary> ''' <param name="sender"></param> ''' <param name="e"></param> ''' <remarks></remarks> Private Sub btCancel_Click(sender As Object,e As EventArgs) Handles btCancel.Click Me.Close() End Sub '导出为Excel Private Sub Button1_Click(sender As Object,e As EventArgs) Handles Button1.Click '要先添加引用才能用到 Microsoft.Office.Interop.Excel.Application() Dim MyExcel As New Microsoft.Office.Interop.Excel.Application() MyExcel.Application.Workbooks.Add(True) MyExcel.Visible = True '去除dgvRecord的编号列（这里也可以不要） Dim m As Integer For m = 0 To dgvRecord.ColumnCount - 1 MyExcel.Cells(1,m + 1) = Me.dgvRecord.Columns(m).HeaderText Next m '往excel表里添加数据 Dim i As Integer For i = 0 To Me.dgvRecord.RowCount - 1 Dim j As Integer For j = 0 To dgvRecord.ColumnCount - 1 If Me.dgvRecord(j,i).Value Is System.DBNull.Value Then MyExcel.Cells(i + 2,j + 1) = "" Else MyExcel.Cells(i + 2,j + 1) = dgvRecord(j,i).Value.ToString End If Next j Next i End Sub End Class
2、建立子窗体
如下图建立子窗体，选择继承创建的父窗体模板，然后就可以得到一模一样的子窗体了。通过在子窗体里重写一些方法和类，以实现不同的功能就可以了。
'************************************************* '作者：崔晓光 '小组： '说明：学生上机组合查询 '创建日期：2014.9.9 '版本号： '**********************************************/ Imports Entity.Entity Imports Entity '学生正在上机查询 Public Class FrmComQueryStudentOn ''' <summary> ''' 加载combo的item ''' </summary> ''' <param name="sender"></param> ''' <param name="e"></param> ''' <remarks></remarks> Private Sub FrmComQueryStudentOn_Load(sender As Object,e As EventArgs) Handles MyBase.Load cmbName1.Items.Add("卡号") cmbName1.Items.Add("上机日期") cmbName1.Items.Add("上机时间") cmbName1.Items.Add("机器名") cmbName2.Items.Add("卡号") cmbName2.Items.Add("上机日期") cmbName2.Items.Add("上机时间") cmbName2.Items.Add("机器名") cmbName3.Items.Add("卡号") cmbName3.Items.Add("上机日期") cmbName3.Items.Add("上机时间") cmbName3.Items.Add("机器名") End Sub ''' <summary> ''' 把加载的汉字转换成数据库的字段 ''' </summary> ''' <param name="cmbName"></param> ''' <returns></returns> ''' <remarks></remarks> Public Overrides Function ToEnglish(cmbName As String) As String Select Case cmbName Case "卡号" ToEnglish = "cardId" Case "上机日期" ToEnglish = "onDate" Case "上机时间" ToEnglish = "onTime" Case "机器名" ToEnglish = "local" Case "与" ToEnglish = " and " Case "或" ToEnglish = " or " Case Else ToEnglish = "" End Select End Function ''' <summary> ''' 传数据库表名 ''' </summary> ''' <returns></returns> ''' <remarks></remarks> Protected Overrides Function GetdbName() As String Return "LineRecord_Info" End Function ''' <summary> ''' 查询并把数据显示到datagridview中 ''' </summary> ''' <remarks></remarks> Protected Overrides Sub Todgv(ByVal comQueryEntity As ComQueryEntity) Dim returnList As New List(Of LineRecordEntity) '实例化集合，用来返回实体类 Dim frmComQueryParent As New FrmComQueryParent '实例化父窗体 Dim facadeComQuery As New Facade.FacadeComQuery '实例化外观 Try comQueryEntity.sqlString = frmComQueryParent.Query(Me,comQueryEntity) '获得拼接字符串 returnList = facadeComQuery.QueryStudentOn(comQueryEntity) '调用外观进行查询 '取出返回的实体 Dim lineRecordEntity As LineRecordEntity Dim dataTable As New Data.DataTable dataTable.Columns.Add("卡号") '自动创建列 dataTable.Columns.Add("上机日期") dataTable.Columns.Add("上机时间") dataTable.Columns.Add("机器名") Dim datanewRow As DataRow '声明一个新行 For i = 0 To returnList.Count - 1 lineRecordEntity = returnList.Item(i) datanewRow = dataTable.NewRow() '显示数据 datanewRow.Item(0) = lineRecordEntity.CardId datanewRow.Item(1) = lineRecordEntity.OnDate datanewRow.Item(2) = lineRecordEntity.OnTime datanewRow.Item(3) = lineRecordEntity.Local dataTable.Rows.Add(datanewRow) '将新行插入到表中 Next '绑定数据源 dgvRecord.AutoGenerateColumns = True '自动创建列 dgvRecord.AllowUserToAddRows = False Me.dgvRecord.DataSource = dataTable '显示信息 Me.dgvRecord.Refresh() Catch ex As Exception MsgBox(ex.Message) End Try End Sub End Class
3.DAL层，具体的查询
我们虽然传的是实体，但实际上是一个字符串，所以在D层，只要将字符串拼接起来就行
''' <summary>
''' 从表中查询学生上机信息,组合查询
''' </summary>
''' <param name="comQueryEntity">上机记录实体</param>
Public Function QueryOn(ByVal comQueryEntity As Entity.ComQueryEntity) As List(Of Entity.Entity.LineRecordEntity) Implements ILineRecord.QueryOn
Try
strsql = "select * from (select * from LineRecord_Info where offStatus='正在上机')as LineRecord_Info where " & comQueryEntity.sqlString.Trim
dataTable = sqlHelper.ExecSelectNo(CommandType.Text,strsql)
Dim returnList As New List(Of LineRecordEntity)
Dim lineRecordEntity As New LineRecordEntity
'封装查到的实体
For i = 0 To dataTable.Rows.Count - 1
lineRecordEntity.CardId = dataTable.Rows(i).Item(0).ToString
lineRecordEntity.OnDate = dataTable.Rows(i).Item(1).ToString
lineRecordEntity.OnTime = dataTable.Rows(i).Item(2).ToString
lineRecordEntity.Local = dataTable.Rows(i).Item(8).ToString
returnList.Add(lineRecordEntity)
Next
Return returnList
Catch ex As Exception
Throw
End Try
End Function
三、总结
自此，模版方法模式已经做完。这里注意的是组合查询的查询语句的方式，详见
机房收费系统 之 组合查询BUG。
模版方法的核心就是将整体架构抽象到父类中，具体的时间情况由子类拓展。在我们学习生活中也是，学者去抽象，去总结，这样才能提升层次。