在机房收费系统中有三个窗体功能涉及到组合查询。vb版的组合查询和.net+三层架构版的组合查询还是有一些区别的。虽然有区别，但是，核心不变——拼接字符串。
为什么说组合查询是一个小难点？
1,它与普通查询的区别:普通查询只要一条含有参数的sql语句即可.例如根据卡号查余额(其中@CardID为参数).Dim sql As String = "selectCardID ,StudentInfo.StuID,StuName,Sex,Department,Grade,Class,Explian,Cash,CardInfo.Status from StudentInfo,CardInfo where StudentInfo.StuID=CardInfo.StuID And CardInfo.CardID=@CardIDand CardInfo.Status='使用'"
(1)可以说参数@CardID的值是确定的，直接将txtCardID中的内容传递过来即可。而组合查询的参数不止一个，并且是动态的,选择不同的查询条件，就会有不同的参数，并且查询条件不止一个。
(2)并且(普通的查询（按上例）操作符为“=”，而组合查询操作符不确定（=、>、<、<>）;
(3)普通查询只有一个查询条件，组合查询有多条，并且关系不确定（或、与）
2,两层的组合查询和三层的组合查询又不一样？
原因：三层的sql语句需要参数传递，两层无需参数，直接在本层完成
我的设计:1,没有点击第一个组合关系的话,后两排控件不可用.即,只有第一个查询条件有效;2,点击了第一个组合关系,但是组合关系框内没有选内容,即便后两排填了查询条件也无效;3,点击了第一个组合关系,并且选择了其中一种关系(或/与)，如果第二排没有填写查询条件或条件输入不完整则会提示“请将查询条件输入完整！”，此时前两个查询条件输入有效；4，第二个组合关系，同理。
看起来很复杂，其实就是：选择的组合关系框数，决定有效的查询条件数。条件输入不完整时，提示。
我还增加了修改信息功能，有意者可以私下讨论。
看代码:
设计到的View_StuInfo视图：
UI层:
Public Class FrmManageStuInfo
'点击“查询”按钮
Private Sub btCheck_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btCheck.Click
Dim combinQuery_m As New Model.combinQuery
Dim combinQuery_bll As New BLL.combinQuery_BLL
Dim ds As DataSet
Dim table As String = "View_StuInfo" '视图View_StuInfo
Dim arrayCtr(8) As Control '定义控件数组
'给数组赋值
arrayCtr(0) = cmbField1 '字段1控件
arrayCtr(1) = cmbField2 '字段2控件
arrayCtr(2) = cmbField3 '字段3控件
arrayCtr(3) = cmbOperate1 '操作符1控件
arrayCtr(4) = cmbOperate2 '操作符2控件
arrayCtr(5) = cmbOperate3 '操作符3控件
arrayCtr(6) = txtContent1 '查询内容1控件
arrayCtr(7) = txtContent2 '查询内容2控件
arrayCtr(8) = txtContent3 '查询内容3控件
'当组合关系1=空，并且组合关系2不可用；或者组合关系1！=空，并且组合关系2不可用时
If (cmbAndOr1.Text = "" And cmbAndOr2.Enabled = False) Or (Not cmbAndOr1.Text = "" And cmbAndOr2.Enabled = True) Then
'调用UI层combinQuery_UI类中的TxtIsNull()方法，判断除两个组合关系控件外的所有可用控件是否为空
If combinQuery_UI.TxtIsNull(arrayCtr) Then '除两个组合关系控件外，如果存在内容为空的控件，则提示“请将查询条件输入完整”
MessageBox.Show("请将查询条件输入完整！")
Exit Sub
End If
End If
'给参数赋值
combinQuery_m.CmbField1 = cmbField1.Text.Trim() '将查询条件字段1的值赋给实体combinQuery_m的字段1CmbField1属性
combinQuery_m.CmbField2 = cmbField2.Text.Trim() '将查询条件字段2的值赋给实体combinQuery_m的字段2CmbField2属性
combinQuery_m.CmbField3 = cmbField3.Text.Trim() '将查询条件字段3的值赋给实体combinQuery_m的字段3CmbField3属性
combinQuery_m.CmbOperate1 = cmbOperate1.Text.Trim() '将操作符1的值赋给实体combinQuery_m的操作符1CmbOperate1属性
combinQuery_m.CmbOperate2 = cmbOperate2.Text.Trim() '将操作符2的值赋给实体combinQuery_m的操作符2CmbOperate2属性
combinQuery_m.CmbOperate3 = cmbOperate3.Text.Trim() '将操作符3的值赋给实体combinQuery_m的操作符3CmbOperate3属性
combinQuery_m.TxtContent1 = txtContent1.Text.Trim() '将查询内容1的值赋给实体combinQuery_m的查询内容1TxtContent1属性
combinQuery_m.TxtContent2 = txtContent2.Text.Trim() '将查询内容2的值赋给实体combinQuery_m的查询内容2TxtContent2属性
combinQuery_m.TxtContent3 = txtContent3.Text.Trim() '将查询内容3的值赋给实体combinQuery_m的查询内容3TxtContent3属性
combinQuery_m.CmbAndOr1 = cmbAndOr1.Text.Trim() '将组合关系1的值赋给实体combinQuery_m的组合关系1CmbAndOr1属性
combinQuery_m.CmbAndOr2 = cmbAndOr2.Text.Trim() '将组合关系2的值赋给实体combinQuery_m的组合关系2CmbAndOr2属性
'调用Model层combinQuery类的StudentInfoField()方法，返回界面中每个查询字段中对应的表中的每个字段名，以实体形式保存
combinQuery_m = Model.combinQuery.StudentInfoField(combinQuery_m)
'调用BLL层CombinQuery_BLL()方法，返回数据集ds
ds = combinQuery_bll.CombinQuery_BLL(table, combinQuery_m)
'DataGridView1控件绑定数据源
DataGridView1.DataSource = ds.Tables("NewTable")
DataGridView1.Columns(0).HeaderText = "学号"
DataGridView1.Columns(1).HeaderText = "卡号"
DataGridView1.Columns(2).HeaderText = "姓名"
DataGridView1.Columns(3).HeaderText = "性别"
DataGridView1.Columns(4).HeaderText = "系别"
DataGridView1.Columns(5).HeaderText = "年级"
DataGridView1.Columns(6).HeaderText = "班级"
DataGridView1.Columns(7).HeaderText = "金额"
DataGridView1.Columns(8).HeaderText = "状态"
DataGridView1.Columns(9).HeaderText = "备注"
'DataGridView1控件的列绑定数据表中的列
For i = 0 To ds.Tables("NewTable").Rows.Count - 1
DataGridView1.Columns(i).DataPropertyName = ds.Tables("NewTable").Columns(i).ToString()
Next i
btModify.Enabled = True
End Sub
'点击“组合关系1”控件
Private Sub cmbAndOr1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbAndOr1.Click
'输入第二个条件的控件们可用
cmbField2.Enabled = True
cmbOperate2.Enabled = True
txtContent2.Enabled = True
cmbAndOr2.Enabled = True
End Sub
' 窗体加载
Private Sub FrmManageStuInfo_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'窗体加载时，将DataGridView控件设为只读的，即，不可编辑。只有当点击“修改”按钮时，才可编辑，将ReadOnly=False
DataGridView1.ReadOnly = True
'“修改”按钮和“保存修改”按钮不可用。当 点击“查询”按钮，并且有查询内容（查询内容不为空）时，“修改”按钮才可用
btModify.Enabled = False
btSaveModify.Enabled = False
'输入第二个条件的控件们不可用
cmbField2.Enabled = False
cmbOperate2.Enabled = False
txtContent2.Enabled = False
cmbAndOr2.Enabled = False
'输入第三个条件的控件们不可用
cmbField3.Enabled = False
cmbOperate3.Enabled = False
txtContent3.Enabled = False
End Sub
'选择“组合关系1”中值时
Private Sub cmbAndOr1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbAndOr1.SelectedIndexChanged
'输入第二个条件的控件们可用
cmbField2.Enabled = True
cmbOperate2.Enabled = True
txtContent2.Enabled = True
cmbAndOr2.Enabled = True
End Sub
'选择“组合关系2”中值时
Private Sub cmbAndOr2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbAndOr2.SelectedIndexChanged
'输入第三个条件的控件们可用
cmbField3.Enabled = True
cmbOperate3.Enabled = True
txtContent3.Enabled = True
End Sub
BLL层:
Public Class combinQuery_BLL
''' <summary>
''' 组合查询
''' </summary>
''' <param name="combinQuery"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function CombinQuery_BLL(ByVal table As String, ByVal combinQuery As Model.combinQuery) As DataSet
Dim combinQuery_da As New DAL.combinQuery_DA
Dim ds As DataSet
ds = combinQuery_da.CombinQuery_DA(table, combinQuery)
Return ds
End Function
End Class
DAL层:
'组合查询类
Public Class combinQuery_DA
''' <summary>
''' 组合查询
''' </summary>
''' <param name="table"></param>
''' <param name="combinQuery"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function CombinQuery_DA(ByVal table As String, ByVal combinQuery As Model.combinQuery) As DataSet
'sql语句。调用Model层combinQuery类的方法CombinQuerySql()方法，返回sql语句
Dim sql As String = Model.combinQuery.CombinQuerySql(table, combinQuery)
Dim ds As DataSet '定义Dataset变量
'调用ExecuteDataset()方法，返回数据集ds
ds = New SqlHelper.SqlHelper().ExecuteDataSet(sql, CommandType.Text)
Return ds
End Function
实体层:
定义组合查询为实体类，并将“字段1”、“字段2”、”“字段3”、“操作符1”、“操作符2”、“操作符3”、“组合关系1”、“组合关系2” 设为实体类combinQuery的属性。
'组合查询信息类
Public Class combinQuery
'字段1
Private _cmbField1 As String
Public Property CmbField1() As String
Get
Return _cmbField1
End Get
Set(ByVal value As String)
_cmbField1 = value
End Set
End Property
'字段2
Private _cmbField2 As String
Public Property CmbField2() As String
Get
Return _cmbField2
End Get
Set(ByVal value As String)
_cmbField2 = value
End Set
End Property
'字段3
Private _cmbField3 As String
Public Property CmbField3() As String
Get
Return _cmbField3
End Get
Set(ByVal value As String)
_cmbField3 = value
End Set
End Property
'操作符
Private _cmbOperate1 As String
Public Property CmbOperate1() As String
Get
Return _cmbOperate1
End Get
Set(ByVal value As String)
_cmbOperate1 = value
End Set
End Property
'操作符2
Private _cmbOperate2 As String
Public Property CmbOperate2() As String
Get
Return _cmbOperate2
End Get
Set(ByVal value As String)
_cmbOperate2 = value
End Set
End Property
'操作符3
Private _cmbOperate3 As String
Public Property CmbOperate3() As String
Get
Return _cmbOperate3
End Get
Set(ByVal value As String)
_cmbOperate3 = value
End Set
End Property
'查询内容1
Private _txtContent1 As String
Public Property TxtContent1() As String
Get
Return _txtContent1
End Get
Set(ByVal value As String)
_txtContent1 = value
End Set
End Property
'查询内容2
Private _txtContent2 As String
Public Property TxtContent2() As String
Get
Return _txtContent2
End Get
Set(ByVal value As String)
_txtContent2 = value
End Set
End Property
'查询内容3
Private _txtContent3 As String
Public Property TxtContent3() As String
Get
Return _txtContent3
End Get
Set(ByVal value As String)
_txtContent3 = value
End Set
End Property
'组合关系1
Private _cmbAndOr1 As String
Public Property CmbAndOr1() As String
Get
Return _cmbAndOr1
End Get
Set(ByVal value As String)
_cmbAndOr1 = value
End Set
End Property
'组合关系2
Private _cmbAndOr2 As String
Public Property CmbAndOr2() As String
Get
Return _cmbAndOr2
End Get
Set(ByVal value As String)
_cmbAndOr2 = value
End Set
End Property
以下方法，用Select case语句给“字段1”、“字段2”、”“字段3”、“操作符1”、“操作符2”、“操作符3”、“组合关系1”、“组合关系2” 赋表的字段值。
''' <summary>
''' 将查询字段(例如:卡号)对应于StudentInfo表中的字段(例如:CardID)，将操作符对应于关系运算符,将组合关系(或/与)对应于逻辑运算符(Or/And)
''' </summary>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function StudentInfoField(ByVal combinQuery As combinQuery) As combinQuery
'Dim combinQuery As New combinQuery '实例化实体类combinQuery
'查询字段1
Select Case (combinQuery.CmbField1)
Case "卡号"
combinQuery.CmbField1 = "CardID"
Case "学号"
combinQuery.CmbField1 = "StuID"
Case "姓名"
combinQuery.CmbField1 = "StuName"
Case "性别"
combinQuery.CmbField1 = "Sex"
Case "系别"
combinQuery.CmbField1 = "Department"
Case "年级"
combinQuery.CmbField1 = "Grade"
Case "班级"
combinQuery.CmbField1 = "Class"
End Select
'查询字段2
Select Case combinQuery.CmbField2
Case "卡号"
combinQuery.CmbField2 = "CardID"
Case "学号"
combinQuery.CmbField2 = "StuID"
Case "姓名"
combinQuery.CmbField2 = "StuName"
Case "性别"
combinQuery.CmbField2 = "Sex"
Case "系别"
combinQuery.CmbField2 = "Department"
Case "年级"
combinQuery.CmbField2 = "Grade"
Case "班级"
combinQuery.CmbField2 = "Class"
End Select
'查询字段3
Select Case combinQuery.CmbField3
Case "卡号"
combinQuery.CmbField3 = "CardID"
Case "学号"
combinQuery.CmbField3 = "StuID"
Case "姓名"
combinQuery.CmbField3 = "StuName"
Case "性别"
combinQuery.CmbField3 = "Sex"
Case "系别"
combinQuery.CmbField3 = "Department"
Case "年级"
combinQuery.CmbField3 = "Grade"
Case "班级"
combinQuery.CmbField3 = "Class"
End Select
'操作符1
Select Case combinQuery.CmbOperate1
Case "="
combinQuery.CmbOperate1 = "="
Case ">"
combinQuery.CmbOperate1 = ">"
Case "<"
combinQuery.CmbOperate1 = "<"
Case "<>"
combinQuery.CmbOperate1 = "<>"
End Select
'操作符2
Select Case combinQuery.CmbOperate2
Case "="
combinQuery.CmbOperate2 = "="
Case ">"
combinQuery.CmbOperate2 = ">"
Case "<"
combinQuery.CmbOperate2 = "<"
Case "<>"
combinQuery.CmbOperate2 = "<>"
End Select
'操作符3
Select Case combinQuery.CmbOperate3
Case "="
combinQuery.CmbOperate3 = "="
Case ">"
combinQuery.CmbOperate3 = ">"
Case "<"
combinQuery.CmbOperate3 = "<"
Case "<>"
combinQuery.CmbOperate3 = "<>"
End Select
'组合关系1
Select Case combinQuery.CmbAndOr1
Case "或"
combinQuery.CmbAndOr1 = "Or"
Case "与"
combinQuery.CmbAndOr1 = "And"
End Select
'组合关系2
Select Case combinQuery.CmbAndOr2
Case "或"
combinQuery.CmbAndOr2 = "Or"
Case "与"
combinQuery.CmbAndOr2 = "And"
End Select
Return combinQuery
End Function
以下方法是拼接sql语句的方法。其中字符串类型的参数table的作用实现了代码复用性，因为不止在管理学生信息一个窗体是组合查询，还有查询教师工作记录,查询学生上机状态时也是组合查询. 通过table参数传递个窗体涉及到的表明,从而都可用次方法,代码复用.
''' <summary>
''' 生成组合查询sql语句——拼接sql字符串
''' </summary>
''' <param name="table">所需查询的表</param>
''' <param name="combinQuery">combinQuery实体</param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function CombinQuerySql(ByVal table As String, ByVal combinQuery As combinQuery) As String
'首先，第一个查询条件有效
Dim sql As String = "select * from " & table & " where " & combinQuery.CmbField1 & " " & combinQuery.CmbOperate1 & " " & combinQuery.TxtContent1
If combinQuery.CmbAndOr1 = "" Then '如果第一个组合关系为空，则第一个查询条件有效
Return sql
Else '如果第一个组合关系不为空，则前两个查询条件有效
sql = sql & " " & combinQuery.CmbAndOr1 & " " & combinQuery.CmbField2 & " " & combinQuery.CmbOperate2 & " " & combinQuery.TxtContent2.Trim()
If combinQuery.CmbAndOr2 = "" Then '如果第一个组合关系不为空，第二个组合关系为空，则仅仅前两个查询条件有效
Return sql
Else '如果第一二组合关系不为空，则三个查询条件均有效
sql = sql & " " & combinQuery.CmbAndOr2 & " " & combinQuery.CmbField3 & " " & combinQuery.CmbOperate3 & " " & combinQuery.TxtContent3.Trim()
Return sql
End If
End If
If combinQuery.CmbAndOr1 <> "" And combinQuery.CmbAndOr2 = "" Then '如果第一个组合关系不为空，第二个组合关系为空，则前两个查询条件有效
sql = sql & " " & combinQuery.CmbAndOr1 & " " & combinQuery.CmbField2 & " " & combinQuery.CmbOperate2 & " " & combinQuery.TxtContent2.Trim()
ElseIf combinQuery.CmbAndOr1 <> "" And combinQuery.CmbAndOr2 <> "" Then '如果第一个组合关系和第二个组合关系均不为空，则三个查询条件有效
sql = sql
End If
End Function
End Class