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

前端之家收集整理的这篇文章主要介绍了VB.NET版机房收费之模板方法实现组合查询前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

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

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

一:窗体的继承

1.这是组合查询的父窗体(添加时和普通窗体没什么区别):


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


添加继承的窗体


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


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

二:代码的继承

1.父类

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

Imports Charge.BLL
Public 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 Sub
End 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)单引号
BEGIN
	
	SET @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)
	      END
END
EXECUTE(@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 Sub
End Class

三:总结

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

猜你在找的VB相关文章