组合查询(机房重构知识点总结)

时间:2022-09-16 11:18:09

历经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 Sub
End 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 Function
End 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 Function
End Class

D层代码:

Imports System.Data.SqlClient
Public 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 Function
End 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 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)

AS
BEGIN
DECLARE @sqlText varchar(200)
SET @sqlText='SELECT * FROM '+@dbName+' WHERE ' +@field1 +@operator1+char(39) + @content1 + char(39)
if @relation1<>''
BEGIN
SET @sqlText=@sqlText+@relation1+CHAR(32)+@field2+@operator2+CHAR(39)+@content2+CHAR(39)
if @relation2<>''
BEGIN
SET @sqlText=@sqlText+@relation2+CHAR(32)+@field3+@operator3+CHAR(39)+@content3+CHAR(39)
END
END
EXECUTE(@sqlText)
END


效果如下:

组合查询(机房重构知识点总结)

题外话:

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

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

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

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