组合查询（机房重构知识点总结）

历经n多天，组合查询模板终于做完了，总结一下这几天的成果，和大家一起学习交流。

先看一下父窗体的关键代码：

父窗体代码：

Public Class frmComboQuery    Protected Overridable Sub frmComboQuery_Load(sender As Object, e As EventArgs) Handles MyBase.Load        Dim ComboxArray(4) As ComboBox            '定义控件数组，获取组合框        ComboxArray(0) = cboOperator1        ComboxArray(1) = cboOperator2        ComboxArray(2) = cboOperator3        ComboxArray(3) = cboRelation1        ComboxArray(4) = cboRelation2        CommonMethod.SelectIndex(ComboxArray)           '设置combox默认显示第一项        Dim ControlArray(6) As Control            '定义控件数组，获取组合2和组合3的控件        ControlArray(0) = cboField2        ControlArray(1) = cboOperator2        ControlArray(2) = txtContent2        ControlArray(3) = cboRelation2        ControlArray(4) = cboField3        ControlArray(5) = cboOperator3        ControlArray(6) = txtContent3        CommonMethod.LockControlArray(ControlArray)        '调用共有方法，锁定控件    End Sub    Private Sub btnQuery_Click(sender As Object, e As EventArgs) Handles btnQuery.Click        Try            Dim ControlArray(2) As Control             '定义控件数组，获取组合1的控件            ControlArray(0) = cboField1            ControlArray(1) = cboOperator1            ControlArray(2) = txtContent1            If CommonMethod.IsEmptyControlArray(ControlArray) = False Then           '判断组合1的控件是否为空                Exit Sub            End If            If cboRelation1.Text.Trim <> "<请选择>" Then                '组合关系1不为空时                ControlArray(0) = cboField2                   '获取组合2的控件                ControlArray(1) = cboOperator2                ControlArray(2) = txtContent2                If CommonMethod.IsEmptyControlArray(ControlArray) = False Then      '判断组合2的条件是否为空                    Exit Sub                End If                If cboRelation2.Text.Trim <> "<请选择>" Then                 '组合关系2不为空时                    ControlArray(0) = cboField3                     '获取组合3的控件                    ControlArray(1) = cboOperator3                    ControlArray(2) = txtContent3                    If CommonMethod.IsEmptyControlArray(ControlArray) = False Then     '判断组合3的条件是否为空                        Exit Sub                    End If                End If            End If            Dim eComboQuery1 As New Entity.ComboQueryEntity       '定义组合查询实体，将条件传入实体            eComboQuery1.dbName = GetdbName()            eComboQuery1.Field1 = cboField1.Text.Trim            eComboQuery1.Field2 = cboField2.Text.Trim            eComboQuery1.Field3 = cboField3.Text.Trim            eComboQuery1.Operator1 = cboOperator1.Text.Trim            eComboQuery1.Operator2 = cboOperator2.Text.Trim            eComboQuery1.Operator3 = cboOperator3.Text.Trim            eComboQuery1.Content1 = txtContent1.Text.Trim            eComboQuery1.Content2 = txtContent2.Text.Trim            eComboQuery1.Content3 = txtContent3.Text.Trim            eComboQuery1.Relation1 = cboRelation1.Text.Trim            eComboQuery1.Relation2 = cboRelation2.Text.Trim            Dim dtComboQuery As New DataTable            Dim mgr As New BLL.ComboQueryBLL            dtComboQuery = mgr.ComboQuery(eComboQuery1)            dgvRecord.DataSource = dtComboQuery        Catch ex As Exception            MessageBox.Show(ex.Message.ToString())            dgvRecord.DataSource = Nothing        End Try    End Sub    Protected Overridable Function GetdbName() As String        Return ""    End Function    Private Sub cboRelation1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboRelation1.SelectedIndexChanged        Dim ControlArray(3) As Control                   '定义控件数组，获取组合2的控件和组合关系2的控件，        ControlArray(0) = cboField2        ControlArray(1) = cboOperator2        ControlArray(2) = txtContent2        ControlArray(3) = cboRelation2        If cboRelation1.SelectedIndex = 0 Then               '如果组合关系1为空，清空组合2和组合关系2            CommonMethod.ClearControlArray(ControlArray)            CommonMethod.LockControlArray(ControlArray)        Else            CommonMethod.UnLockControlArray(ControlArray)                 '否则，解锁组合2和组合关系2        End If    End Sub    Private Sub cboRelation2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboRelation2.SelectedIndexChanged        Dim ControlArray(2) As Control                   '定义控件数组，获取组合3的控件        ControlArray(0) = cboField3        ControlArray(1) = cboOperator3        ControlArray(2) = txtContent3        If cboRelation2.SelectedIndex = 0 Then               '如果组合关系2为空，清空组合2，锁定组合3            CommonMethod.ClearControlArray(ControlArray)            CommonMethod.LockControlArray(ControlArray)        Else            CommonMethod.UnLockControlArray(ControlArray)                '否则，解锁组合3        End If    End SubEnd Class

文中调用的公共方法上篇博客已写，这里不再重复。

现在以查询操作员工作记录为例：

Public Class frmWorkLog    Protected Overrides Sub frmComboQuery_Load(sender As Object, e As EventArgs)        MyBase.frmComboQuery_Load(sender, e)        Dim ComboxArray(2) As ComboBox           '定义控件数组，获取组合框        ComboxArray(0) = cboField1        ComboxArray(1) = cboField2        ComboxArray(2) = cboField3        CommonMethod.SelectIndex(ComboxArray)           '设置combox默认显示第一项    End Sub    Protected Overrides Function GetdbName() As String        Return "WorkLog"    End FunctionEnd Class

说明：1.设置默认第一项在父窗体添加了一部分，这里是字段的那部分，因为字段是在子窗体添加，所以如果这段代码也放父窗体会报错。2.这里通过传递一个字符串来通知D层确定哪一个数据表，这里是我感觉不好的地方，但是暂时没有想到其他方法，只能先这么写。
B层代码：

Public Class ComboQueryBLL    Dim factory As New Factory.DataAccess    Public Function ComboQuery(ByVal cboworklog As Entity.ComboQueryEntity) As DataTable        Dim iComboQuery As IDAL.IComboQuery        iComboQuery = factory.CreateComboQuery        Dim dtComboQuery As New DataTable        dtComboQuery = iComboQuery.ComboQuery(cboworklog)        If dtComboQuery.Rows.Count = 0 Then            Throw New Exception("没有记录")        Else            Return dtComboQuery        End If    End FunctionEnd Class

D层代码：

Imports System.Data.SqlClientPublic Class SqlServerComboQueryDAL : Implements IDAL.IComboQuery    Dim sqlhelper As New SqlHelper    Public Function ComboQuery1(cboworklog As Entity.ComboQueryEntity) As DataTable Implements IDAL.IComboQuery.ComboQuery        Dim ecboworklog As New Entity.ComboQueryEntity        Select Case cboworklog.dbName            Case "WorkLog"                ecboworklog = Method.SwitchWorklogField(cboworklog)            '调用方法，转换字段，以匹配数据库            Case "StudentAccount"                ecboworklog = Method.SwitchStudentAccount(cboworklog)        End Select        Dim sqlParameter As SqlParameter()        sqlParameter = New SqlParameter() {        New SqlParameter("@dbName", ecboworklog.dbName),        New SqlParameter("@Field1", ecboworklog.Field1),        New SqlParameter("@Field2", ecboworklog.Field2),        New SqlParameter("@Field3", ecboworklog.Field3),        New SqlParameter("@Operator1", ecboworklog.Operator1),        New SqlParameter("@Operator2", ecboworklog.Operator2),        New SqlParameter("@Operator3", ecboworklog.Operator3),        New SqlParameter("@Content1", ecboworklog.Content1),        New SqlParameter("@Content2", ecboworklog.Content2),        New SqlParameter("@Content3", ecboworklog.Content3),        New SqlParameter("@Relation1", ecboworklog.Relation1),        New SqlParameter("@Relation2", ecboworklog.Relation2)}        Dim dtComboQuery As New DataTable        dtComboQuery = sqlhelper.Query("sp_ComboQuery", CommandType.StoredProcedure, sqlParameter)        Return dtComboQuery    End FunctionEnd Class

D层转换字段的方法：

    Function SwitchWorklogField(ByVal eworklog As Entity.ComboQueryEntity)        Dim strField(2) As String             '字符串数组，临时存放字段值        Dim Field(2) As String        Field(0) = eworklog.Field1        Field(1) = eworklog.Field2        Field(2) = eworklog.Field3        For i = 0 To 2                              '通过一个循环对字段进行匹配            Select Case Field(i)                Case "职工ID"                    strField(i) = "UserID"                Case "登陆日期"                    strField(i) = "LoginDate"                Case "登陆时间"                    strField(i) = "LoginTime"                Case "注销日期"                    strField(i) = "LogOffDate"                Case "注销时间"                    strField(i) = "LogOffTime"                Case "电脑名"                    strField(i) = "ComputerName"                Case Else                    strField(i) = ""            End Select            Field(i) = strField(i)        Next        eworklog.Field1 = Field(0)                     '将字段值进行匹配，并赋值给组合查询实体        eworklog.Field2 = Field(1)        eworklog.Field3 = Field(2)        Dim strRelation(1) As String                    '定义字符串数组，临时存在关系值        Dim Relation(1) As String        Relation(0) = eworklog.Relation1        Relation(1) = eworklog.Relation2        For i = 0 To 1                                            '循环匹配字段            Select Case Relation(i)                Case "或"                    strRelation(i) = "OR"                Case "且"                    strRelation(i) = "AND"                Case Else                    strRelation(i) = ""            End Select            Relation(i) = strRelation(i)        Next        eworklog.Relation1 = Relation(0)            '组合查询实体赋值        eworklog.Relation2 = Relation(1)        eworklog.dbName = "T_WorkLog"        Return eworklog                           '返回实体    End Function

存储过程代码：

USE [ChargeSystem]GO/****** Object:  StoredProcedure [dbo].[sp_ComboQuery]    Script Date: 2014/6/22 16:40:59 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:刘晓春-- Create date: 2014年6月17日-- Description:组合查询-- =============================================CREATE PROCEDURE [dbo].[sp_ComboQuery]@dbName varchar(20),@field1 varchar(20),@operator1 varchar(20),@content1 varchar(20),@relation1 varchar(10),@field2 varchar(20),@operator2 varchar(20),@content2 varchar(20),@relation2 varchar(20),@field3 varchar(20),@operator3 varchar(20),@content3 varchar(20)ASBEGINDECLARE @sqlText varchar(200)SET @sqlText='SELECT * FROM '+@dbName+' WHERE ' +@field1 +@operator1+char(39) + @content1 + char(39)if @relation1<>''BEGINSET @sqlText=@sqlText+@relation1+CHAR(32)+@field2+@operator2+CHAR(39)+@content2+CHAR(39)if @relation2<>''BEGINSET @sqlText=@sqlText+@relation2+CHAR(32)+@field3+@operator3+CHAR(39)+@content3+CHAR(39)ENDENDEXECUTE(@sqlText)END

效果如下：

题外话：

    因为系统多次用到组合查询，而且各个窗体非常类似，所以最初只是想到用窗体的继承，免得做重复的窗体。后来父窗体做好之后，发现那些判断是否为空、清空等代码可以写到父窗体，然后子窗体写关于查询的代码。

    紧接着就出现了一个问题，使用窗体继承，对于同一个事件而言，程序会先执行父窗体中的代码，后执行子窗体中的代码。在我的程序里，当执行按钮的click事件时，父窗体会先判断组合框是否为空，如果判断出为空，给出了提示，当点完确定后，程序并没有像想象中那样停下来让你选择组合框，而是接着执行子窗体的查询。

    后来只好请教师父和师哥，得到一个解决办法，就是声明一个全局变量，由它通知子窗体是否执行，同时师哥指出，其实子窗体的代码也可以放在父窗体中，我自己也觉得声明全局变量感觉不好，所以就演化到现在的样子了，后来才知道，不知不觉还用了一个设计模式，模板方法模式。

    不过这个模式依然需要子窗体去传递一个参数，让D层去判断是用的哪一个数据库，感觉不好，但是也没有想出好的办法，如果大家有什么好的建议，望不吝奉献。