組合查詢以及拼接字符串

首先，什麼情況下要用到組合查詢呢？總的來說，有兩種情況：一是在單個查詢中從不同的表返回類似結構的數據；二是對單個表執行多個查詢，按單個查詢返回數據。在這裡，我們說的組合查詢是指第二種情況，即要查詢的表是固定的，查詢條件是不定的並且有多個查詢條件。

從例子來看，

例如，輸入相應的查詢條件

1、教師不等於“0”

2、機器號等於“yang”

兩個條件是“與”的關系，我們可以很容易的寫出它的sql語句

select * from T_Worklog_Info  where id <> '0' and computer='yang'

所以接下來要做的核心就是拼出這條語句。

在U層，我們把輸入的查詢條件的信息賦給實體，然後把實體傳入B層。

Dim student As New Login.Model.CmbQueryInfo  
       Dim Bquery As New Login.BLL.cmbQueryforBLL  
       '定義控件基類  
       Dim controlArray(2) As System.Windows.Forms.Control  
       Dim table As New DataTable  
       Dim i As Integer
      
       controlArray(0) = cmbWord1  
       controlArray(1) = cmbOperator1  
       controlArray(2) = txtContent1  
       '字段名  
       student.FileName1 = cmbWord1.Text  
       student.FileName2 = cmbWord2.Text  
       student.FileName3 = cmbWord3.Text  
       '操作符  
       student.Operator1 = cmbOperator1.Text  
       student.Operator2 = cmbOperator2.Text  
       student.Operator3 = cmbOperator3.Text  
       '查詢內容  
       student.QueryContent1 = txtContent1.Text  
       student.QueryContent2 = txtContent2.Text  
       student.QueryContent3 = txtContent3.Text  
       '組合關系  
       student.CompositionRelation1 = cmbRelation1.Text  
       student.CompositionRelation2 = cmbRelation2.Text  
      
       DataGridView1.Rows.Clear()  
       '查詢  
       table = Bquery.cmbQuery(student, "T_Worklog_Info")

在B層，接收實體之後，開始拼接字符串。

首先，會用到兩個方法。

ModifyFields方法主要是把“卡號”等中文字符轉換為數據庫中的字段“cardno”。

Public Function ModifyFields(student As Login.Model.CmbQueryInfo) As Login.Model.CmbQueryInfo  
    Dim str As String
    Select Case student.FileName1  
        Case "卡號"
            str = "cardno"
        Case "學號"
            str = "studentno"
        Case "姓名"
            str = "studentname"
        Case "性別"
            str = "sex"
        Case "系別"
            str = "apartment"
        Case "年級"
            str = "grade"
        Case "班級"
            str = "class"
        Case "上機日期"
            str = "ondate"
        Case "上機時間"
            str = "ontime"
        Case "下機日期"
            str = "offdate"
        Case "下機時間"
            str = "offtime"
        Case "機房號"
            str = "computer"
        Case "機器號"
            str = "computer"
        Case "登錄日期"
            str = "LoginDate"
        Case "登錄時間"
            str = "LoginTime"
        Case "注銷日期"
            str = "LogoutDate"
        Case "注銷時間"
            str = "LogoutTime"
        Case "教師"
            str = "id"
        Case Else
            str = ""
    End Select
    student.FileName1 = str  
      
    'student.FileName2, student.FileName3類似  
      
    Select Case student.CompositionRelation1  
        Case "與"
            str = "and"
        Case "或"
            str = "or"
        Case Else
            str = "NO"
    End Select
    student.CompositionRelation1 = str  
    Select Case student.CompositionRelation2  
        Case "與"
            str = "and"
        Case "或"
            str = "or"
        Case Else
            str = "NO"
    End Select
    student.CompositionRelation2 = str  
    Return student  
End Function

本欄目