综合查询——字符串连接

       首先，在什么情况下使用它在组合查询？大体。有两种情况：首先在单个查询中从不同的表返回类似结构的数据；二是对单个表运行多个查询，按单个查询返回数据。

在这里，我们说的组合查询是指另外一种情况，即要查询的表是固定的。查询条件是不定的而且有多个查询条件。

      从样例来看，

比如。输入对应的查询条件

1、教师不等于“0”

2、机器号等于“yang” 

两个条件是“与”的关系，我们能够非常easy的写出它的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

CreateQueryContent方法

 Public Function CreateQueryContent(student As Login.Model.CmbQueryInfo) As Login.Model.CmbQueryInfo
        Dim tempQueryComposition As New Login.Model.CmbQueryInfo
        Try
            tempQueryComposition.QueryContent1 = student.FileName1 + " " + student.Operator1 + "'" + student.QueryContent1 + "'"
            tempQueryComposition.QueryContent2 = student.FileName2 + " " + student.Operator2 + _
                                              "'" + student.QueryContent2 + "'"
            tempQueryComposition.QueryContent3 = student.FileName3 + " " + student.Operator3 + _
                                            "'" + student.QueryContent3 + "'"

            tempQueryComposition.CompositionRelation1 = student.CompositionRelation1
            tempQueryComposition.CompositionRelation2 = student.CompositionRelation2
            Return tempQueryComposition
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Function

在D层，完毕最后的sql语句的拼接。

    Public Function CreateSqlString(student As Login.Model.CmbQueryInfo, tablename As String) As String
        Dim sql As String

        Try
            '没有组合关系时
            If student.CompositionRelation1.Trim = "NO" Then

                sql = "select * from " + tablename + " Where" + " " + student.FileName1 + student.Operator1 + "'" + student.QueryContent1 + "'"
            Else
                '有一个组合关系时
                sql = "select * from " + tablename + " Where" + " " + student.FileName1 + student.Operator1 + "'" + student.QueryContent1 + "'" + " " + student.CompositionRelation1 + " " + student.FileName2 + student.Operator2 + "'" + student.QueryContent2 + "'"
            End If
            '有两个组合关系时
            If student.CompositionRelation2.Trim <> "NO" Then
                sql = sql + student.CompositionRelation2 + " " + student.FileName3 + student.Operator3 + "'" + student.QueryContent3 + "'"
            End If

            Return sql
        Catch ex As Exception

        End Try
    End Function

      拼接完毕后。能够放到数据库中检验一下，接下来的问题就easy多了。

最后的显示结果：

    组合查询。其本质终究还是查询。用拼接字符串的方法理解起来比較easy。但特别easy出错。一不小心sql语句就

会出错，只是调试起来也挺easy发现错误的。

期待大家与我交流其它的查询方法。

版权声明：本文博主原创文章。博客，未经同意不得转载。