機房收費系統之組合查詢:基底字元串拼接方法和預存程序實現

組合查詢是費了不少功夫,查了不少資料,主要的兩種思路是:字串拼接和結合預存程序的使用.

 

第一種方法是通過最簡單的方法來實現的,三層架構是為瞭解耦,對於組合查詢就有點不太好處理,下面是我處理的過程,

程式介面:

U層(介面層):行動數據,傳遞給實體

 ''' <summary>    ''' 擷取資料的值後傳遞給實體,並尋找後資料顯示給使用者    ''' </summary>    ''' <param name="sender"></param>    ''' <param name="e"></param>    ''' <remarks></remarks>    Private Sub cmdQuery_Click(sender As Object, e As EventArgs) Handles cmdQuery.Click        Dim enselectString As New Entity.SelectString        Dim table As New DataTable        '代替變數        Dim f1 As String        Dim f2 As String        Dim f3 As String        '選擇後轉換成資料庫識別的欄位        Select Case cboFieldA.Text            Case "學號"                f1 = "studentId"            Case "姓名"                f1 = "studentName"            Case "性別"                f1 = "sex"            Case "系別"                f1 = "department"            Case "年級"                f1 = "grade"            Case "班級"                f1 = "stuClass"            Case "備忘"                f1 = "studentStyle"            Case "卡號"                f1 = "cardId"            Case Else                f1 = ""        End Select        Select Case cboFieldB.Text            Case "學號"                f2 = "studentId"            Case "姓名"                f2 = "studentName"            Case "性別"                f2 = "sex"            Case "系別"                f2 = "department"            Case "年級"                f2 = "grade"            Case "班級"                f2 = "stuClass"            Case "備忘"                f2 = "studentStyle"            Case "卡號"                f2 = "cardId"            Case Else                f2 = ""        End Select        Dim f4 As String        Select Case cboRelationA.Text            Case "或"                f4 = "or"            Case "與"                f4 = "and"            Case Else                f4 = ""        End Select        Select Case cboFieldC.Text            Case "學號"                f3 = "studentId"            Case "姓名"                f3 = "studentName"            Case "性別"                f3 = "sex"            Case "系別"                f3 = "department"            Case "年級"                f3 = "grade"            Case "班級"                f3 = "stuClass"            Case "備忘"                f3 = "studentStyle"            Case "卡號"                f3 = "cardId"            Case Else                f3 = ""        End Select        '將參數傳遞給實體        enselectString.Work_cboFieldA = f1        enselectString.Work_cboFieldB = f2        enselectString.Work_cboFieldC = f3        enselectString.Work_cboOperatorA = cboOperatorA.Text        enselectString.Work_cboOperatorB = cboOperatorB.Text        enselectString.Work_cboOperatorC = cboOperatorC.Text        enselectString.Work_cboRelationA = f4        enselectString.Work_cboRelationB = cboRelationB.Text        enselectString.Work_txtConditionA = txtConditionA.Text        enselectString.Work_txtConditionB = txtConditionB.Text        enselectString.Work_txtConditionC = txtConditionC.Text        '將查詢結果顯示出來給使用者        Dim InquireStudentInfoFA As New Facade.StudentManageFA        table = InquireStudentInfoFA.InquireStudentInfoBll(enselectString)        DGW.DataSource = table '資料的綁定        '顯示成自己需要的資料        DGW.Columns(0).HeaderText = "學號"        DGW.Columns(1).HeaderText = "姓名"        DGW.Columns(2).HeaderText = "卡號"        DGW.Columns(3).HeaderText = "餘額"        DGW.Columns(4).HeaderText = "系別"        DGW.Columns(5).HeaderText = "年級"        DGW.Columns(6).HeaderText = "班級"        DGW.Columns(7).HeaderText = "性別"        DGW.Columns(8).HeaderText = "狀態"        DGW.Columns(9).HeaderText = "備忘"

 

實體層中的方法

    ''' <summary>    ''' 產生字串    ''' </summary>    ''' <param name="table">訪問不同的資料庫表的名稱</param>    ''' <param name="ss"></param>    ''' <returns>字串</returns>    ''' <remarks></remarks>    Public Function SelectString(ByVal table As String, ByVal ss As SelectString) As String        Dim sql As String        Try            sql = "select * from " & table & " where " & ss.cboFieldA & " " & ss.Work_cboOperatorA & " '" & ss.Work_txtConditionA & "'"            If ss.Work_cboRelationA = "" Then                Return sql            Else                sql = sql & " " & ss.Work_cboRelationA & " " & ss.Work_cboFieldB & " " & ss.Work_cboOperatorB & " '" & ss.Work_txtConditionB & "'"                If ss.Work_cboRelationB = "" Then                    Return sql                Else                    sql = sql & " " & ss.Work_cboRelationB & " " & ss.Work_cboFieldC & " " & ss.Work_cboOperatorC & " '" & ss.Work_txtConditionC & "'"                    Return sql                End If            End If        Catch ex As Exception                   '錯誤處理              sql = ""            Return sql        End Try

B層:邏輯的整理

   ''' <summary>    ''' 查詢學生的資訊    ''' </summary>    ''' <param name="selectstring">實體選擇字串</param>    ''' <returns>返回表</returns>    ''' <remarks></remarks>    Public Function InquireStudentInfo(ByVal selectstring As Entity.SelectString) As DataTable        Dim factory As New DBFactory '執行個體化工廠        Dim iStudent As IStudent '定義介面        iStudent = factory.CreateStudentInfo '建立介面        Dim dt As New DataTable        Try '通過視圖來擷取自己需要的資訊            dt = iStudent.GetInfoByString("V_QueryStudentInfo", selectstring)            Return dt        Catch ex As Exception            Return dt        End Try    End Function

D:通過字串來擷取資訊

 ''' <summary>    ''' 通過字串來擷取資訊    ''' </summary>    ''' <param name="table">資料庫表</param>    ''' <param name="selectstring">實體選擇字串</param>    ''' <returns><返回表/returns>    ''' <remarks></remarks>    Public Function GetInfoByString(table As String, selectstring As SelectString) As DataTable Implements IStudent.GetInfoByString        Dim dt As New DataTable '聲明變數        Dim selectstring1 As New Entity.SelectString        Dim sql As String '定義字串        sql = selectstring.SelectString(table, selectstring)        Try            '調用SqlHelper執行            dt = SqlHelper.DBHelper.GetDataTable(sql, CommandType.Text)            Return dt        Catch ex As Exception            Return dt        End Try    End Function

       selectstring方法的功能是根據傳入的參數合成SQL語句，包括表的名稱，這樣做可以對不同功能進行組合查詢，但是只限於該功能所查詢的資料在一張表中或是在同一視圖中。

       通過這樣處理的組合查詢，很好的遵守了三層架構的要求，實現瞭解耦的目的。

 

第二種方法是:不再使用上述的那樣的字串拼接,畢竟這樣的方法容易處錯誤,下面我請教師傅學習後思考:是怎麼實現不論怎樣填寫條件，只傳送三個參數，而且沒有select判斷，需要設定我的Relation預設是and關係，所以只有Relation用了一個if語句來判斷是不是或關係，在只有一個條件時，那麼就將三個參數賦相同的值，也就是對三個相同的條件查詢and關係。這樣做的好處就是避免了使用判斷語句來進行拼加字串，在預存程序中直接傳入參數即可。

ALTER PROCEDURE [dbo].[PROC_Query]@cboFieldA varchar(10),@cboOperatorA varchar(10),@txtConditionA varchar(10),@cboRelationA varchar(10),@cboFieldB varchar(10),@cboOperatorB varchar(10),@txtConditionB varchar(10),@cboRelationB varchar(10),@cboFieldC varchar(10),@cboOperatorC varchar(10),@txtConditionC varchar(10)--表名稱或視圖名稱ASdeclare @TempSql varchar(500)--臨時存放sql語句--CHAR(32)是空格,CHAR(39)單引號BEGINset @TempSql='select * from V_StudentInfo 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

 

這種方法自己還是需要後期的多多實踐,要求是多這樣的組合查詢有很強的邏輯分析,希望在合作開發的時候加深對此方法的理解.