组合查询是费了不少功夫,查了不少资料,主要的两种思路是:字符串拼接和结合存储过程的使用.
第一种方法是通过最简单的方法来实现的,三层架构是为了解耦,对于组合查询就有点不太好处理,下面是我处理的过程,
程序界面:
U层(界面层):手机数据,传递给实体
”’ <summary>”’ 获取数据的值后传递给实体,并查找后数据显示给用户”’ </summary>”’ <param name="sender"></param>”’ <param name="e"></param>”’ <remarks></remarks>Private Sub cmdQuery_Click(sender As Object, e As EventArgs) Handles cmdQuery.ClickDim enselectString As New Entity.SelectStringDim table As New DataTable’代替变量Dim f1 As StringDim f2 As StringDim f3 As String’选择后转换成数据库识别的字段Select Case cboFieldA.TextCase "学号"f1 = "studentId"Case "姓名"f1 = "studentName"Case "性别"f1 = "sex"Case "系别"f1 = "department"Case "年级"f1 = "grade"Case "班级"f1 = "stuClass"Case "备注"f1 = "studentStyle"Case "卡号"f1 = "cardId"Case Elsef1 = ""End SelectSelect Case cboFieldB.TextCase "学号"f2 = "studentId"Case "姓名"f2 = "studentName"Case "性别"f2 = "sex"Case "系别"f2 = "department"Case "年级"f2 = "grade"Case "班级"f2 = "stuClass"Case "备注"f2 = "studentStyle"Case "卡号"f2 = "cardId"Case Elsef2 = ""End SelectDim f4 As StringSelect Case cboRelationA.TextCase "或"f4 = "or"Case "与"f4 = "and"Case Elsef4 = ""End SelectSelect Case cboFieldC.TextCase "学号"f3 = "studentId"Case "姓名"f3 = "studentName"Case "性别"f3 = "sex"Case "系别"f3 = "department"Case "年级"f3 = "grade"Case "班级"f3 = "stuClass"Case "备注"f3 = "studentStyle"Case "卡号"f3 = "cardId"Case Elsef3 = ""End Select’将参数传递给实体enselectString.Work_cboFieldA = f1enselectString.Work_cboFieldB = f2enselectString.Work_cboFieldC = f3enselectString.Work_cboOperatorA = cboOperatorA.TextenselectString.Work_cboOperatorB = cboOperatorB.TextenselectString.Work_cboOperatorC = cboOperatorC.TextenselectString.Work_cboRelationA = f4enselectString.Work_cboRelationB = cboRelationB.TextenselectString.Work_txtConditionA = txtConditionA.TextenselectString.Work_txtConditionB = txtConditionB.TextenselectString.Work_txtConditionC = txtConditionC.Text’将查询结果显示出来给用户Dim InquireStudentInfoFA As New Facade.StudentManageFAtable = InquireStudentInfoFA.InquireStudentInfoBll(enselectString)DGW.DataSource = table ‘数据的绑定’显示成自己需要的数据DGW.Columns(0).HeaderText = "学号"DGW.Columns(1).HeaderText = "姓名"DGW.Columns(2).HeaderText = "卡号"DGW.Columns(3).HeaderText = "余额"DGW.Columns(4).HeaderText = "系别"DGW.Columns(5).HeaderText = "年级"DGW.Columns(6).HeaderText = "班级"DGW.Columns(7).HeaderText = "性别"DGW.Columns(8).HeaderText = "状态"DGW.Columns(9).HeaderText = "备注"
实体层中的方法 ”’ <summary>”’ 生成字符串”’ </summary>”’ <param name="table">访问不同的数据库表的名称</param>”’ <param name="ss"></param>”’ <returns>字符串</returns>”’ <remarks></remarks>Public Function SelectString(ByVal table As String, ByVal ss As SelectString) As StringDim sql As StringTrysql = "select * from " & table & " where " & ss.cboFieldA & " " & ss.Work_cboOperatorA & " ‘" & ss.Work_txtConditionA & "’"If ss.Work_cboRelationA = "" ThenReturn sqlElsesql = sql & " " & ss.Work_cboRelationA & " " & ss.Work_cboFieldB & " " & ss.Work_cboOperatorB & " ‘" & ss.Work_txtConditionB & "’"If ss.Work_cboRelationB = "" ThenReturn sqlElsesql = sql & " " & ss.Work_cboRelationB & " " & ss.Work_cboFieldC & " " & ss.Work_cboOperatorC & " ‘" & ss.Work_txtConditionC & "’"Return sqlEnd IfEnd IfCatch ex As Exception’错误处理sql = ""Return sqlEnd Try
B层:逻辑的整理
”’ <summary>”’ 查询学生的信息”’ </summary>”’ <param name="selectstring">实体选择字符串</param>”’ <returns>返回表</returns>”’ <remarks></remarks>Public Function InquireStudentInfo(ByVal selectstring As Entity.SelectString) As DataTableDim factory As New DBFactory ‘实例化工厂Dim iStudent As IStudent ‘定义接口iStudent = factory.CreateStudentInfo ‘创建接口Dim dt As New DataTableTry ‘通过视图来获取自己需要的信息dt = iStudent.GetInfoByString("V_QueryStudentInfo", selectstring)Return dtCatch ex As ExceptionReturn dtEnd TryEnd FunctionD:通过字符串来获取信息 ”’ <summary>”’ 通过字符串来获取信息”’ </summary>”’ <param name="table">数据库表</param>”’ <param name="selectstring">实体选择字符串</param>”’ <returns><返回表/returns>”’ <remarks></remarks>Public Function GetInfoByString(table As String, selectstring As SelectString) As DataTable Implements IStudent.GetInfoByStringDim dt As New DataTable ‘声明变量Dim selectstring1 As New Entity.SelectStringDim sql As String ‘定义字符串sql = selectstring.SelectString(table, selectstring)Try’调用SqlHelper执行dt = SqlHelper.DBHelper.GetDataTable(sql, CommandType.Text)Return dtCatch ex As ExceptionReturn dtEnd TryEnd Function德高培身，财多伤身。
