首先说一下，我用数组进行组合查询的思路。
@H_301_2@ U层：设计组合查询界面，封装窗体上的参数。 @H_301_2@ B层：由于U层的数据是“姓名”等汉字字段。主要是将U层显示的字段转换成数据库表中的字段。 @H_301_2@ D层：进行数据库 查询，之后返回查询结果。
下面是该实例的界面：
@H_301_2@接下来，看一下各层是如何实现各自的职责的。 @H_301_2@ @H_301_2@U层：Private Sub btnQuery_Click(sender As Object,e As EventArgs) Handles btnQuery.Click
'验证所选字段 是否非空
Dim myObject As New UIcommonFunction
If myObject.verifyNull(Controls) = False Then
Exit Sub
End If
Try
Dim dtWorklog As New DataTable '这个表里面存放查询结果
Dim QueryWorklog As New BLL.sqlQueryRecordBLL
'定义四个数组，用于存放组合查询的 字段
Dim fields(2) As String
Dim operate(2) As String
Dim content(2) As String
Dim relation(1) As String
'把UI上的数据，封装到数组
fields(0) = cbofield1.Text
fields(1) = cbofield2.Text
fields(2) = cbofield3.Text
operate(0) = cboOperate1.Text
operate(1) = cboOperate2.Text
operate(2) = cboOperate3.Text
content(0) = txtContent1.Text
content(1) = txtContent2.Text
content(2) = txtContent3.Text
relation(0) = cboRelation1.Text
relation(1) = cboRelation2.Text '这里的参数如果嫌烦的话，可以利用数组循环。我就不做修改了
'对操作员员工进行组合查询
dtWorklog = QueryWorklog.CombineQueryWorklog(fields,operate,content,relation)
mydatagrid.DataSource = dtWorklog
Catch ex As Exception
MessageBox.Show(ex.Message.ToString())
End Try
End Sub
B层：
''' <summary>
''' 操作员工的组合查询
''' </summary>
''' <param name="field"></param>
''' <param name="operate"></param>
''' <param name="content"></param>
''' <param name="relation"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function CombineQueryWorklog(field() As String,operate() As String,content() As String,relation() As String) As DataTable
Dim iWorklog As IDAL.IWorklog
iWorklog = Factory.DBFactory.CreateWorklog
'把UI层传回来的汉字，转换成数据库中对应的字段
For i = 0 To 2
If field(i) = "教师" Then field(i) = " UserID "
If field(i) = "上机日期" Then field(i) = " loginDate "
If field(i) = "上机时间" Then field(i) = " loginTime "
If field(i) = "注销日期" Then field(i) = " logoutDate "
If field(i) = "注销时间" Then field(i) = " logoutTime "
If field(i) = "机器号" Then field(i) = " computer "
Next
For i = 0 To 1
If relation(i) = "与" Then relation(i) = " and "
If relation(i) = "或" Then relation(i) = " or "
Next
'把要查询的内容 用单引号 （这里需要提醒一点，大家可以去对比一下 UserId='1' 与 UserId=1 这两者的区别）
For i = 0 To 2
If content(i) <> "" Then
content(i) = "'" & content(i) & "'"
End If
Next
Dim dtWorklog As New DataTable
dtWorklog = iWorklog.CombineQueryWorklog(field,relation)
If dtWorklog.Rows.Count = 0 Then
Throw New Exception("查询记录为空")
End If
Return dtWorklog
End Function
D层：
''' <summary>
''' 操作员工记录 组合查询
''' </summary>
''' <param name="fields"></param>
''' <param name="operate"></param>
''' <param name="content"></param>
''' <param name="relation"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function CombineQueryWorklog(fields() As String,relation() As String) As DataTable Implements IDAL.IWorklog.CombineQueryWorklog
Dim strsql As String = "select identity(int,1,1) as 序号,UserID as 教师ID,loginDate as 登陆日期,loginTime as 登陆时间,logoutDate as 注销日期,logoutTime as 注销时间,computer as 机器号 into #1 from worklog_Info where " & fields(0) & operate(0) & content(0) & relation(0) & fields(1) & operate(1) & content(1) & relation(1) & fields(2) & operate(2) & content(2) & "order by loginDate select * from #1 drop table #1"
Dim helper As New sqlHelper
Dim dtWorklog As New DataTable
dtWorklog = helper.QueryNo(strsql,CommandType.Text) '参考<a target=_blank href="http://blog.csdn.net/zc474235918/article/details/26963237">http://blog.csdn.net/zc474235918/article/details/26963237</a>
Return dtWorklog
End Function
End Class
对于以上查询方式，也并不是唯一，完全可以使用 参数化查询，使用sqlparameter 来完成。