【机房重构】组合查询之模板方法

╰+攻爆jí腚メ 2022-07-28 01:06 218阅读 0赞

在第一次敲机房的时候组合查询就是一个很值得学习的问题，在这次机房个人重构又重新的了解学习了一下这个功能，感觉比第一次好了很多，也是熟悉了很多的东西，这次我用了设计模式中的模板方法模式。感觉棒棒的！来分享一下！！

#       模板方法模式： #

定义一个操作中的算法的骨架，而将一些步骤延迟到子类中。模板方法使得子类可以不改变一个算法的结构即可重定义该算法的某些特定步骤。

#     模板方法模式（TemplateMethod）结构图 #

![Center][]

#   组合查询 #

## **      1、建立模板父窗体**** ** ##

Private Sub Father1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            '将参数传递给实体，赋初值  
            '字段名  
            GroupInquire.CmbName1 = ""
            GroupInquire.CmbName2 = ""
            GroupInquire.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 dgv.Columns.Count - 1
                dgv.Columns(i).Width = DataGridViewAutoSizeColumnMode.AllCells    '调整列宽为根据内容自动调整 
            Next
        End Sub
    
        '查询  
        Private Sub btnQuery_Click(sender As Object, e As EventArgs) Handles btnQuery.Click
            If CmbRelation1.Text = "" Then
                If CmbName1.Text = "" Or CmbName1.Text = "" Or txtContent1.Text = "" Then
                    MsgBox("第一行查询条件不能为空，请完善", , "提示")
                    Exit Sub
                End If
            End If
            If CmbRelation1.Text <> "" Then
                If CmbName1.Text = "" Or CmbName1.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 CmbName1.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
    
            '将参数传给实体  
            GroupInquire.DbName = GetdbName()
    
            GroupInquire.CmbName1 = GetEnglish(CmbName1.Text)
            GroupInquire.CmbName2 = GetEnglish(CmbName2.Text)
            GroupInquire.CmbName3 = GetEnglish(CmbName3.Text)
    
            GroupInquire.CmbMark1 = CmbMark1.Text.Trim
            GroupInquire.CmbMark2 = CmbMark2.Text.Trim
            GroupInquire.CmbMark3 = CmbMark3.Text.Trim
    
            GroupInquire.TxtContent1 = txtContent1.Text.Trim
            GroupInquire.TxtContent2 = txtContent2.Text.Trim
            GroupInquire.TxtContent3 = txtContent3.Text.Trim
    
            GroupInquire.CmbRelation1 = GetEnglish(CmbRelation1.Text)
            GroupInquire.CmbRelation2 = GetEnglish(CmbRelation2.Text)
    
            Dim dt As New DataTable
            Dim Generalfacade As New Facade.FaFather   '实例化外观层  
            dt = Generalfacade.GroupFather(GroupInquire)
            If (dt.Rows.Count = 0) Then
                MsgBox("没有符合条件的记录", , "提示")
                Exit Sub
            Else
                Call Todgv(GroupInquire)  '把表显示到datagridview中   
            End If
        End Sub
    
        '模板方法，定义虚函数GetEnglish，查询字段转化为数据库字段   
        Public Overridable Function GetEnglish(cmbName As String) As String
            Return ""
    
        End Function
    
        '获得数据库表名    
        Protected Overridable Function GetdbName() As String
            Return ""
        End Function
    
        '把表显示到datagridview中  
        Protected Overridable Sub Todgv(ByVal comQueryEntity As Entity.GroupInquire)
    
        End Sub
    
        '第一个组合关系是否为空  
        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
        ' 第二个组合关系是否为空   
        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
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            '清空
            CmbName1.Text = ""
            CmbName2.Text = ""
            CmbName3.Text = ""
            CmbMark3.Text = ""
            CmbMark2.Text = ""
            CmbMark1.Text = ""
            txtContent3.Text = ""
            txtContent1.Text = ""
            txtContent2.Text = ""
            CmbRelation1.Text = ""
            CmbRelation2.Text = ""
            'dgv.Rows.Remove(dgv.All)
    
    
        End Sub

## **   2、建立子窗体** ##

**     **如下图建立子窗体，然后选择要继承的父窗体，在子窗体里重写类和方法就OK了，只需在U层这一层写就行。我们来用学生基本信息查询为例。

子窗体的代码：

Private Sub frmStuBaseInfo_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            '赋初值  
            CmbName1.Items.Add("学号")
            CmbName1.Items.Add("姓名")
            CmbName1.Items.Add("性别")
            CmbName1.Items.Add("专业")
            CmbName1.Items.Add("年级")
            CmbName1.Items.Add("班级")

CmbName2.Items.Add("学号")
            CmbName2.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("专业")
            CmbName3.Items.Add("年级")
            CmbName3.Items.Add("班级")
        End Sub
        '把加载的汉字转换成数据库的字段   
        Public Overrides Function GetEnglish(cmbName As String) As String
            Select Case cmbName
                Case "学号"
                    GetEnglish = "chr_StudentNo"
                Case "姓名"
                    GetEnglish = "chr_StudentName"
                Case "性别"
                    GetEnglish = "chr_Sex"
                Case "专业"
                    GetEnglish = "chr_Department"
                Case "年级"
                    GetEnglish = "chr_Grade"
                Case "班级"
                    GetEnglish = "chr_Class"
                Case "与"
                    GetEnglish = "and"
                Case "或"
                    GetEnglish = "or"
                Case Else
                    GetEnglish = ""
            End Select
        End Function
    
        '传数据库表名   
        Protected Overrides Function GetdbName() As String
            Return "T_Student"
        End Function
    
        '查询并把数据显示到datagridview中  
        Protected Overrides Sub Todgv(ByVal comQueryEntity As Entity.GroupInquire)
    
            Dim table As New DataTable
            Dim father As New Father1
            Dim GeneralFacade As New Facade.FaFather
            Try
               table = GeneralFacade.GroupFather(comQueryEntity)
                If table.Rows.Count = 0 Then
                    table.Clear()
                    dgv.DataSource = Nothing
                    dgv.Refresh()
                Else
                    dgv.DataSource = table
                    dgv.Columns(0).Visible = False
                    dgv.Columns(0).HeaderText = "卡号"
                    dgv.Columns(1).HeaderText = "学号"
                    dgv.Columns(2).HeaderText = "姓名"
                    dgv.Columns(3).HeaderText = "专业"
                    dgv.Columns(4).HeaderText = "性别"
                    dgv.Columns(5).HeaderText = "年级"
                    dgv.Columns(6).HeaderText = "班级"
                    'dgv.Columns(7).HeaderText = "注释"
                    dgv.Columns(7).HeaderText = "注册日期"
                    dgv.Columns(8).HeaderText = "注册时间"
             End If
            Catch ex As Exception
                MsgBox(ex.Message, vbOKOnly, "提示")
            End Try
        End Sub

##    3、DAL层数据库查询 ##

Public Function IGroupCheck(father As Entity.GroupInquire) As DataTable Implements IGroupInquire.IGroupCheck
            Dim sql As String
            Dim table As DataTable
            sql = "select * from " & father.DbName & " where "
            If Trim(father.CmbRelation1) = "" Then  '有一组查询条件  
                sql = sql & father.CmbName1 & father.CmbMark1 & "'" & father.TxtContent1 & "'"
            Else
                If Trim(father.CmbRelation2) = "" Then '有两组查询条件  
                    sql = sql & father.CmbName1 & father.CmbMark1 & "'" & father.TxtContent1 & "'" & father.CmbRelation1 & " " & father.CmbName2 & father.CmbMark2 & "'" & father.TxtContent2 & "'"
                Else
                    sql = sql & father.CmbName1 & father.CmbMark1 & "'" & father.TxtContent1 & "'" & father.CmbRelation1 & " " & father.CmbName2 & father.CmbMark2 & "'" & father.TxtContent2 & "'" & father.CmbRelation2 & " " & father.CmbName3 & father.CmbMark3 & "'" & father.TxtContent3 & "'"
                End If
            End If
            '模板的无参查询  
            table = sqlHelper.ExecSelect(sql, CommandType.Text)
            Return table
        End Function

[Center]: /images/20220727/d4c0dd89e95f49da8985e6b1c038b656.png