对于组合查询,真的是“费劲苦难”,当然也只是玩笑话,下边谈谈自己拼接字符串的办法吧!当然对于泛型集合的学习,详情《泛型集合,解决Datatable强耦合》
首先我们要明确声明一个实体层,定义组合查询为实体类,将"字段,操作符,组合关系分别设为实体类combinQuery的属性
'字段1对于U层,我们除了要实现参数赋值,还要将其记录显示在DataGridView中,在显示的这一阶段自己采用了泛型集合,将datatable类型转换成了集合而显示出来
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以上代码主要还是实现将查询到的集合反馈到DataGridView的过程
'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)
看部分代码:
'字符串转换由于在整个系统过程中,我们涉及到了四个组合查询,所以定义了一个字符串类型的Table,实现了代码的复用性。以下则是拼接字符串的方法:
'给字段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>由于是D层实现调用,所以我把这个过程以一个Module形式定义在了D层中,下边看最关键的D层部分:
''' 生成组合查询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
Private clsSqlhelper As DAL.sqlhelper = New DAL.sqlhelper() '声明并实例化整个过程自己尝试定义了工厂类和接口,所以B层直接声明了一个变量,调用由Factory的方法返回来的接口
''' <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