组合查询——继承与多态的小练习

      

          上次个人版机房收费做的时候，组合查询这里做的不好，没有抽出模板，导致感觉有很多冗余，这次自己挑U层，一方面是感觉自己的B层和D层做的可以提升的空间目前很小了，另一方面就是想加强一下自己对细节的注意问题。

            上次组合查询是这样的，以代码为例：

           

       U层代码如下：

    

Imports System.Collections '引入HashTable类


Public Class frmColWorkInfo
    Dim HtZD As New Hashtable   '定义处理字段名转换的hashtable
    Dim HtZh As New Hashtable '定义处理组合关系转换的hashtable

#Region "单例模式：用来判断本窗体是否已经实例化"

    Private Shared ColWorkInfo As frmColWorkInfo = Nothing  '定义一个静态的类变量

    Private Sub New()

        ' 此调用是 Windows 窗体设计器所必需的。
        InitializeComponent()

        ' 在 InitializeComponent() 调用之后添加任何初始化。
    End Sub

    Public Shared Sub GetInstance() ' As frmCheckBalance '用来出现实例

        If IsNothing(ColWorkInfo) OrElse ColWorkInfo.IsDisposed Then '如果没有实例化
            '注意：1，要判断窗体是否已被实例化和窗体是否被销毁过（当关闭一个窗体时，资源被释放，但是并不是nothing）
            '     2,orelse产生了逻辑短路的问题，如果这里用Or会产生错误，因为可能会引用不存在的对象。

            ColWorkInfo = New frmColWorkInfo '实例化checkbanlance
        End If

        'ColWorkInfo.MdiParent = frmMain '设置父窗体
        ColWorkInfo.Show()     '显示窗体出来，但是此时子窗体还是被隐藏在下层的，必须要通过SetParent将它拿到上层来
        SetParent(ColWorkInfo.Handle.ToInt64, frmMain.Handle.ToInt64)  '设置窗体置前

        'Return checkBalance '返回
    End Sub

#End Region

    Private Sub frmColWorkInfo_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        '''''''''''''''''''''' HtZD '''''''''''''''''''''''处理字段名的转换'''''''''''''''''''''
        Dim ZDname() As String
        Dim eZdValue() As String
        MyDataGrid.AutoGenerateColumns = False

        '字段对应英文
        ZDname = {"教师", "级别", "注册日期", "注册时间", "注销日期", "注销时间", "机器名"}
        eZdValue = {"UserName", "tealevel", "regdate", "regtime", "DelDate", "DelTime", "reger"}

        '加载中文字段到下拉列表框中
        CmbZD1.Items.AddRange(ZDname)
        CmbZD2.Items.AddRange(ZDname)
        CmbZD3.Items.AddRange(ZDname)

        '中文为key，英文为value添加到hashtable
        For i As Integer = 0 To ZDname.Count - 1
            HtZD.Add(ZDname(i), eZdValue(i))
        Next
        '''''字段名转换处理完毕

        ''''''''''''''''''''''''''''HtZh'''''''''''''''''处理组合关系的转换'''''''''''''''''''''''''''
        Dim ZHname As String()
        Dim eZhValue As String()


        '字段对应英文
        ZHname = {"并且", "或者"}
        eZhValue = {"and", "or"}

        '加到下拉框中
        CmbZH1.Items.AddRange(ZHname)
        CmbZH2.Items.AddRange(ZHname)
        CmbZH1.Items.Add("")
        CmbZH2.Items.Add("")

        '添加到hashtable
        For t As Integer = 0 To ZHname.Count - 1
            HtZh.Add(ZHname(t), eZhValue(t))
        Next

    End Sub

    '查询
    Private Sub btnQuery_Click(sender As Object, e As EventArgs) Handles btnQuery.Click
        '清除单元格
        MyDataGrid.DataSource = Nothing '清空表格


        '先判断是否选择了条件，如果没有选择条件提示
        If CmbZD1.SelectedIndex < 0 OrElse CmbCZ1.SelectedIndex < 0 OrElse txtQuery1.Text.ToString = "" Then
            MsgBox("请选择查询条件！", vbOKOnly, "温馨提示")
            Exit Sub
        End If

        '查询前的非空判断
        Dim UserInfo As String() '定义字符数组

        '单条件查询
        If (CmbZH1.SelectedIndex < 0 Or CmbZH1.SelectedIndex = 2) And (CmbZH2.SelectedIndex < 0 Or CmbZH2.SelectedIndex > 1) Then
            UserInfo = {HtZD(CmbZD1.SelectedItem).ToString.Trim, CmbCZ1.SelectedItem.ToString.Trim, txtQuery1.Text.ToString.Trim}
            '2个条件查询
        ElseIf ((CmbZH2.SelectedIndex < 0 Or CmbZH2.SelectedIndex > 1) And (CmbZH1.SelectedIndex >= 0 And CmbZH1.SelectedIndex < 2)) Then

            '强制选择下一行
            If CmbZD2.SelectedIndex < 0 OrElse CmbCZ2.SelectedIndex < 0 OrElse txtQuery2.Text.ToString = "" Then
                MsgBox("请选择查询条件！", vbOKOnly, "温馨提示")
                Exit Sub
            End If


            UserInfo = {HtZD(CmbZD1.SelectedItem).ToString, CmbCZ1.SelectedItem.ToString, txtQuery1.Text.ToString, HtZh(CmbZH1.SelectedItem).ToString, HtZD(CmbZD2.SelectedItem).ToString, CmbCZ2.SelectedItem.ToString, txtQuery2.Text.ToString}

        Else    '3条件查询

            If CmbZD3.SelectedIndex < 0 OrElse CmbCZ3.SelectedIndex < 0 OrElse txtQuery3.Text.ToString = "" Then
                MsgBox("请选择查询条件！", vbOKOnly, "温馨提示")
                Exit Sub
            End If

            UserInfo = {HtZD(CmbZD1.SelectedItem).ToString.Trim, CmbCZ1.SelectedItem.ToString.Trim, txtQuery1.Text.ToString.Trim, HtZh(CmbZH1.SelectedItem).ToString.Trim, HtZD(CmbZD2.SelectedItem).ToString.Trim, CmbCZ2.SelectedItem.ToString.Trim, txtQuery2.Text.ToString.Trim, HtZh(CmbZH2.SelectedItem).ToString.Trim, HtZD(CmbZD3.SelectedItem).ToString.Trim, CmbCZ3.SelectedItem.ToString.Trim, txtQuery3.Text.ToString.Trim}
        End If


        '查询
        Dim ColWorkInfo As New BLL.ColWorkInfoBLL  '定义B层
        Dim mylist As New List(Of Entity.TeaInfo)   '查询到的集合

        mylist = ColWorkInfo.ChkColWorkInfo(UserInfo)

        If IsNothing(mylist) Then '如果没有查询到
            MsgBox("没有查询到符合条件的记录，请重新输入！", vbOKOnly, "温馨提示")
            Exit Sub

        End If

        MyDataGrid.DataSource = mylist '绑定数据源



    End Sub

    '退出
    Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
        Me.Dispose()

    End Sub


    '组合关系一确定下面能否使用
    Private Sub CmbZH1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles CmbZH1.SelectedIndexChanged
        If CmbZH1.SelectedItem.ToString <> "" Then
            '下面一栏能用
            CmbZD2.Enabled = True
            CmbCZ2.Enabled = True
            txtQuery2.Enabled = True
            CmbZH2.Enabled = True

        Else
            CmbZD2.Enabled = False
            CmbCZ2.Enabled = False
            txtQuery2.Enabled = False
            CmbZH2.Enabled = False
        End If

    End Sub

    '组合关系二确定下面能否使用
    Private Sub CmbZH2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles CmbZH2.SelectedIndexChanged
        If CmbZH2.SelectedItem.ToString <> "" Then
            '下面一栏能用
            CmbZD3.Enabled = True
            CmbCZ3.Enabled = True
            txtQuery3.Enabled = True
        Else
            CmbZD3.Enabled = False
            CmbCZ3.Enabled = False
            txtQuery3.Enabled = False
        End If

    End Sub
End Class

B层太简单，不写了，直接看D层：

  ''' <summary>
    ''' 查询操作员工作记录
    ''' </summary>
    ''' <param name="userInfo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ChkUserWorkInfo(ByVal userInfo As String()) As List(Of Entity.TeaInfo) Implements IDAL.IteaInfoDAO.ChkUserWorkInfo

        ReDim Preserve userInfo(0 To 13)

        Dim mySqlHelper As New SQLHelper '定义sqlHelper工具类
        Dim strSQL As String    '定义SQL查询语句
        Dim dt As New DataTable '定义返回表
        Dim mylist As New List(Of Entity.TeaInfo) '定义转换后的集合

        strSQL = "Pro_ZHQuery"
        Dim sqlParams As SqlParameter() = {New SqlParameter("@ChkTable", "T_TeaInfo"), New SqlParameter("@cboFieldA", userInfo(0)), New SqlParameter("@cboOperatorA", userInfo(1)), New SqlParameter("@txtConditionA", userInfo(2)), New SqlParameter("@cboRelationA", userInfo(3)), New SqlParameter("@cboFieldB", userInfo(4)), New SqlParameter("@cboOperatorB", userInfo(5)), New SqlParameter("@txtConditionB", userInfo(6)), New SqlParameter("@cboRelationB", userInfo(7)), New SqlParameter("@cboFieldC", userInfo(8)), New SqlParameter("@cboOperatorC", userInfo(9)), New SqlParameter("@txtConditionC", userInfo(10))}

        dt = mySqlHelper.ExecSelect(strSQL, CommandType.StoredProcedure, sqlParams)

        If dt.Rows.Count <= 0 Then  '如果没有查询到，返回空
            Return Nothing

        End If

        mylist = Entity.EntityConverter.convertToList(Of Entity.TeaInfo)(dt)
        Return mylist

    End Function

     还有后台的存储过程，在所有的组合查询里面，因为参数原型一样，所以就只用了一个存储过程，表明也是在调用存储过程的时候传入的：

ALTER procedure [dbo].[Pro_ZHQuery]

@ChkTable varchar(10),

@cboFieldA varchar(10),  
@cboOperatorA varchar(10),  
@txtConditionA varchar(10), 

@cboRelationA varchar(10)='and', 

@cboFieldB varchar(10)='1',  
@cboOperatorB varchar(10)='=',  
@txtConditionB varchar(10)='1',  

@cboRelationB varchar(10)='and', 

@cboFieldC varchar(10)='1',  
@cboOperatorC varchar(10)='=',  
@txtConditionC varchar(10)='1'
AS  
declare @TempSql varchar(500)--临时存放sql语句  
--CHAR(32)是空格,CHAR(39)单引号  

BEGIN  
set @TempSql='select * from '+ @ChkTable +' where '+CHAR(32)  
+@cboFieldA+@cboOperatorA+CHAR(39)+@txtConditionA+CHAR(39)+  
CHAR(32)+@cboRelationA+CHAR(32)+  
@cboFieldB+@cboOperatorB+CHAR(39)+@txtConditionB+CHAR(39)+  
+CHAR(32)+@cboRelationB+CHAR(32)  
+@cboFieldC+@cboOperatorC+CHAR(39)+@txtConditionC+CHAR(39)  
execute (@TempSql)  

END  

   

     整体看起来，前台，也就是界面，冗余太多了，3个窗体每个窗体的代码都粘了一遍。

   

     这次，做之前首先规划了一下到底该怎么改？首先来看看哪里有多余的：前台：除了加载的hashtable内容和显示的内容不一样，别的地方区别很小；后台，D层中，因为三个组合查询用到的D层方法只是返回值不同，所以，返回值这里可以用list (of T)代替，这样，前台后台冗余就少多了。

    首先，看看D层是怎么改的：

    

    

   当B层调用的时候，动态传入T类型，这样三个组合查询窗体就共用一个D层方法了。

    接下来是U层，我们将重复的地方抽出来：

Public Class Form1
    Public HtZD As New Hashtable   '定义处理字段名转换的hashtable
    Public HtZh As New Hashtable '定义处理组合关系转换的hashtable

    '处理条件关系转换
    Public ZDname() As String
    Public eZdValue() As String

    '处理组合关系转换
    Public ZHname As String()
    Public eZhValue As String()




    '字段转换，将用户选择的条件转换成数据库中可查询的字段
    Public MustOverride Sub ChangeIntoDBName()




    '窗体加载
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load



        MyDataGrid.AutoGenerateColumns = False '禁止自动加载数据源

        Call ChangeIntoDBName() '调用字段转换函数


        ''''''''''''''''''''''''''''''处理数据库中列名的转换
        '加载中文字段到下拉列表框中
        CmbZD1.Items.AddRange(ZDname)
        CmbZD2.Items.AddRange(ZDname)
        CmbZD3.Items.AddRange(ZDname)

        '中文为key，英文为value添加到hashtable
        For i As Integer = 0 To ZDname.Count - 1
            HtZD.Add(ZDname(i), eZdValue(i))
        Next
        '''''数据库中列名转换处理完毕




        ''''''''''''''''''''''''''''HtZh'''''''''''''''''处理组合关系的转换''''''''''''''''''''''''''

        '字段对应英文
        ZHname = {"并且", "或者"}
        eZhValue = {"and", "or"}
        '加到下拉框中
        CmbZH1.Items.AddRange(ZHname)
        CmbZH2.Items.AddRange(ZHname)
        CmbZH1.Items.Add("")
        CmbZH2.Items.Add("")

        '添加到hashtable
        For t As Integer = 0 To ZHname.Count - 1
            HtZh.Add(ZHname(t), eZhValue(t))
        Next

    End Sub


    '退出



    '组合关系一确定下面能否使用
    Private Sub CmbZH1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles CmbZH1.SelectedIndexChanged
        If CmbZH1.SelectedItem.ToString <> "" Then
            '下面一栏能用
            CmbZD2.Enabled = True
            CmbCZ2.Enabled = True
            txtQuery2.Enabled = True
            CmbZH2.Enabled = True

        Else
            CmbZD2.Enabled = False
            CmbCZ2.Enabled = False
            txtQuery2.Enabled = False
            CmbZH2.Enabled = False
        End If

    End Sub

    '组合关系二确定下面能否使用
    Private Sub CmbZH2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles CmbZH2.SelectedIndexChanged
        If CmbZH2.SelectedItem.ToString <> "" Then
            '下面一栏能用
            CmbZD3.Enabled = True
            CmbCZ3.Enabled = True
            txtQuery3.Enabled = True
        Else
            CmbZD3.Enabled = False
            CmbCZ3.Enabled = False
            txtQuery3.Enabled = False
        End If

    End Sub

    '查询
    Public Overridable Sub btnQuery_Click(sender As Object, e As EventArgs) Handles btnQuery.Click
        '清除单元格
        MyDataGrid.DataSource = Nothing '清空表格


        '先判断是否选择了条件，如果没有选择条件提示
        If CmbZD1.SelectedIndex < 0 OrElse CmbCZ1.SelectedIndex < 0 OrElse txtQuery1.Text.ToString = "" Then
            MsgBox("请选择查询条件！", vbOKOnly, "温馨提示")
            Exit Sub
        End If

        '查询前的非空判断
        Dim UserInfo As String() '定义字符数组

        '单条件查询
        If (CmbZH1.SelectedIndex < 0 Or CmbZH1.SelectedIndex = 2) And (CmbZH2.SelectedIndex < 0 Or CmbZH2.SelectedIndex > 1) Then

            UserInfo = {HtZD(CmbZD1.SelectedItem).ToString.Trim, CmbCZ1.SelectedItem.ToString.Trim, txtQuery1.Text.ToString.Trim}

            '2个条件查询
        ElseIf ((CmbZH2.SelectedIndex < 0 Or CmbZH2.SelectedIndex > 1) And (CmbZH1.SelectedIndex >= 0 And CmbZH1.SelectedIndex < 2)) Then

            '强制选择下一行
            If CmbZD2.SelectedIndex < 0 OrElse CmbCZ2.SelectedIndex < 0 OrElse txtQuery2.Text.ToString = "" Then
                MsgBox("请选择查询条件！", vbOKOnly, "温馨提示")
                Exit Sub
            End If


            UserInfo = {HtZD(CmbZD1.SelectedItem).ToString, CmbCZ1.SelectedItem.ToString, txtQuery1.Text.ToString, HtZh(CmbZH1.SelectedItem).ToString, HtZD(CmbZD2.SelectedItem).ToString, CmbCZ2.SelectedItem.ToString, txtQuery2.Text.ToString}

        Else    '3条件查询

            If CmbZD3.SelectedIndex < 0 OrElse CmbCZ3.SelectedIndex < 0 OrElse txtQuery3.Text.ToString = "" Then
                MsgBox("请选择查询条件！", vbOKOnly, "温馨提示")
                Exit Sub
            End If

            UserInfo = {HtZD(CmbZD1.SelectedItem).ToString.Trim, CmbCZ1.SelectedItem.ToString.Trim, txtQuery1.Text.ToString.Trim, HtZh(CmbZH1.SelectedItem).ToString.Trim, HtZD(CmbZD2.SelectedItem).ToString.Trim, CmbCZ2.SelectedItem.ToString.Trim, txtQuery2.Text.ToString.Trim, HtZh(CmbZH2.SelectedItem).ToString.Trim, HtZD(CmbZD3.SelectedItem).ToString.Trim, CmbCZ3.SelectedItem.ToString.Trim, txtQuery3.Text.ToString.Trim}
        End If




    End Sub



    Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click

    End Sub
End Class

   （需要注意的是：如果使用的是继承的窗体，即添加窗体的时候选择的是继承的窗体，那么控件的初始化写在form_load里面； 但是如果是类继承模板窗体，需要将控件的初始化写在父窗体的sub new里面）

   对比下，主要改动就是：

 

   然后在继承的窗体中，实现加载九个下拉框的文字记载：

 

  

 

       

         但是，查询按钮的click时间还要在父类方法的基础上进行一些修改：click时间里面在我们没有用模板的时候，放的是清空数据源，判断选了几个条件，然后才是调用B层查询接着显示结果，所以，我们需要前面的两个步骤，对后面的调用B层进行一个多态，所以，如上所示：

<span style="font-size:18px;"></span><pre name="code" class="vb"> Public Overridable Sub btnQuery_Click

        然后在子窗体中重写：

     

    

   

      至此，基本的窗体的继承算是实现了，但是，对于学生基本信息查询的窗体，这个窗体是在模板窗体的基础上多出两个button来的：

   

     

    

      多出了修改按钮和退出按钮，好吧，那就在窗体加载的时候用代码添加两个按钮吧：