歷經(jīng)n多天。組合查詢模板最終做完了，總結(jié)一下這幾天的成果。和大家一起學(xué)習(xí)交流。

先看一下父窗口的關(guān)鍵代碼：

父窗口代碼：

Public Class frmComboQueryProtected Overridable Sub frmComboQuery_Load(sender As Object, e As EventArgs) Handles MyBase.LoadDim ComboxArray(4) As ComboBox '定義控件數(shù)組，獲取組合框ComboxArray(0) = cboOperator1ComboxArray(1) = cboOperator2ComboxArray(2) = cboOperator3ComboxArray(3) = cboRelation1ComboxArray(4) = cboRelation2CommonMethod.SelectIndex(ComboxArray) '設(shè)置combox默認(rèn)顯示第一項(xiàng)Dim ControlArray(6) As Control '定義控件數(shù)組，獲取組合2和組合3的控件ControlArray(0) = cboField2ControlArray(1) = cboOperator2ControlArray(2) = txtContent2ControlArray(3) = cboRelation2ControlArray(4) = cboField3ControlArray(5) = cboOperator3ControlArray(6) = txtContent3CommonMethod.LockControlArray(ControlArray) '調(diào)用共同擁有方法，鎖定控件End SubPrivate Sub btnQuery_Click(sender As Object, e As EventArgs) Handles btnQuery.ClickTryDim ControlArray(2) As Control '定義控件數(shù)組，獲取組合1的控件ControlArray(0) = cboField1ControlArray(1) = cboOperator1ControlArray(2) = txtContent1If CommonMethod.IsEmptyControlArray(ControlArray) = False Then '推斷組合1的控件是否為空Exit SubEnd IfIf cboRelation1.Text.Trim <> "<請(qǐng)選擇>" Then '組合關(guān)系1不為空時(shí)ControlArray(0) = cboField2 '獲取組合2的控件ControlArray(1) = cboOperator2ControlArray(2) = txtContent2If CommonMethod.IsEmptyControlArray(ControlArray) = False Then '推斷組合2的條件是否為空Exit SubEnd IfIf cboRelation2.Text.Trim <> "<請(qǐng)選擇>" Then '組合關(guān)系2不為空時(shí)ControlArray(0) = cboField3 '獲取組合3的控件ControlArray(1) = cboOperator3ControlArray(2) = txtContent3If CommonMethod.IsEmptyControlArray(ControlArray) = False Then '推斷組合3的條件是否為空Exit SubEnd IfEnd IfEnd IfDim eComboQuery1 As New Entity.ComboQueryEntity '定義組合查詢實(shí)體，將條件傳入實(shí)體eComboQuery1.dbName = GetdbName()eComboQuery1.Field1 = cboField1.Text.TrimeComboQuery1.Field2 = cboField2.Text.TrimeComboQuery1.Field3 = cboField3.Text.TrimeComboQuery1.Operator1 = cboOperator1.Text.TrimeComboQuery1.Operator2 = cboOperator2.Text.TrimeComboQuery1.Operator3 = cboOperator3.Text.TrimeComboQuery1.Content1 = txtContent1.Text.TrimeComboQuery1.Content2 = txtContent2.Text.TrimeComboQuery1.Content3 = txtContent3.Text.TrimeComboQuery1.Relation1 = cboRelation1.Text.TrimeComboQuery1.Relation2 = cboRelation2.Text.TrimDim dtComboQuery As New DataTableDim mgr As New BLL.ComboQueryBLLdtComboQuery = mgr.ComboQuery(eComboQuery1)dgvRecord.DataSource = dtComboQueryCatch ex As ExceptionMessageBox.Show(ex.Message.ToString())dgvRecord.DataSource = NothingEnd TryEnd SubProtected Overridable Function GetdbName() As StringReturn ""End FunctionPrivate Sub cboRelation1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboRelation1.SelectedIndexChangedDim ControlArray(3) As Control '定義控件數(shù)組。獲取組合2的控件和組合關(guān)系2的控件，ControlArray(0) = cboField2ControlArray(1) = cboOperator2ControlArray(2) = txtContent2ControlArray(3) = cboRelation2If cboRelation1.SelectedIndex = 0 Then '假設(shè)組合關(guān)系1為空，清空組合2和組合關(guān)系2CommonMethod.ClearControlArray(ControlArray)CommonMethod.LockControlArray(ControlArray)ElseCommonMethod.UnLockControlArray(ControlArray) '否則。解鎖組合2和組合關(guān)系2End IfEnd SubPrivate Sub cboRelation2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboRelation2.SelectedIndexChangedDim ControlArray(2) As Control '定義控件數(shù)組，獲取組合3的控件ControlArray(0) = cboField3ControlArray(1) = cboOperator3ControlArray(2) = txtContent3If cboRelation2.SelectedIndex = 0 Then '假設(shè)組合關(guān)系2為空。清空組合2。鎖定組合3CommonMethod.ClearControlArray(ControlArray)CommonMethod.LockControlArray(ControlArray)ElseCommonMethod.UnLockControlArray(ControlArray) '否則，解鎖組合3End IfEnd Sub End Class

文中調(diào)用的公共方法上篇博客已寫(xiě)，這里不再反復(fù)。

如今以查詢操作員工作記錄為例：

Public Class frmWorkLogProtected Overrides Sub frmComboQuery_Load(sender As Object, e As EventArgs)MyBase.frmComboQuery_Load(sender, e)Dim ComboxArray(2) As ComboBox '定義控件數(shù)組，獲取組合框ComboxArray(0) = cboField1ComboxArray(1) = cboField2ComboxArray(2) = cboField3CommonMethod.SelectIndex(ComboxArray) '設(shè)置combox默認(rèn)顯示第一項(xiàng)End SubProtected Overrides Function GetdbName() As StringReturn "WorkLog"End Function End Class

說(shuō)明：1.設(shè)置默認(rèn)第一項(xiàng)在父窗口加入了一部分，這里是字段的那部分，由于字段是在子窗口加入，所以假設(shè)這段代碼也放父窗口會(huì)報(bào)錯(cuò)。

2.這里通過(guò)傳遞一個(gè)字符串來(lái)通知D層確定哪一個(gè)數(shù)據(jù)表，這里是我感覺(jué)不好的地方，可是臨時(shí)沒(méi)有想到其它方法。僅僅能先這么寫(xiě)。
B層代碼：

Public Class ComboQueryBLLDim factory As New Factory.DataAccessPublic Function ComboQuery(ByVal cboworklog As Entity.ComboQueryEntity) As DataTableDim iComboQuery As IDAL.IComboQueryiComboQuery = factory.CreateComboQueryDim dtComboQuery As New DataTabledtComboQuery = iComboQuery.ComboQuery(cboworklog)If dtComboQuery.Rows.Count = 0 ThenThrow New Exception("沒(méi)有記錄")ElseReturn dtComboQueryEnd IfEnd Function End Class

D層代碼：

Imports System.Data.SqlClient Public Class SqlServerComboQueryDAL : Implements IDAL.IComboQueryDim sqlhelper As New SqlHelperPublic Function ComboQuery1(cboworklog As Entity.ComboQueryEntity) As DataTable Implements IDAL.IComboQuery.ComboQueryDim ecboworklog As New Entity.ComboQueryEntitySelect Case cboworklog.dbNameCase "WorkLog"ecboworklog = Method.SwitchWorklogField(cboworklog) '調(diào)用方法，轉(zhuǎn)換字段，以匹配數(shù)據(jù)庫(kù)Case "StudentAccount"ecboworklog = Method.SwitchStudentAccount(cboworklog)End SelectDim 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 DataTabledtComboQuery = sqlhelper.Query("sp_ComboQuery", CommandType.StoredProcedure, sqlParameter)Return dtComboQueryEnd Function End Class

D層轉(zhuǎn)換字段的方法：

Function SwitchWorklogField(ByVal eworklog As Entity.ComboQueryEntity)Dim strField(2) As String '字符串?dāng)?shù)組。暫時(shí)存放字段值Dim Field(2) As StringField(0) = eworklog.Field1Field(1) = eworklog.Field2Field(2) = eworklog.Field3For i = 0 To 2 '通過(guò)一個(gè)循環(huán)對(duì)字段進(jìn)行匹配Select Case Field(i)Case "職工ID"strField(i) = "UserID"Case "登陸日期"strField(i) = "LoginDate"Case "登陸時(shí)間"strField(i) = "LoginTime"Case "注銷日期"strField(i) = "LogOffDate"Case "注銷時(shí)間"strField(i) = "LogOffTime"Case "電腦名"strField(i) = "ComputerName"Case ElsestrField(i) = ""End SelectField(i) = strField(i)Nexteworklog.Field1 = Field(0) '將字段值進(jìn)行匹配。并賦值給組合查詢實(shí)體eworklog.Field2 = Field(1)eworklog.Field3 = Field(2)Dim strRelation(1) As String '定義字符串?dāng)?shù)組，暫時(shí)存在關(guān)系值Dim Relation(1) As StringRelation(0) = eworklog.Relation1Relation(1) = eworklog.Relation2For i = 0 To 1 '循環(huán)匹配字段Select Case Relation(i)Case "或"strRelation(i) = "OR"Case "且"strRelation(i) = "AND"Case ElsestrRelation(i) = ""End SelectRelation(i) = strRelation(i)Nexteworklog.Relation1 = Relation(0) '組合查詢實(shí)體賦值eworklog.Relation2 = Relation(1)eworklog.dbName = "T_WorkLog"Return eworklog '返回實(shí)體End Function

存儲(chǔ)過(guò)程代碼：

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 BEGINDECLARE @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

效果例如以下：

題外話：

??? 由于系統(tǒng)多次用到組合查詢，并且各個(gè)窗口很類似，所以最初僅僅是想到用窗口的繼承，免得做反復(fù)的窗口。后來(lái)父窗口做好之后，發(fā)現(xiàn)那些推斷是否為空、清空等代碼能夠?qū)懙礁复翱凇Ｈ缓笞哟翱趯?xiě)關(guān)于查詢的代碼。

??? 緊接著就出現(xiàn)了一個(gè)問(wèn)題，使用窗口繼承。對(duì)于同一個(gè)事件而言，程序會(huì)先運(yùn)行父窗口中的代碼，后運(yùn)行子窗口中的代碼。在我的程序里，當(dāng)運(yùn)行button的click事件時(shí)，父窗口會(huì)先推斷組合框是否為空。假設(shè)推斷出為空，給出了提示。當(dāng)點(diǎn)完確定后，程序并沒(méi)有像想象中那樣停下來(lái)讓你選擇組合框，而是接著運(yùn)行子窗口的查詢。

??? 后來(lái)僅僅好請(qǐng)教師父和師哥，得到一個(gè)解決的方法。就是聲明一個(gè)全局變量，由它通知子窗口是否運(yùn)行，同一時(shí)候師哥指出，事實(shí)上子窗口的代碼也能夠放在父窗口中，我自己也認(rèn)為聲明全局變量感覺(jué)不好。所以就演化到如今的樣子了。后來(lái)才知道，不知不覺(jué)還用了一個(gè)設(shè)計(jì)模式。模板方法模式。

??? 只是這個(gè)模式依舊須要子窗口去傳遞一個(gè)參數(shù)，讓D層去推斷是用的哪一個(gè)數(shù)據(jù)庫(kù)，感覺(jué)不好，可是也沒(méi)有想出好的辦法。假設(shè)大家有什么好的建議。望不吝奉獻(xiàn)。

?

轉(zhuǎn)載于:https://www.cnblogs.com/blfshiye/p/5379484.html

總結(jié)

以上是生活随笔為你收集整理的组合查询（机房重构知识点总结）的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。