个人机房重构——组合查询及优化

        个人机房重构逐渐接近尾声，就遇到了组合查询，本来组合查询并不是一个很难的问题。但是，整个机房重构的过程中，我们要利用组合查询完成学生基本信息维护、学生上机统计信息查询、操作员工作记录、学生上机状态查询。同时我们注意到的是，这几个功能几乎完全一样，只是所要查询到的条件略有差别，大量的重复可不是面向对象的优点，于是我们就开始想解决方案。大话设计模式的学习给我们带来了福音。

模板方法

        当我们要完成在某一细节层次一致的一个过程或一系列步骤，但其个别步骤在更详细的层次上的实现可能不同时，我们通常考虑用模板方法模式来处理。

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

                

     

         模板方法模式是通过把不变的行为搬移到超类，去除子类中的重复代码来体现它的优势，也就是说，模板方法就是提供了一个很好的代码复用平台。那么，说的再多不实践是体会不到的，那么就让我们一起看看有什么具体的应用。

父窗体及优化

           为什么说是优化的呢？因为，在操作员工作记录中，我们会用到日期和时间查询。但是，一个小小的TextBox怎么能满足我们的要求呢？既然是时间，那么就有专门的控件：DateTimePicker

           那么，让我们看看父窗体。

代码解析

具体如何实现我们在代码中进行说明：

Public Class TemplateMethod    '实例化组合查询的实体    Protected GroupInquire As New Entity.TemplateMethodEntity    '定义接收查询条件的变量    Public txtRemark1 As String    Public txtRemark2 As String    Public txtRemark3 As String    Private Sub TemplateMethod_Load(sender As Object, e As EventArgs) Handles MyBase.Load        dtp1.Visible = False            'DateTimePicker控件的可见性        dtp2.Visible = False        dtp3.Visible = False        cmbName2.Enabled = False        '控件的可用性        cmbName3.Enabled = False        cmbOper2.Enabled = False        cmbOper3.Enabled = False        cmbGroup2.Enabled = False        txtSelect2.Enabled = False        txtSelect3.Enabled = False        '自动调整列宽        Dim i As Integer        For i = 0 To dgvLine.Columns.Count - 1            dgvLine.Columns(i).Width = DataGridViewAutoSizeColumnMode.AllCells        Next    End Sub    '查询    '点击查询按钮    Private Sub btnOK_Click(sender As Object, e As EventArgs) Handles btnOK.Click        Dim frm As New TemplateMethod        '判断查询内容是否为空        If cmbGroup1.Text = "" Then            If cmbName1.Text = "" Or cmbOper1.Text = "" Then                MsgBox("字段名或运算符不能为空！", , "提示！")                Exit Sub            End If        End If        If txtSelect1.Text = "" And dtp1.Text = "" Then            MsgBox("查询内容不能为空！", , "警告")            Exit Sub        End If        '判断第二行是否为空        If cmbGroup1.Text <> "" Then            If cmbName1.Text = "" Or cmbOper1.Text = "" Or cmbName2.Text = "" Or cmbOper2.Text = "" Then                MsgBox("字段名或运算符不能为空！", , "提示！")                Exit Sub            ElseIf txtSelect1.Text = "" And dtp1.Text = "" Then                MsgBox("第一行查询条件不能为空！")                Exit Sub            ElseIf txtSelect2.Text = "" And dtp2.Text = "" Then                MsgBox("第二行查询条件不能为空！")                Exit Sub            End If        End If        '判断第三行是否为空        If cmbGroup2.Text <> "" Then            If cmbName1.Text = "" Or cmbOper1.Text = "" Or cmbName2.Text = "" Or cmbOper2.Text = "" Or cmbName3.Text = "" Or cmbOper3.Text = "" Then                MsgBox("您输入的查询条件不能为空！", , "提示！")                Exit Sub            ElseIf txtSelect1.Text = "" And dtp1.Text = "" Then                MsgBox("第一行查询条件不能为空！", , "提示")                Exit Sub            ElseIf txtSelect2.Text = "" And dtp2.Text = "" Then                MsgBox("第二行查询条件不能为空！", , "提示")                Exit Sub            ElseIf txtSelect3.Text = "" And dtp3.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.cmbOper1 = cmbOper1.Text.Trim        GroupInquire.cmbOper2 = cmbOper2.Text.Trim        GroupInquire.cmbOper3 = cmbOper3.Text.Trim        GroupInquire.txtSelect1 = txtRemark1                  '设置一个变量，将textBox或者DateTimePicker控件的值赋值后传递给实体        GroupInquire.txtSelect2 = txtRemark2        GroupInquire.txtSelect3 = txtRemark3        GroupInquire.cmbGroup1 = GetEnglish(cmbGroup1.Text)        GroupInquire.cmbGroup2 = GetEnglish(cmbGroup2.Text)        GroupInquire.SqlText = Query(frm, GroupInquire)        '将拼接的字符串传递给实体层        '进行数据查询并返回dt        Dim dt As New DataTable        Dim Generalfacade As New Facade.TemplateMethodFacade        dt = Generalfacade.SelectInfo(GroupInquire)        If (dt.Rows.Count = 0) Then                                 '若是没有数据，则清空原来查询到的数据            MsgBox("没有符合条件的记录！", , "提示！")            dt.Clear()            dgvLine.DataSource = Nothing            dgvLine.Refresh()            Exit Sub        Else            Call Todgv(dt)              ' 若是查询到数据，返回dt表，等待子类接收        End If    End Sub    '连接字符串，以便供给D层进行查询    Public Function Query(frm As TemplateMethod, ByVal GroupInquire As Entity.TemplateMethodEntity) As String        Dim sql As String        sql = GroupInquire.cmbName1 & GroupInquire.cmbOper1 & "'" & GroupInquire.txtSelect1 & "'"        If Trim(GroupInquire.cmbGroup1) <> "" Then            If Trim(GroupInquire.cmbGroup2) = "" Then   '有两组查询条件                sql = sql & GroupInquire.cmbGroup1 & GroupInquire.cmbName2 & GroupInquire.cmbOper2 & "'" & GroupInquire.txtSelect2 & "'"            Else                sql = sql & GroupInquire.cmbGroup1 & GroupInquire.cmbName2 & GroupInquire.cmbOper2 & "'" & GroupInquire.txtSelect2 & "'" & GroupInquire.cmbGroup2 & GroupInquire.cmbName3 & GroupInquire.cmbOper3 & "'" & GroupInquire.txtSelect3 & "'"            End If        End If        Return sql    End Function    ' 转换中文字符为英文字符    Public Overridable Function GetEnglish(cmbName As String) As String        Return ""    End Function    '获得所要查询数据库表    Protected Overridable Function GetdbName() As String        Return ""    End Function    ' 传递父类的datatable表给子类    Protected Overridable Sub Todgv(ByVal dt As DataTable)    End Sub    Private Sub cmbGroup1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbGroup1.SelectedIndexChanged        If cmbGroup1.Text = "" Then            cmbName2.Enabled = False              '如果没有选择第一个组合查询，则之后的控件都不可用            cmbName3.Enabled = False            cmbOper2.Enabled = False            cmbOper3.Enabled = False            cmbGroup2.Enabled = False            txtSelect2.Enabled = False            txtSelect3.Enabled = False        Else            cmbName2.Enabled = True            cmbOper2.Enabled = True            txtSelect2.Enabled = True            cmbGroup2.Enabled = True        End If    End Sub    Private Sub cmbGroup2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbGroup2.SelectedIndexChanged        If cmbGroup2.Text = "" Then                    '如果没有选择第二个组合查询，则之后的控件都不可用            cmbName3.Enabled = False            cmbOper3.Enabled = False            txtSelect3.Enabled = False        Else            cmbName3.Enabled = True            cmbOper3.Enabled = True            txtSelect3.Enabled = True        End If    End Sub</span>

以上基本上就是父窗体的代码了，重要的是理解，多条件的组合查询如何完成和实现就足够了。

优化

下面的代码就是对于应用了DateTimePicker控件的实现

<span style="font-size:18px;"> '判断查询的是否为时间。    Private Sub txtSelect1_GotFocus(sender As Object, e As EventArgs) Handles txtSelect1.GotFocus        If cmbName1.Text = "上机日期" Or cmbName1.Text = "下机日期" Or cmbName1.Text = "上机时间" Or cmbName1.Text = "下机时间" Then            dtp1.Format = DateTimePickerFormat.Custom            dtp1.Visible = True            txtSelect1.Visible = False            If cmbName1.Text = "上机日期" Or cmbName1.Text = "下机日期" Then                txtRemark1 = Format(dtp1.Value, "yyyy-MM-dd")            ElseIf cmbName1.Text = "上机时间" Or cmbName1.Text = "下机时间" Then                dtp1.Format = DateTimePickerFormat.Time                txtRemark1 = Format(dtp1.Value, "hh:mm:ss")            End If        End If    End Sub    '若是TextBox失去焦点，判断若不是采用的DateTime控件，则将TextBox中数据传入实体层    Private Sub txtSelect1_LostFocus(sender As Object, e As EventArgs) Handles txtSelect1.LostFocus        If dtp1.Visible = False Then            txtRemark1 = txtSelect1.Text.Trim()        End If    End Sub    '当cmbName重新获得焦点时，恢复初始状态    Private Sub cmbName1_GotFocus(sender As Object, e As EventArgs) Handles cmbName1.GotFocus        dtp1.Visible = False        txtSelect1.Visible = True    End Sub</span>

以上只是对于其中一个CmbName的判断，同样的方法判断三次就可以了。就可以实现两个控件的相互切换了。

子类窗体的实现

子类窗体继承父类窗体的属性来实现模板方法。具体步骤如下：

子类窗体的创建过程

 子窗体代码：

<span style="font-size:18px;">Public Class frmInquryOperator    Private Sub frmInquryOperator_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("机器名")        cmbName1.SelectedIndex = 0        cmbOper1.Items.Add("=")        cmbOper1.Items.Add("<>")        cmbOper1.Items.Add(">")        cmbOper1.Items.Add("<")        cmbOper1.SelectedIndex = 0        cmbName2.Items.Add("教师")        cmbName2.Items.Add("上机日期")        cmbName2.Items.Add("上机时间")        cmbName2.Items.Add("下机日期")        cmbName2.Items.Add("下机时间")        cmbName2.Items.Add("机器名")        cmbName2.SelectedIndex = 0        cmbOper2.Items.Add("=")        cmbOper2.Items.Add("<>")        cmbOper2.Items.Add(">")        cmbOper2.Items.Add("<")        cmbOper2.SelectedIndex = 0        cmbName3.Items.Add("教师")        cmbName3.Items.Add("上机日期")        cmbName3.Items.Add("上机时间")        cmbName3.Items.Add("下机日期")        cmbName3.Items.Add("下机时间")        cmbName3.Items.Add("机器名")        cmbName3.SelectedIndex = 0        cmbOper3.Items.Add("=")        cmbOper3.Items.Add("<>")        cmbOper3.Items.Add(">")        cmbOper3.Items.Add("<")        cmbOper3.SelectedIndex = 0        cmbGroup1.Items.Add("与")        cmbGroup1.Items.Add("或")        cmbGroup2.Items.Add("与")        cmbGroup2.Items.Add("或")    End Sub    '将中文字符串转换为英语    Public Overrides Function GetEnglish(cmbName As String) As String        Select Case cmbName            Case "教师"                GetEnglish = "UserID"            Case "上机日期"                GetEnglish = "LoginDate"            Case "上机时间"                GetEnglish = "LoginTime"            Case "下机日期"                GetEnglish = "LogoutDate"            Case "下机时间"                GetEnglish = "LogoutTime"            Case "机器名"                GetEnglish = "Computer"            Case "与"                GetEnglish = " and "            Case "或"                GetEnglish = " or "            Case Else                GetEnglish = ""        End Select    End Function    '传数据库表名    Protected Overrides Function GetdbName() As String        Return "WorkLog_Info"    End Function    '查询并把数据显示到DataGridView中    Protected Overrides Sub Todgv(ByVal dt As DataTable)        Try            dgvLine.DataSource = dt            dgvLine.Columns(0).HeaderText = "教师"            dgvLine.Columns(0).Frozen = True            dgvLine.Columns(1).HeaderText = "级别"            dgvLine.Columns(1).Frozen = True            dgvLine.Columns(2).HeaderText = "登陆日期"            dgvLine.Columns(2).Frozen = True            dgvLine.Columns(3).HeaderText = "登陆时间"            dgvLine.Columns(3).Frozen = True            dgvLine.Columns(4).HeaderText = "下机日期"            dgvLine.Columns(4).Frozen = True            dgvLine.Columns(5).HeaderText = "下机时间"            dgvLine.Columns(5).Frozen = True            dgvLine.Columns(6).HeaderText = "机器名"            dgvLine.Columns(6).Frozen = True        Catch ex As Exception            MsgBox(ex.Message, vbOKOnly, "提示")        End Try    End SubEnd Class</span>

D层代码

Public Function ISelect(GroupInquire As Entity.TemplateMethodEntity) As DataTable Implements ITemplateMethodDAL.ISelect        Dim dt As DataTable        Dim cmdText As String        Dim sqlhelper As New SQLHelper.SqlHelper        cmdText = "select * from " & GroupInquire.DbName & " Where " & GroupInquire.SqlText      ‘拼接查询语句，连接字符串    '模板的无参查询        dt = sqlhelper.ExecSelect(cmdText, CommandType.Text)        Return dt    End Function

总结：将拼接字符串放在U层，并传给实体层，在D层查询时进行接收，这样方便以后的修改。组合查询是对模板方法的一次很好的利用，通过不断地实践来合理的运用设计模式，会给我们的编程带来巨大的好处。简单易复用的代码永远是最好的。