VB.NET & （三层+泛型）实现组合查询

对于组合查询，真的是“费劲苦难”，当然也只是玩笑话，下边谈谈自己拼接字符串的办法吧！当然对于泛型集合的学习，详情《 泛型集合，解决Datatable强耦合 》

     首先我们要明确声明一个实体层，定义组合查询为实体类，将"字段，操作符，组合关系分别设为实体类combinQuery的属性

[vb] view plain copy print ?

1. '字段1 
2.     Private _ComboFileName1 As String 
3.     Public Property ComboFileName1() As String 
4.         Get 
5.             Return _ComboFileName1 
6.         End Get 
7.         Set(value As String) 
8.             _ComboFileName1 = value 
9.         End Set 
10.     End Property 
11.     '字段2 
12.     Private _ComboFileName2 As String 
13.     Public Property ComboFileName2() As String 
14.         Get 
15.             Return _ComboFileName2 
16.         End Get 
17.         Set(value As String) 
18.             _ComboFileName2 = value 
19.         End Set 
20.     End Property 
21. ………… 

 '字段1
    Private _ComboFileName1 As String
    Public Property ComboFileName1() As String
        Get
            Return _ComboFileName1
        End Get
        Set(value As String)
            _ComboFileName1 = value
        End Set
    End Property
    '字段2
    Private _ComboFileName2 As String
    Public Property ComboFileName2() As String
        Get
            Return _ComboFileName2
        End Get
        Set(value As String)
            _ComboFileName2 = value
        End Set
    End Property
…………

    对于U层，我们除了要实现参数赋值，还要将其记录显示在DataGridView中，在显示的这一阶段自己采用了泛型集合，将datatable类型转换成了集合而显示出来

看U层：

[vb] view plain copy print ?

1. Private Sub btnQuery_Click(sender As Object, e As EventArgs) Handles btnQuery.Click 
2.         'combinQuery_m  ，定义一个实体 
3.         Dim combinQuery_m As IList(Of Entity.RegisterEntity)   '定义泛型集合 
4.         Dim combinQuery_bll As New BLL.StusInfoMainBLL    '定义一个B层接口 
5.         Dim combinQuery As New Entity.CombinQueryEntity    '定义一个实体，作为参数 
6.  
7.         Dim table As String = "T_student"    '用到的表，与学生表联系起来 
8.  
9.         Dim arrayControl(2) As Control 
10. '给参数赋值   
11.         With combinQuery 
12.             '将查询条件字段1的值赋给实体combinQuery_m的字段1ComboFileName1属性   
13.             .ComboFileName1 = ComboFileName1.Text.Trim() 
14.             .ComboFileName2 = ComboFileName2.Text.Trim() 
15.             .ComboFileName3 = ComboFileName3.Text.Trim() 
16.             ''将操作符1的值赋给实体combinQuery_m的操作符1ComboSign1属性   
17.             .ComboSign1 = ComboSign1.Text.Trim() 
18.             .ComboSign2 = ComboSign2.Text.Trim() 
19.             .ComboSign3 = ComboSign3.Text.Trim() 
20.             '将查询内容1的值赋给实体combinQuery_m的查询内容1txtInqure1属性   
21.             .txtInqure1 = txtInqure1.Text.Trim() 
22.             .txtInqure2 = txtInqure2.Text.Trim() 
23.             .txtInqure3 = txtInqure3.Text.Trim() 
24.             '将组合关系1的值赋给实体combinQuery_m的组合关系1ComboRelation1属性  
25.             .ComboRelation1 = ComboRelation1.Text.Trim() 
26.             .ComboRelation2 = ComboRelation2.Text.Trim() 
27.         End With 
28. combinQuery_m = combinQuery_bll.StusInfo(table, combinQuery) 
29.  
30.  
31.         DataGridView1.DataSource = combinQuery_m   '查询到的集合 

Private Sub btnQuery_Click(sender As Object, e As EventArgs) Handles btnQuery.Click
        'combinQuery_m  ，定义一个实体
        Dim combinQuery_m As IList(Of Entity.RegisterEntity)   '定义泛型集合
        Dim combinQuery_bll As New BLL.StusInfoMainBLL    '定义一个B层接口
        Dim combinQuery As New Entity.CombinQueryEntity    '定义一个实体，作为参数

        Dim table As String = "T_student"    '用到的表，与学生表联系起来

        Dim arrayControl(2) As Control
 '给参数赋值  
        With combinQuery
            '将查询条件字段1的值赋给实体combinQuery_m的字段1ComboFileName1属性  
            .ComboFileName1 = ComboFileName1.Text.Trim()
            .ComboFileName2 = ComboFileName2.Text.Trim()
            .ComboFileName3 = ComboFileName3.Text.Trim()
            ''将操作符1的值赋给实体combinQuery_m的操作符1ComboSign1属性  
            .ComboSign1 = ComboSign1.Text.Trim()
            .ComboSign2 = ComboSign2.Text.Trim()
            .ComboSign3 = ComboSign3.Text.Trim()
            '将查询内容1的值赋给实体combinQuery_m的查询内容1txtInqure1属性  
            .txtInqure1 = txtInqure1.Text.Trim()
            .txtInqure2 = txtInqure2.Text.Trim()
            .txtInqure3 = txtInqure3.Text.Trim()
            '将组合关系1的值赋给实体combinQuery_m的组合关系1ComboRelation1属性 
            .ComboRelation1 = ComboRelation1.Text.Trim()
            .ComboRelation2 = ComboRelation2.Text.Trim()
        End With
combinQuery_m = combinQuery_bll.StusInfo(table, combinQuery)


        DataGridView1.DataSource = combinQuery_m   '查询到的集合

     以上代码主要还是实现将查询到的集合反馈到DataGridView的过程
我们还要将字符串转化，给字段，操作符，组合关系等分别赋值

将查询字段（1,2,3）对应于”表“的字段（eg: cardNo），操作符对应于关系运算符，组合关系对应于逻辑运算符(or/and)

看部分代码：

[vb] view plain copy print ?

1. '字符串转换 
2.         '给字段1赋值 
3.         Select Case (combinQuery.ComboFileName1) 
4.             Case "卡号" 
5.                 combinQuery.ComboFileName1 = "cardNo" 
6.             Case "学号" 
7.                 combinQuery.ComboFileName1 = "studentNo" 
8.             Case "姓名" 
9.                 combinQuery.ComboFileName1 = "studentName" 
10.             Case "性别" 
11.                 combinQuery.ComboFileName1 = "sex" 
12.             Case "年级" 
13.                 combinQuery.ComboFileName1 = "Grade" 
14.             Case "班级" 
15.                 combinQuery.ComboFileName1 = "sclass" 
16.         End Select 
17. '操作符1 
18.         Select Case combinQuery.ComboSign1 
19.             Case "=" 
20.                 combinQuery.ComboSign1 = "=" 
21.             Case ">" 
22.                 combinQuery.ComboSign1 = ">" 
23.             Case "<" 
24.                 combinQuery.ComboSign3 = "<" 
25.             Case "<>" 
26.                 combinQuery.ComboSign1 = "<>" 
27.         End Select 
28.   '组合关系1 
29.         Select Case combinQuery.ComboRelation1 
30.             Case "或" 
31.                 combinQuery.ComboRelation1 = "or" 
32.             Case "与" 
33.                 combinQuery.ComboRelation1 = "and" 
34.         End Select 

'字符串转换
        '给字段1赋值
        Select Case (combinQuery.ComboFileName1)
            Case "卡号"
                combinQuery.ComboFileName1 = "cardNo"
            Case "学号"
                combinQuery.ComboFileName1 = "studentNo"
            Case "姓名"
                combinQuery.ComboFileName1 = "studentName"
            Case "性别"
                combinQuery.ComboFileName1 = "sex"
            Case "年级"
                combinQuery.ComboFileName1 = "Grade"
            Case "班级"
                combinQuery.ComboFileName1 = "sclass"
        End Select
 '操作符1
        Select Case combinQuery.ComboSign1
            Case "="
                combinQuery.ComboSign1 = "="
            Case ">"
                combinQuery.ComboSign1 = ">"
            Case "<"
                combinQuery.ComboSign3 = "<"
            Case "<>"
                combinQuery.ComboSign1 = "<>"
        End Select
  '组合关系1
        Select Case combinQuery.ComboRelation1
            Case "或"
                combinQuery.ComboRelation1 = "or"
            Case "与"
                combinQuery.ComboRelation1 = "and"
        End Select

     由于在整个系统过程中，我们涉及到了四个组合查询，所以定义了一个字符串类型的Table，实现了代码的复用性。以下则是拼接字符串的方法：

[vb] view plain copy print ?

1. ''' <summary> 
2.    ''' 生成组合查询sql语句-拼接sql字符串 
3.    ''' </summary> 
4.    ''' <param name="table"></param> 
5.    ''' <param name="combinQuery">combinQuery实体</param> 
6.    ''' <returns></returns> 
7.    ''' <remarks></remarks> 
8.    Public Function CombinsqlQuery(ByVal table As String, ByVal combinQuery As Entity.CombinQueryEntity) As String 
9.        '首先，第一个查询条件有效   
10.  
11.        Dim sql As String = "select * from " & table & " where  " & combinQuery.ComboFileName1 & " " & combinQuery.ComboSign1 & " " & combinQuery.txtInqure1.Trim() 
12.        If combinQuery.ComboRelation1 = "" Then    '如果第一个组合关系为空，则第一个查询条件有效   
13.            Return sql 
14.  
15.        Else     '如果第一个组合关系不为空，则前两个查询条件有效   
16.            sql = sql & " " & combinQuery.ComboRelation1 & " " & combinQuery.ComboFileName2 & " " & combinQuery.ComboSign2 & " " & combinQuery.txtInqure2.Trim() 
17.  
18.            If combinQuery.ComboRelation2 = "" Then     '如果第一个组合关系不为空，第二个组合关系为空，则仅仅前两个查询条件有效   
19.                Return sql 
20.            Else       '如果第一二组合关系不为空，则三个查询条件均有效   
21.                sql = sql & " " & combinQuery.ComboRelation2 & " " & combinQuery.ComboFileName3 & " " & combinQuery.ComboSign3 & " " & combinQuery.txtInqure3.Trim() 
22.  
23.                Return sql 
24.            End If 
25.  
26.        End If 
27.  
28.        If combinQuery.ComboRelation1 <> "" And combinQuery.ComboRelation2 = "" Then     '如果第一个组合关系不为空，第二个组合关系为空，则前两个查询条件有效   
29.            sql = sql & " " & combinQuery.ComboRelation1 & " " & combinQuery.ComboFileName2 & " " & combinQuery.ComboSign2 & " " & combinQuery.txtInqure2.Trim() 
30.  
31.        ElseIf combinQuery.ComboRelation1 <> "" And combinQuery.ComboRelation2 <> "" Then '如果第一个组合关系和第二个组合关系均不为空，则三个查询条件有效   
32.            sql = sql 
33.  
34.        End If 
35.    End Function 

 ''' <summary>
    ''' 生成组合查询sql语句-拼接sql字符串
    ''' </summary>
    ''' <param name="table"></param>
    ''' <param name="combinQuery">combinQuery实体</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CombinsqlQuery(ByVal table As String, ByVal combinQuery As Entity.CombinQueryEntity) As String
        '首先，第一个查询条件有效  

        Dim sql As String = "select * from " & table & " where  " & combinQuery.ComboFileName1 & " " & combinQuery.ComboSign1 & " " & combinQuery.txtInqure1.Trim()
        If combinQuery.ComboRelation1 = "" Then    '如果第一个组合关系为空，则第一个查询条件有效  
            Return sql

        Else     '如果第一个组合关系不为空，则前两个查询条件有效  
            sql = sql & " " & combinQuery.ComboRelation1 & " " & combinQuery.ComboFileName2 & " " & combinQuery.ComboSign2 & " " & combinQuery.txtInqure2.Trim()

            If combinQuery.ComboRelation2 = "" Then     '如果第一个组合关系不为空，第二个组合关系为空，则仅仅前两个查询条件有效  
                Return sql
            Else       '如果第一二组合关系不为空，则三个查询条件均有效  
                sql = sql & " " & combinQuery.ComboRelation2 & " " & combinQuery.ComboFileName3 & " " & combinQuery.ComboSign3 & " " & combinQuery.txtInqure3.Trim()

                Return sql
            End If

        End If

        If combinQuery.ComboRelation1 <> "" And combinQuery.ComboRelation2 = "" Then     '如果第一个组合关系不为空，第二个组合关系为空，则前两个查询条件有效  
            sql = sql & " " & combinQuery.ComboRelation1 & " " & combinQuery.ComboFileName2 & " " & combinQuery.ComboSign2 & " " & combinQuery.txtInqure2.Trim()

        ElseIf combinQuery.ComboRelation1 <> "" And combinQuery.ComboRelation2 <> "" Then '如果第一个组合关系和第二个组合关系均不为空，则三个查询条件有效  
            sql = sql

        End If
    End Function

由于是D层实现调用，所以我把这个过程以一个Module形式定义在了D层中，下边看最关键的D层部分：

[vb] view plain copy print ?

1. Private clsSqlhelper As DAL.sqlhelper = New DAL.sqlhelper()   '声明并实例化 
2.     ''' <summary> 
3.     ''' 泛型集合,组合查询 
4.     ''' </summary> 
5.     ''' <param name="table"></param> 
6.     ''' <param name="combinQuery"></param> 
7.     ''' <returns></returns> 
8.     ''' <remarks></remarks> 
9.     Public Function StuInfoQuery(ByVal table As String, ByVal combinQuery As Entity.CombinQueryEntity) As <span style="color:#3333ff;">IList(Of Entity.RegisterEntity</span>) Implements IDAL.IStusInfoMainDAL.StuInfoQuery 
10.  
11.         Dim dt As New DataTable 
12.         Dim myList As IList(Of Entity.RegisterEntity)     '保存转化后的泛型集 
13.         '调用查询语句 
14.         'sql语句。调用Model层combinQuery类的方法CombinQuerySql()方法，返回sql语句   
15.         Dim strsql As String = CombinQueryModule.CombinsqlQuery(table, combinQuery) 
16.         dt = clsSqlhelper.Query(strsql, CommandType.Text)    '执行查询 
17.  
18.         '将dt转换为泛型集合 
19.        myList = EntityHelper.converToList(Of RegisterEntity)(dt) 
20.         Return myList 
21.     End Function 

Private clsSqlhelper As DAL.sqlhelper = New DAL.sqlhelper()   '声明并实例化
    ''' <summary>
    ''' 泛型集合,组合查询
    ''' </summary>
    ''' <param name="table"></param>
    ''' <param name="combinQuery"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function StuInfoQuery(ByVal table As String, ByVal combinQuery As Entity.CombinQueryEntity) As <span style="color:#3333ff;">IList(Of Entity.RegisterEntity</span>) Implements IDAL.IStusInfoMainDAL.StuInfoQuery

        Dim dt As New DataTable
        Dim myList As IList(Of Entity.RegisterEntity)     '保存转化后的泛型集
        '调用查询语句
        'sql语句。调用Model层combinQuery类的方法CombinQuerySql()方法，返回sql语句  
        Dim strsql As String = CombinQueryModule.CombinsqlQuery(table, combinQuery)
        dt = clsSqlhelper.Query(strsql, CommandType.Text)    '执行查询

        '将dt转换为泛型集合
       myList = EntityHelper.converToList(Of RegisterEntity)(dt)
        Return myList
    End Function

整个过程自己尝试定义了工厂类和接口，所以B层直接声明了一个变量，调用由Factory的方法返回来的接口

[vb] view plain copy print ?

1. '声明并实例化Factory为DataAccess类 
2.   Private ReadOnly factory As DataAccess = New DataAccess 
3.   Public Function StusInfo(ByVal data As String, ByVal StusQuery As Entity.CombinQueryEntity) As IList(Of Entity.RegisterEntity) 
4.       '声明并实例化变量InterfaceUser为：调用Factory.CreateUserDAL()所返回来的IUser 
5.       Dim comboQuery As IStusInfoMainDAL = factory.StusInfoMain() 
6.       Dim StusMain As IList(Of Entity.RegisterEntity)   '定义一个泛型集合 
7.       StusMain = comboQuery.StuInfoQuery(data, StusQuery)</span> 
8.  
9.       Return StusMain 
10.   End Function 

  '声明并实例化Factory为DataAccess类
    Private ReadOnly factory As DataAccess = New DataAccess
    Public Function StusInfo(ByVal data As String, ByVal StusQuery As Entity.CombinQueryEntity) As IList(Of Entity.RegisterEntity)
        '声明并实例化变量InterfaceUser为：调用Factory.CreateUserDAL()所返回来的IUser
        Dim comboQuery As IStusInfoMainDAL = factory.StusInfoMain()
        Dim StusMain As IList(Of Entity.RegisterEntity)   '定义一个泛型集合
        StusMain = comboQuery.StuInfoQuery(data, StusQuery)</span>

        Return StusMain
    End Function

     这样组合查询的整个过程就完成了， 整个过程的实现，很好的遵守了三层架构的要求，实现了解耦的目的。当然除了拼接字符串还有使用存储过程实现的方法，这样就无需大量赋值，而且也就不用拼接字符串了，直接将这些全部定义在了存储过程中，然后再存储过程中直接传入参数就好了，虽然自己明白整个过程的实现，但是还是欠缺实践，希望在合作开发的过程中能够熟练对于各种技巧的使用。