组合查询以及拼接字符串

首先，什么情况下要用到组合查询呢？总的来说，有两种情况：一是在单个查询中从不同的表返回类似结构的数据；二是对单个表执行多个查询，按单个查询返回数据。在这里，我们说的组合查询是指第二种情况，即要查询的表是固定的，查询条件是不定的并且有多个查询条件。

从例子来看，

例如，输入相应的查询条件

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

本栏目更多精彩内容：http://www.bianceng.cn/webkf/aspx/

转载请注明：http://www.shhjfk.com/zytd/zytd/24.html