VB.NET版机房收费之模板方法实现组合查询

    组合查询算是机房中比较复杂的一块，思路很容易，就是将多个查询条件放在一起限制查询结果，但工作量确实很大，而且在机房中有四个这样的窗体，学习完设计模式后的感触就是每次代码有重复出现的就想着自己用一用那个模式，但自己真正实践时还真有挑战，策略模式看着别人博客也没有实现，组合查询也是参考着他们博客实现的，这个阶段模仿着学吧。

       模板方法定义了一个操作的算法的骨架，把具体的实现延迟到子类中，就是说把重复的代码上升到父类，子类去继承并重写父类中的方法，所以父类中放的也都是一些抽象的行为（虚方法），这样很好的实现代码复用。

一：窗体的继承

1.这是组合查询的父窗体（添加时和普通窗体没什么区别）：

2.这是子窗体创建时的不同：（以学生上机记录查询为例）

添加继承的窗体

选择要继承的窗体，这样就将学生上机记录的组合查询子窗体建好，创建好的子窗体中控件都是带小锁形状的，不能编辑，只有在form_load中重写父类的方法。

接着看一下代码部分子类是如何继承父类的

二：代码的继承

1.父类

U层主要是窗体加载和一些虚方法：

Imports Charge.BLLPublic Class frmGroupInquire    Protected enGroup As New ChargeEntity.GroupInquireEntity  '实例化实体    Private Sub frmGroupInquire_Load(sender As Object, e As EventArgs) Handles MyBase.Load        Label1.Text = "输入查" + Chr(10) + "询条件"        '窗体加载时给操作符和组合关系赋值        cmbOperator1.Items.Add("=")        cmbOperator1.Items.Add(">")        cmbOperator1.Items.Add("<")        cmbOperator1.Items.Add("<>")        cmbOperator2.Items.Add("=")        cmbOperator2.Items.Add(">")        cmbOperator2.Items.Add("<")        cmbOperator2.Items.Add("<>")        cmbOperator3.Items.Add("=")        cmbOperator3.Items.Add(">")        cmbOperator3.Items.Add("<")        cmbOperator3.Items.Add("<>")        cmbRelation1.Items.Add("与")        cmbRelation1.Items.Add("或")        cmbRelation2.Items.Add("与")        cmbRelation2.Items.Add("或")        '窗体加载后，后两行文本框不能用        cmbField2.Enabled = False        cmbOperator2.Enabled = False        txtContent2.Enabled = False        cmbField3.Enabled = False        cmbOperator3.Enabled = False        txtContent3.Enabled = False    End Sub    Private Sub btnInquire_Click(sender As Object, e As EventArgs) Handles btnInquire.Click        DataGridView1.DataSource = Nothing '清空DataGridView1内容        '第一个组合关系为空，第一行不能为空        If cmbRelation1.Text = "" Then            Dim arrayControl() As Control            ReDim Preserve arrayControl(2)            arrayControl(0) = cmbField1            arrayControl(1) = cmbOperator1            arrayControl(2) = txtContent1            If Verdict.IsSomeEmptyText(arrayControl) Then  '调用模块判断是否为空                Exit Sub            End If        End If        '第一个组合关系不为空，前两行不能为空        If cmbRelation1.Text <> "" Then            Dim arrayControl() As Control            ReDim Preserve arrayControl(5)            arrayControl(0) = cmbField1            arrayControl(1) = cmbOperator1            arrayControl(2) = txtContent1            arrayControl(3) = cmbField2            arrayControl(4) = cmbOperator2            arrayControl(5) = txtContent2            If Verdict.IsSomeEmptyText(arrayControl) Then  '调用模块判断是否为空                Exit Sub            End If        End If        '第二个组合关系不为空，都不能为空        If cmbRelation2.Text <> "" Then            Dim arrayControl() As Control            ReDim Preserve arrayControl(8)            arrayControl(0) = cmbField1            arrayControl(1) = cmbOperator1            arrayControl(2) = txtContent1            arrayControl(3) = cmbField2            arrayControl(4) = cmbOperator2            arrayControl(5) = txtContent2            arrayControl(6) = cmbField3            arrayControl(7) = cmbOperator3            arrayControl(8) = txtContent3            If Verdict.IsSomeEmptyText(arrayControl) Then  '调用模块判断是否为空                Exit Sub            End If        End If        '给实体赋值        enGroup.cmbField1 = GetDBName(cmbField1.Text.Trim())        enGroup.cmbField2 = GetDBName(cmbField2.Text.Trim())        enGroup.cmbField3 = GetDBName(cmbField3.Text.Trim())        enGroup.cmbOperator1 = cmbOperator1.Text.Trim()        enGroup.cmbOperator2 = cmbOperator2.Text.Trim()        enGroup.cmbOperator3 = cmbOperator3.Text.Trim()        enGroup.txtContect1 = txtContent1.Text.Trim()        enGroup.txtContect2 = txtContent2.Text.Trim()        enGroup.txtContect3 = txtContent3.Text.Trim()        enGroup.cmbRelation1 = GetDBName(cmbRelation1.Text.Trim())        enGroup.cmbRelation2 = GetDBName(cmbRelation2.Text.Trim())        enGroup.listName = GetList()        Dim groupinquirebll As New GroupInquireBLL '实例化B层        Dim dt As New DataTable        Call Dview()    End Sub    Protected Overridable Function GetDBName(ByVal control As String) As String '定义虚函数GetDBName，或许不同数据库的字段名        Return ""    End Function    Protected Overridable Function GetList() As String '定义虚函数GetList，或许不同数据库的表名        Return ""    End Function    Protected Overridable Sub Dview() '定义虚函数Dview，将数据显示在控件中    End Sub    Private Sub cmbRelation1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbRelation1.SelectedIndexChanged        If cmbRelation1.Text <> "" Then  '组合关系1是否为空，影响到第2组条件的行操作            cmbField2.Enabled = True            cmbOperator2.Enabled = True            txtContent2.Enabled = True        End If    End Sub    Private Sub cmbRelation2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbRelation2.SelectedIndexChanged        If cmbRelation2.Text <> "" Then   '组合关系2是否为空，影响到第3组条件的行操作            cmbField3.Enabled = True            cmbOperator3.Enabled = True            txtContent3.Enabled = True        End If    End Sub    Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click        Me.Close()        frmMain.Panel1.Visible = True        frmMain.Panel2.Visible = True    End Sub    Private Sub btnToExcel_Click(sender As Object, e As EventArgs) Handles btnToExcel.Click        ToExcel.ToExcel(DataGridView1) '调用导出excel表格公共方法    End SubEnd Class

其他层代码和普通查询基本一致，在这里也用了存储过程

-- =============================================-- Author:李立平-- Create date: 2014-09-05-- Description:GroupInquire-- =============================================ALTER PROCEDURE [dbo].[PROC_GroupInquire]-- Add the parameters for the stored procedure here@cmbField1 varchar(10),@cmbField2 varchar(10),@cmbField3 varchar(10),@cmbOperator1 varchar(10),@cmbOperator2 varchar(10),@cmbOperator3 varchar(10),@txtContect1 varchar(10),@txtContect2 varchar(10),@txtContect3 varchar(10),@cmbRelation1 varchar(10),@cmbRelation2 varchar(10),@listName varchar (20)  --获取表的名称AS     declare @Tempsql varchar(500) --临时存放sql语句--char(32)空格，char（39）单引号BEGINSET @Tempsql ='select * from '+@listName+' where '+@cmbField1+@cmbOperator1+char(39)+@txtContect1+char(39)    if @cmbRelation1 !=''  --第一个组合关系不为空    begin       set  @Tempsql=@Tempsql+@cmbRelation1+char(32)+@cmbField2 +@cmbOperator2 +char(39) +@txtContect2 +char(39)          if @cmbRelation2 !='' --第二个组合关系不为空          begin               set @Tempsql =@Tempsql +@cmbRelation2 +char(32)+@cmbField3  +@cmbOperator3 +char(39) +@txtContect3 +char(39)      ENDENDEXECUTE(@Tempsql)END

    因为涉及到不止一个SQL语句，所以组合查询单独建立了一个实体类（属性就是窗体上使用到的控件），在D层用存储过程代替SQL语句（在这里想说的是自己返回的datatable，用返回集合实现不了，还不知道为什么？）

2、子类

子类相对来说更容易了，就是对父类U层方法的重写（还是以查询上机记录为例）

Imports System.Collections '引入哈希表Public Class frmLineStatisticsGI    Private Shared fLS As frmLineStatisticsGI   '声明一个静态类变量，类似C#中static    Private Sub New()   '初始化为私有，外部代码不能直接new来实例化该窗体        ' 此调用是设计器所必需的。        InitializeComponent()        ' 在 InitializeComponent() 调用之后添加任何初始化。    End Sub    Public Shared Function GetInstance() As frmLineStatisticsGI  '得到该窗体实例的方法        If fLS Is Nothing OrElse fLS.IsDisposed Then  '当窗体关闭或者被处理过就实例化该窗体，因为关闭后不会将变量设置为nothing，只是将窗体dispose掉            fLS = New frmLineStatisticsGI            fLS.MdiParent = frmMain        End If        Return fLS  '如果已经实例化则返回窗体本身    End Function    Private Sub frmLineStatistics_Load(sender As Object, e As EventArgs) Handles MyBase.Load        Me.Text = "上机统计信息查询" '表名        Dim haField As New Hashtable '定义处理字符串转换的hashtable        Dim FieldName() As String        Dim FieldValue() As String  '使用哈希表将数据库字段进行转换        '数据库中字段对应相应英文        FieldName = {"卡号", "学号", "上机日期", "上机时间", "下机日期", "下机时间", "消费金额", "余额", "备注"}        FieldValue = {"cardNo", "studentNo", "onDate", "onTime", "offDate", "offTime", "comsumeCash", "presentCash", "status"}        '放到Field字段中        cmbField1.Items.AddRange(FieldName)        cmbField2.Items.AddRange(FieldName)        cmbField3.Items.AddRange(FieldName)        '中文关键字，英文value添加到hashtable        For i As Integer = 0 To FieldName.Count - 1            haField.Add(FieldName(i), FieldValue(i))        Next    End Sub    '重写获得表名方法    Protected Overrides Function GetList() As String        Return "T_Line"    End Function    '重写转换成数据库字段方法    Protected Overrides Function GetDBName(control As String) As String        Select Case control            Case "卡号"                Return "cardNo"            Case "学号"                Return "studentNo"            Case "上机日期"                Return "onDate"            Case "上机时间"                Return "onTime"            Case "下机日期"                Return "offDate"            Case "下机时间"                Return "offTime"            Case "消费金额"                Return "consumeCash"            Case "余额"                Return "presentCash"            Case "备注"                Return "status"            Case "与"                Return "and"            Case "或"                Return "or"            Case Else                Return ""        End Select    End Function    '重写返回数据到datagridview方法    Protected Overrides Sub Dview()        DataGridView1.DataSource = Nothing '清空控件内容        Dim groupinquirebll As New Charge.BLL.GroupInquireBLL        Dim dt As New DataTable        dt = groupinquirebll.GroupInquire(enGroup)        If dt.Rows.Count = 0 Then            MsgBox("没有记录！")        Else            DataGridView1.DataSource = Nothing            DataGridView1.DataSource = dt            DataGridView1.Columns(0).HeaderText = "卡号"            DataGridView1.Columns(1).HeaderText = "学号"            DataGridView1.Columns(2).HeaderText = "上机日期"            DataGridView1.Columns(3).HeaderText = "上机时间"            DataGridView1.Columns(4).HeaderText = "下机日期"            DataGridView1.Columns(5).HeaderText = "下机时间"            DataGridView1.Columns(6).HeaderText = "消费金额"            DataGridView1.Columns(6).HeaderText = "余额"            DataGridView1.Columns(6).HeaderText = "备注"        End If    End SubEnd Class

三：总结

    在机房中有很多类似的窗体，比如查询充值记录和上机记录，充值金额查询和退还金额查询，有种相见恨晚的感觉，模板方法最大的好处就是实现代码复用，减少工作量，还有就是设计模式是一种思想，只有在应用中解决问题后才发挥出它们的价值，多多思考！