组合查询=实体+模板方法——机房重构点滴积累

来源：互联网发布：js 全局命名空间编辑：程序博客网时间：2024/05/01 02:15

准备敲组合查询的时候，通过上网查资料、看同学们的博客，本菜鸟渐渐地有了思路，但是到具体实践的时候，遇到了令人纠结的“DataTable”和“List（of ）”，关于这两个知识点毫无疑问各有千秋，网上也有一些介绍，通过查资料分析，总的来说还是用泛型比较好（最后有一些链接推荐给大家），所以自然就决定用泛型了，不过好像历史上咱们的组合查询还没有有返回泛型的这种情况（或许是我没有百度到）SO here we go

废话完具体到组合查询，this查询用到了模板方法：将相同的代码提前到父窗体，这样代码被极好的被复用了，这种思想简洁省事效率高而且不易出错，真正的事倍功半，who 想到的？崇拜ing

UI层代码：

<span style="font-family:SimSun;font-size:18px;"> '第一行不能为空        Dim i As Integer        If GroupBox1.Controls(i).Text = "" Then            MessageBox.Show("第一行查询条件不能为空", "温馨提示")            Exit Sub        End If        '如果第一个组合条件不为空        If cmbCombin1.Text <> "" Then            If GroupBox1.Controls(i).Text = "" Or GroupBox2.Controls(i).Text = "" Then                MsgBox("请完善要查询的条件", vbOK + vbExclamation, "温馨提示")                Exit Sub            End If        End If        '如果第二个组合条件不为空        '判断同时 给实体传参        Dim mylist As New Entity.CHECKEntity        mylist.getTable = GetTable() '得到数据表名字        mylist.GetEntity = GetEntity() '得到实体        mylist.CmbName1 = GetEnglish(cmbName1.Text.Trim)        mylist.CmbName2 = GetEnglish(cmbName2.Text.Trim)        mylist.CmbName3 = GetEnglish(cmbName3.Text.Trim)        '选择关系        mylist.CmbRelation1 = cmbRelation1.Text.Trim        mylist.CmbRelation2 = cmbRelation2.Text.Trim        mylist.CmbRelation3 = cmbRelation3.Text.Trim        '要查询的内容        mylist.TxtQuery1 = txtQuery1.Text.Trim        mylist.TxtQuery2 = txtQuery2.Text.Trim        mylist.CmbRelation3 = txtQuery3.Text.Trim        '组合        mylist.CmbCombination1 = GetEnglish(cmbCombin1.Text.Trim)        mylist.CmbCombination2 = GetEnglish(cmbCombin2.Text.Trim)        '定义实体接信息 *result定义的是Objectt类型*        Dim result As Object        Dim facadep As New Facade.CHECKFacade        result = facadep.QueryFacade(mylist)        If result.count = 0 Then            MsgBox("兔子，么有符合记录的信息", , "温馨提示")            Exit Sub        Else            '把泛型信息传到窗体            Call Todgv(result)        End If</span>

在父窗体中我们需要判断文本内容是否为空，感觉一个一个的判断特别费事而且影响效率，所以我用了GroupBOX控件包起来文本框，这样判断一个顶三个 ——是不是很聪明？(*^__^*) 个人感觉这样比较省事：

UI层虚方法：

下面是父窗体中定义的一些虚方法，需要子窗体具体的去实现：

<span style="font-family:SimSun;font-size:18px;">  '获得数据库中字段的名字    Protected Overridable Function GetEnglish(cmbName As String) As String        Return ""    End Function    '获得数据库中表名字    '在这里给了我思路，既然可以把表名传过来    '为什么不能把实体名传过来呢？so定义了一个GetEntity    Protected Overridable Function GetTable() As String        Return ""    End Function    '获得实体的名字    Protected Overridable Function GetEntity() As Object        Return ""    End Function    '定义DataGridView中用来显示的虚方法    '这里需要做相应的改动，把从数据库中查到的消息作为参数传到相应的子窗体中    Protected Overridable Sub Todgv(ByVal mylist As Object)    End Sub</span>

相应的子窗体UI层：

<span style="font-family:SimSun;font-size:18px;"><pre name="code" class="vb"> '重载获得数据库中表的名字的方法    Protected Overrides Function GetTable() As String        Return "Worklogin"    End Function    '这是工作记录查询窗体，相应的把实体传给父窗体    Protected Overrides Function GetEntity() As Object        Return "Entity.OlineWorkerEntity"    End Function    '重载在datagridview中显示的方法    Protected Overrides Sub Todgv(result As Object)        '将参数作为数据源赋给datagridview        Showall.DataSource = result        Showall.Columns(0).Visible = False        Showall.Columns(0).HeaderText = "工作人员"        Showall.Columns(1).HeaderText = "级别"        Showall.Columns(2).HeaderText = "上机日期"        Showall.Columns(3).HeaderText = "上机时间"        Showall.Columns(4).HeaderText = "下机日期"        Showall.Columns(5).HeaderText = "下机时间"        Showall.Columns(6).HeaderText = "工作电脑"        Showall.Columns(7).HeaderText = "现在状态"        Showall.DefaultCellStyle.Font = New Font("楷体", 16)        Showall.AutoResizeColumns()        Showall.ReadOnly = True’只读    End Sub</span>

Facade平淡无奇，所以就不写了，但是注意因为返回的类型不确定，所以要把其定义为Object类型，这也是从“DataTable”到“List（of ）”一个关键所在；

重点的BLL层来了

在这里判断实体类型，相对的通过Factory、IDAL，将其传到D层不同的方法中，之前我是把判断放到D层的，但是感觉不太好，毕竟B层是业务逻辑判断嘛，所以D层还是本本分分的好了：

<span style="font-family:SimSun;font-size:18px;">  Public Function QueryAll(ByVal groupCheck As Entity.CHECKEntity) As Object        '动态将entity送给mylist()        Dim iQuery As IDAL.ICHECK        Dim FctQ As New Factory.CHECKFactory        iQuery = FctQ.SelectOnOff        '学生账单        If groupCheck.GetEntity = "Entity.StudentBillEntity" Then            Dim mylist As List(Of Entity.StudentBillEntity)            mylist = iQuery.IStudentBill(groupCheck)            Return mylist            '值班老师        ElseIf groupCheck.GetEntity = "Entity.OlineWorkerEntity" Then            Dim mylist As List(Of Entity.OnlineWorkerEntity)            mylist = iQuery.IWorkerCord(groupCheck)            Return mylist            '学生信息        ElseIf groupCheck.GetEntity = "Entity.RegisterEntity" Then            Dim mylist As List(Of Entity.RegisterEntity)            mylist = iQuery.IStudentSmg(groupCheck)            Return mylist        Else            '上机记录            Dim mylist As List(Of Entity.OnlineEntity)            mylist = iQuery.IOnline(groupCheck)            Return mylist        End If    End Function</span>

Factory、IDAL层没有什么Change 低调飘过 ~~~~~

D层：

CheckQuery部分的代码 “数据库查询”是下面四个Function都要用到的，难道要敲四遍吗？No way 不仅低效率而且浪费时间(⊙﹏⊙)b,果断放弃，把他单独提出来，用的时候调就好了

<span style="font-family:SimSun;font-size:18px;">  Public Function CheckQuery(groupcheck As Entity.CHECKEntity) As DataTable        Dim sql As String        sql = "select * from " & groupcheck.getTable & " where "        '一个查询条件;        '如果第一个组合条件为空则()        If Trim(groupcheck.CmbCombination1) = "" Then            sql = sql & groupcheck.CmbName1 & groupcheck.CmbRelation1 & "'" & groupcheck.TxtQuery1 & "'"        Else            '两个查询条件            If Trim(groupcheck.CmbCombination2) = "" Then                sql = sql & groupcheck.CmbName1 & groupcheck.CmbRelation1 & "'" & groupcheck.TxtQuery1 & "'" & groupcheck.CmbCombination1 & " " & groupcheck.CmbName2 & groupcheck.CmbRelation2 & "'" & groupcheck.TxtQuery2 & "'"                '三个条件            Else                sql = sql & groupcheck.CmbName1 & groupcheck.CmbRelation1 & "'" & groupcheck.TxtQuery1 & "'" & groupcheck.CmbCombination1 & " " & groupcheck.CmbName2 & groupcheck.CmbRelation2 & "'" & groupcheck.TxtQuery2 & "'" & groupcheck.CmbCombination2 & " " & groupcheck.CmbName3 & groupcheck.CmbRelation3 & "'" & groupcheck.TxtQuery3 & "'"            End If        End If        Dim dt As DataTable        dt = LoginSqlHelper.GetDataTable(sql, CommandType.Text)        Return dt        If dt.Rows.Count = 0 Then            Return Nothing            Exit Function        End If    End Function    '上机记录    Public Function IOnline(groupcheck As CHECKEntity) As List(Of OnlineEntity) Implements IDAL.ICHECK.IOnline        Dim QueryDt As DataTable = CheckQuery(groupcheck)        Dim mylist As List(Of Entity.OnlineEntity)        mylist = DataToList.convertToList(Of Entity.OnlineEntity)(QueryDt)        Return mylist    End Function    '账单记录    Public Function IStudentBill(groupcheck As CHECKEntity) As List(Of StudentBillEntity) Implements IDAL.ICHECK.IStudentBill        Dim QueryDt As DataTable = CheckQuery(groupcheck)        Dim mylist As List(Of Entity.StudentBillEntity)        mylist = DataToList.convertToList(Of Entity.StudentBillEntity)(QueryDt)        Return mylist    End Function    '学生信息    Public Function IStudentSmg(groupcheck As CHECKEntity) As List(Of RegisterEntity) Implements IDAL.ICHECK.IStudentSmg        Dim QueryDt As DataTable = CheckQuery(groupcheck)        Dim mylist As List(Of Entity.RegisterEntity)        mylist = DataToList.convertToList(Of Entity.RegisterEntity)(QueryDt)        Return mylist    End Function    '工作记录    Public Function IWorkerCord(groupcheck As CHECKEntity) As List(Of OnlineWorkerEntity) Implements IDAL.ICHECK.IWorkerCord        Dim QueryDt As DataTable = CheckQuery(groupcheck)        Dim mylist As List(Of Entity.OnlineWorkerEntity)        mylist = DataToList.convertToList(Of Entity.OnlineWorkerEntity)(QueryDt)        Return mylist    End Function</span>

代码复用——COOL吧，这样组合查询基本上就OK了，上图show一下：