我个人一直认为，组合查询是机房收费系统的一个难点，尤其是用到三层之后，如果要为组合查询中的每一个查询建立一个显然是太麻烦了。
下面介绍一下我的方法，对大家起个参考作用。
我将该表中可输入的内容定义为一个类，这样调用一次B层和D层就可以了完成查询了。
我以学生上机记录查询为例
1、UI层
Public Class FrmQuerryRecALLStuUP
Protected Overrides Sub cmdQuerry_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
Try
myGrid.Columns.Clear()
myGrid.Columns.Add("卡号", "卡号")
myGrid.Columns.Add("学号", "学号")
myGrid.Columns.Add("学生姓名", "学生姓名")
myGrid.Columns.Add("性别", "性别")
myGrid.Columns.Add("上机日期", "上机日期")
myGrid.Columns.Add("上机时间", "上机时间")
myGrid.Columns.Add("上机日期", "下机日期")
myGrid.Columns.Add("下机日期", "下机时间")
myGrid.Columns.Add("消费金额", "消费金额")
' myGrid.Columns.Add("操作员", "操作员")
'''''''''''''''将组合框中内容传递到UQuery参数''''''''''''''''''
Dim UQuery As New Entity.Entity_QueryCompositon
UQuery.Combine1 = Trim(cobCombine1.Text)
UQuery.Combine2 = Trim(cobCombine2.Text)
UQuery.FiledName1 = Trim(cobFild1.Text)
UQuery.FiledName2 = Trim(cobFild2.Text)
UQuery.FiledName3 = Trim(cobFild3.Text)
UQuery.Operator1 = Trim(cobOperator1.Text)
UQuery.Operator2 = Trim(cobOperator2.Text)
UQuery.Operator3 = Trim(cobOperator3.Text)
UQuery.QueryContent1 = Trim(TextBox1.Text)
UQuery.QueryContent2 = Trim(TextBox2.Text)
UQuery.QueryContent3 = Trim(TextBox3.Text)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim BQuerry As New BLL.BLL_QuerryCompositon
Dim BIsEmpty As String
BIsEmpty = BQuerry.isEmpty(UQuery) '判断是否为空
If BQuerry.QuerryOK = False Then '如果判断失败，则退出
Exit Sub
End If
Dim BModifyFild As New Entity.Entity_QueryCompositon
BModifyFild = BQuerry.ModifyFild(UQuery) '将汉字转换为计算机能识别的语言
Dim table As New DataTable
table = BQuerry.QuerryStuUp(BModifyFild)
If table.Rows.Count <= 0 Then
MsgBox("没有记录")
Else
For i = 0 To table.Rows.Count - 1 '将dt表中的内容显示在DataGridView中 ’这种方式显示部分
myGrid.Rows.Add()
myGrid.Rows(i).Cells(0).Value = table.Rows(i)(0) '卡号
myGrid.Rows(i).Cells(1).Value = table.Rows(i)(1) '学号
myGrid.Rows(i).Cells(2).Value = table.Rows(i)(2) '学生姓名
myGrid.Rows(i).Cells(3).Value = table.Rows(i)(4) '性别
myGrid.Rows(i).Cells(4).Value = table.Rows(i)(5) '上机日期
myGrid.Rows(i).Cells(5).Value = table.Rows(i)(6) '上机时间
myGrid.Rows(i).Cells(6).Value = table.Rows(i)(7) '下机日期
myGrid.Rows(i).Cells(7).Value = table.Rows(i)(8) '下机时间
myGrid.Rows(i).Cells(8).Value = table.Rows(i)(10) '消费金额
' myGrid.Rows(i).Cells(9).Value = table.Rows(i)(9) '操作员
Next i
myGrid.Rows(0).Selected = False
End If
Catch ex As Exception
MsgBox(ex.Message, vbOKOnly, "警告！")
End Try
End Sub
Protected Overrides Sub cmdOut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
Dim grd = daochu(myGrid)
End Sub
Private Sub FrmQuerryRecALLStuUP_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
cobFild1.Items.Add("卡号")
cobFild1.Items.Add("学号")
cobFild1.Items.Add("姓名")
cobFild1.Items.Add("性别")
cobFild1.Items.Add("上机日期")
cobFild1.Items.Add("上机时间")
cobFild1.Items.Add("下机日期")
cobFild1.Items.Add("下机时间")
cobFild1.Items.Add("消费金额")
cobFild2.Items.Add("卡号")
cobFild2.Items.Add("学号")
cobFild2.Items.Add("姓名")
cobFild2.Items.Add("性别")
cobFild2.Items.Add("上机日期")
cobFild2.Items.Add("上机时间")
cobFild2.Items.Add("下机日期")
cobFild2.Items.Add("下机时间")
cobFild2.Items.Add("消费金额")
cobFild3.Items.Add("卡号")
cobFild3.Items.Add("学号")
cobFild3.Items.Add("姓名")
cobFild3.Items.Add("性别")
cobFild3.Items.Add("上机日期")
cobFild3.Items.Add("上机时间")
cobFild3.Items.Add("下机日期")
cobFild3.Items.Add("下机时间")
cobFild3.Items.Add("消费金额")
End Sub
End Class
2、B层
'/*************************************************
'作者：金福林
'小组: 无
'说明：组合框B层
'创建日期：2014/5/5 20:10:13
'版本号：1.1.0
'**********************************************/
Public Class BLL_QuerryCompositon
Implements IDAL.IDAL_QuerryCompositon
Public QuerryOK As Boolean = False
Public Function CombineString(ByVal QuerryCompositon As Entity.Entity_QueryCompositon) As String
End Function
Public Function isEmpty(ByVal QuerryCompositon As Entity.Entity_QueryCompositon) As String
If QuerryCompositon.FiledName1 = "" Then
MsgBox("字段名不能为空，请重新输入", vbOKOnly, "警告！")
Exit Function
End If
If QuerryCompositon.Operator1 = "" Then
MsgBox("操作符不能为空，请重新输入", vbOKOnly, "警告！")
Exit Function
End If
If QuerryCompositon.QueryContent1 = "" Then
MsgBox("查询内容不能为空，请重新输入", vbOKOnly, "警告！")
Exit Function
End If
If QuerryCompositon.QueryContent1.Length > 11 Then
MsgBox("您对查询内容一输入的字符串过长，请重新输入", vbOKOnly, "警告！")
Exit Function
End If
If QuerryCompositon.Combine1 <> "" Then '如果组合关系不为空
If QuerryCompositon.FiledName2 = "" Then
MsgBox("当存在组合关系时，字段名不能为空，请重新输入", vbOKOnly, "警告！")
Exit Function
End If
If QuerryCompositon.Operator2 = "" Then
MsgBox("当存在组合关系时，操作符不能为空，请重新输入", vbOKOnly, "警告！")
Exit Function
End If
If QuerryCompositon.QueryContent2 = "" Then
MsgBox("当存在组合关系时，查询内容不能为空，请重新输入", vbOKOnly, "警告！")
Exit Function
End If
If QuerryCompositon.QueryContent2.Length > 11 Then
MsgBox("您对查询内容二输入的字符串过长，请重新输入", vbOKOnly, "警告！")
Exit Function
End If
End If
If QuerryCompositon.Combine2 <> "" Then '如果组合关系不为空
If QuerryCompositon.FiledName3 = "" Then
MsgBox("当存在组合关系时，字段名不能为空，请重新输入", vbOKOnly, "警告！")
Exit Function
End If
If QuerryCompositon.Operator3 = "" Then
MsgBox("当存在组合关系时，操作符不能为空，请重新输入", vbOKOnly, "警告！")
Exit Function
End If
If QuerryCompositon.QueryContent3 = "" Then
MsgBox("当存在组合关系时，查询内容不能为空，请重新输入", vbOKOnly, "警告！")
Exit Function
End If
If QuerryCompositon.QueryContent2.Length > 11 Then
MsgBox("您对查询内容三输入的字符串过长，请重新输入", vbOKOnly, "警告！")
Exit Function
End If
End If
QuerryOK = True
End Function
Public Function ModifyFild(ByVal QUerryCompositon As Entity.Entity_QueryCompositon) As Entity.Entity_QueryCompositon
Dim tempString As String
Dim CombineString As New Entity.Entity_QueryCompositon
Select Case QUerryCompositon.FiledName1
''''''''''''''学生信息维护的字段修改''''''''''''''''''''''''''
Case "学号"
tempString = "studentNo"
Case "卡号"
tempString = "cardno"
Case "性别"
tempString = "sex"
Case "年级"
tempString = "grade"
Case "班级"
tempString = "class"
Case "姓名"
tempString = "studentName"
Case "余额"
tempString = "cash"
''''''''''''学生上机记录的字段更改'''''''''''''''''
Case "上机日期"
tempString = "ondate"
Case "上机时间"
tempString = "ontime"
Case "下机日期"
tempString = "offdate"
Case "下机时间"
tempString = "offtime"
Case "消费金额"
tempString = "consume"
''''''''''''''操作员工作记录字段''''''''''''''''''''''
Case "教师"
tempString = "UserID"
Case "级别"
tempString = "Level"
Case "注册日期"
tempString = "LoginDate"
Case "注册时间"
tempString = "LoginTime"
Case "注销日期"
tempString = "LogoutDate"
Case "注销时间"
tempString = "LogoutTime"
Case "机器名"
tempString = "computer"
Case Else
tempString = ""
End Select
CombineString.FiledName1 = tempString '修改第一个字符串的名称
Select Case QUerryCompositon.FiledName2
Case "学号"
tempString = "studentNo"
Case "卡号"
tempString = "cardno"
Case "性别"
tempString = "sex"
Case "年级"
tempString = "grade"
Case "班级"
tempString = "class"
Case "姓名"
tempString = "studentName"
Case "余额"
tempString = "cash"
''''''''''''学生上机记录的字段更改'''''''''''''''''
Case "上机日期"
tempString = "ondate"
Case "上机时间"
tempString = "ontime"
Case "下机日期"
tempString = "offdate"
Case "下机时间"
tempString = "offtime"
Case "消费金额"
tempString = "consume"
''''''''''''''操作员工作记录字段''''''''''''''''''''''
Case "教师"
tempString = "UserID"
Case "级别"
tempString = "Level"
Case "注册日期"
tempString = "LoginDate"
Case "注册时间"
tempString = "LoginTime"
Case "注销日期"
tempString = "LogoutDate"
Case "注销时间"
tempString = "LogoutTime"
Case "机器名"
tempString = "computer"
Case Else
tempString = ""
End Select
CombineString.FiledName2 = tempString
Select Case QUerryCompositon.FiledName3
Case "学号"
tempString = "studentNo"
Case "卡号"
tempString = "cardno"
Case "性别"
tempString = "sex"
Case "年级"
tempString = "grade"
Case "班级"
tempString = "class"
Case "姓名"
tempString = "studentName"
Case "余额"
tempString = "cash"
''''''''''''学生上机记录的字段更改'''''''''''''''''
Case "上机日期"
tempString = "ondate"
Case "上机时间"
tempString = "ontime"
Case "下机日期"
tempString = "offdate"
Case "下机时间"
tempString = "offtime"
Case "消费金额"
tempString = "consume"
''''''''''''''操作员工作记录字段''''''''''''''''''''''
Case "教师"
tempString = "UserID"
Case "级别"
tempString = "Level"
Case "注册日期"
tempString = "LoginDate"
Case "注册时间"
tempString = "LoginTime"
Case "注销日期"
tempString = "LogoutDate"
Case "注销时间"
tempString = "LogoutTime"
Case "机器名"
tempString = "computer"
Case Else
tempString = ""
End Select
CombineString.FiledName3 = tempString
Select Case QUerryCompositon.Combine1
Case "与"
tempString = "and"
Case "或"
tempString = "or"
Case Else
tempString = ""
End Select
CombineString.Combine1 = tempString
Select Case QUerryCompositon.Combine2
Case "与"
tempString = "and"
Case "或"
tempString = "or"
Case Else
tempString = ""
End Select
CombineString.Combine2 = tempString
'Select Case QUerryCompositon.Operator1
' Case "="
' tempString = "like"
' CombineString.Operator1 = tempString
' Case Else
' tempString = ""
' CombineString.Operator1 = QUerryCompositon.Operator1
'End Select
'Select Case QUerryCompositon.Operator2
' Case "="
' tempString = "like"
' CombineString.Operator2 = tempString
' Case Else
' tempString = ""
' CombineString.Operator2 = QUerryCompositon.Operator2
'End Select
'Select Case QUerryCompositon.Operator3
' Case "="
' tempString = "like"
' CombineString.Operator3 = tempString
' Case Else
' tempString = ""
' CombineString.Operator3 = QUerryCompositon.Operator3
'End Select
'''''''将未替换的字符仍然保留为原来字符'''''''''''''''''''''''
CombineString.QueryContent1 = QUerryCompositon.QueryContent1
CombineString.QueryContent2 = QUerryCompositon.QueryContent2
CombineString.QueryContent3 = QUerryCompositon.QueryContent3
CombineString.Operator1 = QUerryCompositon.Operator1
CombineString.Operator2 = QUerryCompositon.Operator2
CombineString.Operator3 = QUerryCompositon.Operator3
Return CombineString
End Function
Public Function QuerryOperator(ByVal QuerryCompositon As Entity.Entity_QueryCompositon) As System.Data.DataTable Implements IDAL.IDAL_QuerryCompositon.QuerryOperator
Dim DQueryOperator As New DAL.DAL_QuerryCompositon
Dim table As New DataTable
table = DQueryOperator.QuerryOperator(QuerryCompositon)
Return table
End Function
Public Function QuerryStuInfo(ByVal QuerryCompositon As Entity.Entity_QueryCompositon) As System.Data.DataTable Implements IDAL.IDAL_QuerryCompositon.QuerryStuInfo
Dim DQueryStu As New DAL.DAL_QuerryCompositon
Dim table As New DataTable
table = DQueryStu.QuerryStuInfo(QuerryCompositon)
Return table
End Function
Public Function QuerryStuUp(ByVal QuerryCompositon As Entity.Entity_QueryCompositon) As System.Data.DataTable Implements IDAL.IDAL_QuerryCompositon.QuerryStuUp
Dim DQueryStuUP As New DAL.DAL_QuerryCompositon
Dim table As New DataTable
table = DQueryStuUP.QuerryStuUp(QuerryCompositon)
Return table
End Function
End Class
3、D层
'/*************************************************
'作者：金福林
'小组: 无
'说明：组合查询D层
'创建日期：2014/5/5 21:02:58
'版本号：1.1.0
'**********************************************/
Public Class DAL_QuerryCompositon
Implements IDAL.IDAL_QuerryCompositon
Public Function QuerryOperator(ByVal QuerryCompositon As Entity.Entity_QueryCompositon) As System.Data.DataTable Implements IDAL.IDAL_QuerryCompositon.QuerryOperator
Dim strcon As String
Dim sqlHelper As New SqlHelper
If QuerryCompositon.Combine1 = "" Then '如果第一个组合框为空
strcon = "select * from worklog_Info where " & QuerryCompositon.FiledName1 & " " & QuerryCompositon.Operator1 _
& "'" & QuerryCompositon.QueryContent1 & "'"
Else
If QuerryCompositon.Combine2 = "" Then '如果第一个不为空，第二个组合框为空
strcon = "select * from worklog_Info where " & QuerryCompositon.FiledName1 & " " & QuerryCompositon.Operator1 _
& "'" & QuerryCompositon.QueryContent1 & "' " & QuerryCompositon.Combine1 & " " & QuerryCompositon.FiledName2 _
& " " & QuerryCompositon.Operator2 & "'" & QuerryCompositon.QueryContent2 & "'"
Else '如果两个组合框都不为空
strcon = "select * from worklog_Info where " & QuerryCompositon.FiledName1 & " " & QuerryCompositon.Operator1 _
& "'" & QuerryCompositon.QueryContent1 & "' " & QuerryCompositon.Combine1 & " " & QuerryCompositon.FiledName2 _
& " " & QuerryCompositon.Operator2 & "'" & QuerryCompositon.QueryContent2 & "' " & QuerryCompositon.Combine2 _
& " " & QuerryCompositon.FiledName3 & " " & QuerryCompositon.Operator3 & "'" & QuerryCompositon.QueryContent3 & "'"
End If
End If
Return sqlHelper.ExecSelect(strcon, CommandType.Text)
End Function
Public Function QuerryStuInfo(ByVal QuerryCompositon As Entity.Entity_QueryCompositon) As System.Data.DataTable Implements IDAL.IDAL_QuerryCompositon.QuerryStuInfo
Dim strcon As String
Dim sqlHelper As New SqlHelper
If QuerryCompositon.Combine1 = "" Then '如果第一个组合框为空
strcon = "select * from student_Info where " & QuerryCompositon.FiledName1 & " " & QuerryCompositon.Operator1 _
& "'" & QuerryCompositon.QueryContent1 & "'"
Else
If QuerryCompositon.Combine2 = "" Then '如果第一个不为空，第二个组合框为空
strcon = "select * from student_Info where " & QuerryCompositon.FiledName1 & " " & QuerryCompositon.Operator1 _
& "'" & QuerryCompositon.QueryContent1 & "' " & QuerryCompositon.Combine1 & " " & QuerryCompositon.FiledName2 _
& " " & QuerryCompositon.Operator2 & "'" & QuerryCompositon.QueryContent2 & "'"
Else '如果两个组合框都不为空
strcon = "select * from student_Info where " & QuerryCompositon.FiledName1 & " " & QuerryCompositon.Operator1 _
& "'" & QuerryCompositon.QueryContent1 & "' " & QuerryCompositon.Combine1 & " " & QuerryCompositon.FiledName2 _
& " " & QuerryCompositon.Operator2 & "'" & QuerryCompositon.QueryContent2 & "' " & QuerryCompositon.Combine2 _
& " " & QuerryCompositon.FiledName3 & " " & QuerryCompositon.Operator3 & "'" & QuerryCompositon.QueryContent3 & "'"
End If
End If
Return sqlHelper.ExecSelect(strcon, CommandType.Text)
End Function
Public Function QuerryStuUp(ByVal QuerryCompositon As Entity.Entity_QueryCompositon) As System.Data.DataTable Implements IDAL.IDAL_QuerryCompositon.QuerryStuUp
Dim strcon As String
Dim sqlHelper As New SqlHelper
If QuerryCompositon.Combine1 = "" Then '如果第一个组合框为空
strcon = "select * from Line_Info where " & QuerryCompositon.FiledName1 & " " & QuerryCompositon.Operator1 _
& "'" & QuerryCompositon.QueryContent1 & "'"
Else
If QuerryCompositon.Combine2 = "" Then '如果第一个不为空，第二个组合框为空
strcon = "select * from Line_Info where " & QuerryCompositon.FiledName1 & " " & QuerryCompositon.Operator1 _
& "'" & QuerryCompositon.QueryContent1 & "' " & QuerryCompositon.Combine1 & " " & QuerryCompositon.FiledName2 _
& " " & QuerryCompositon.Operator2 & "'" & QuerryCompositon.QueryContent2 & "'"
Else '如果两个组合框都不为空
strcon = "select * from Line_Info where " & QuerryCompositon.FiledName1 & " " & QuerryCompositon.Operator1 _
& "'" & QuerryCompositon.QueryContent1 & "' " & QuerryCompositon.Combine1 & " " & QuerryCompositon.FiledName2 _
& " " & QuerryCompositon.Operator2 & "'" & QuerryCompositon.QueryContent2 & "' " & QuerryCompositon.Combine2 _
& " " & QuerryCompositon.FiledName3 & " " & QuerryCompositon.Operator3 & "'" & QuerryCompositon.QueryContent3 & "'"
End If
End If
Return sqlHelper.ExecSelect(strcon, CommandType.Text)
End Function
End Class
4、实体层
'/*************************************************
'作者：金福林
'小组: 无
'说明：用于组合查询的类
'创建日期：2014/5/5 20:01:08
'版本号：1.1.0
'**********************************************/
Public Class Entity_QueryCompositon
Private e_FiledName1 As String '字段1
Public Property FiledName1 As String
Get
Return Me.e_FiledName1
End Get
Set(ByVal value As String)
Me.e_FiledName1 = value
End Set
End Property
Private e_FiledName2 As String '字段名2
Public Property FiledName2 As String
Get
Return Me.e_FiledName2
End Get
Set(ByVal value As String)
Me.e_FiledName2 = value
End Set
End Property
Private e_FiledName3 As String '字段名3
Public Property FiledName3 As String
Get
Return Me.e_FiledName3
End Get
Set(ByVal value As String)
Me.e_FiledName3 = value
End Set
End Property
Private e_Operator1 As String '操作符1
Public Property Operator1 As String
Get
Return Me.e_Operator1
End Get
Set(ByVal value As String)
Me.e_Operator1 = value
End Set
End Property
Private e_Operator2 As String '操作符2
Public Property Operator2 As String
Get
Return Me.e_Operator2
End Get
Set(ByVal value As String)
Me.e_Operator2 = value
End Set
End Property
Private e_Operator3 As String '操作符3
Public Property Operator3 As String
Get
Return Me.e_Operator3
End Get
Set(ByVal value As String)
Me.e_Operator3 = value
End Set
End Property
Private e_QueryContent1 As String '查询内容1
Public Property QueryContent1 As String
Get
Return Me.e_QueryContent1
End Get
Set(ByVal value As String)
Me.e_QueryContent1 = value
End Set
End Property
Private e_QueryContent2 As String '查询内容2
Public Property QueryContent2 As String
Get
Return Me.e_QueryContent2
End Get
Set(ByVal value As String)
Me.e_QueryContent2 = value
End Set
End Property
Private e_QueryContent3 As String '查询内容3
Public Property QueryContent3 As String
Get
Return Me.e_QueryContent3
End Get
Set(ByVal value As String)
Me.e_QueryContent3 = value
End Set
End Property
Private e_CompositionRelation1 As String '组合关系1
Public Property Combine1 As String
Get
Return Me.e_CompositionRelation1
End Get
Set(ByVal value As String)
Me.e_CompositionRelation1 = value
End Set
End Property
Private e_CompositionRelation2 As String '组合关系2
Public Property Combine2 As String
Get
Return Me.e_CompositionRelation2
End Get
Set(ByVal value As String)
Me.e_CompositionRelation2 = value
End Set
End Property
End Class
5、接口层
Imports Entity
Public Interface IDAL_QuerryCompositon
' Function isEmpty(ByVal QuerryCompositon As Entity_QueryCompositon) As String
' Function CombineString(ByVal QuerryCompositon As Entity_QueryCompositon) As String
' Function ModifyFild(ByVal QUerryCompositon As Entity_QueryCompositon) As Entity_QueryCompositon
Function QuerryStuInfo(ByVal QuerryCompositon As Entity_QueryCompositon) As DataTable
Function QuerryStuUp(ByVal QuerryCompositon As Entity_QueryCompositon) As DataTable
Function QuerryOperator(ByVal QuerryCompositon As Entity_QueryCompositon) As DataTable
End Interface
6、sqlhelper类在我上篇博客中已经写到
http://blog.csdn.net/jinfulin/article/details/25072239
对于这么多代码，可能大家第一次看有点头大，大家主要看实体层和D层，不要照搬，领会思想，理解了之后就会觉得很简单了。