VB.NET & （三层+泛型）实现组合查询
对于组合查询，真的是“费劲苦难”，当然也只是玩笑话，下边谈谈自己拼接字符串的办法吧！当然对于泛型集合的学习，详情《泛型集合，解决Datatable强耦合》
首先我们要明确声明一个实体层，定义组合查询为实体类，将"字段，操作符，组合关系分别设为实体类combinQuery的属性
'字段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层：
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 '查询到的集合我们还要将字符串转化，给字段，操作符，组合关系等分别赋值
将查询字段（1,2,3）对应于”表“的字段（eg: cardNo），操作符对应于关系运算符，组合关系对应于逻辑运算符(or/and)
看部分代码：
'字符串转换
'给字段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 ''' <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 FunctionPrivate 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 '声明并实例化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
