机房收费系统中,一个比较让人纠结功能就是组合查询,不仅仅是代码比较多,而且大多都是重复的代码,也正是因为如此,才比较适合模版方法模式。
一、基本介绍
模版方式模式是定义一个操作中的算法的骨架,而将步骤延迟到子类中。
模板方法使得子类可以不改变一个算法的结构即可重定义算法的某些特定步骤。
类图
二、具体实现
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。
模版方法的核心就是将整体架构抽象到父类中,具体的时间情况由子类拓展。在我们学习生活中也是,学者去抽象,去总结,这样才能提升层次。