对于组合查询,真的是“费劲苦难”,当然也只是玩笑话,下边谈谈自己拼接字符串的办法吧!当然对于泛型集合的学习,详情《泛型集合,解决Datatable强耦合》
首先我们要明确声明一个实体层,定义组合查询为实体类,将"字段,操作符,组合关系分别设为实体类combinQuery的属性
@H_403_12@ '字段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层,我们除了要实现参数赋值,还要将其记录显示在DataGridView中,在显示的这一阶段自己采用了泛型集合,将datatable类型转换成了集合而显示出来看U层:
@H_403_12@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 '查询到的集合 以上代码主要还是实现将查询到的集合反馈到DataGridView的过程我们还要将字符串转化,给字段,操作符,组合关系等分别赋值
将查询字段(1,2,3)对应于”表“的字段(eg: cardNo),操作符对应于关系运算符,组合关系对应于逻辑运算符(or/and)
看部分代码:
@H_403_12@'字符串转换 '给字段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 由于在整个系统过程中,我们涉及到了四个组合查询,所以定义了一个字符串类型的Table,实现了代码的复用性。以下则是拼接字符串的方法: @H_403_12@ ''' <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 Function 由于是D层实现调用,所以我把这个过程以一个Module形式定义在了D层中,下边看最关键的D层部分: @H_403_12@Private 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 整个过程自己尝试定义了工厂类和接口,所以B层直接声明了一个变量,调用由Factory的方法返回来的接口@H_403_12@ '声明并实例化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 这样组合查询的整个过程就完成了,整个过程的实现,很好的遵守了三层架构的要求,实现了解耦的目的。当然除了拼接字符串还有使用存储过程实现的方法,这样就无需大量赋值,而且也就不用拼接字符串了,直接将这些全部定义在了存储过程中,然后再存储过程中直接传入参数就好了,虽然自己明白整个过程的实现,但是还是欠缺实践,希望在合作开发的过程中能够熟练对于各种技巧的使用。