在做机房收费系统过程中,遇到最有意思的一点就是处理组合查询这一部分。
我们使用三层架构的目的是为了解耦,三层架构中分界面层、业务逻辑层、数据层和实体部分,他们各自有各自的职责,详见我的博客“如何学习和理解三层架构”。但是在处理组合查询这一部分的时候,三层架构显得如此不搭,那么三层架构中如何处理组合查询呢?
下面是我的处理方法:
首先,UI层(界面层):
代码部分:
''' <summary>
''' 查询学生信息
''' </summary>
''' <remarks></remarks>
Public Class frmInquireStudentInfo
Private Sub cmdInquire_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdInquire.Click
Dim selectstring As New Entity.SelectString
Dim binquirestudentinfo As New BLL.InquireStudentInfo
Dim dt As New DataTable
Dim F1 As String '代替field1,字段1
Dim F2 As String '代替field2,字段2
Dim F3 As String '代替field3,字段3
Select Case cmbField1.Text
Case "学号"
F1 = "ID"
Case "卡号"
F1 = "CardNo"
Case "姓名"
F1 = "Name"
Case "性别"
F1 = "Sex"
Case "专业"
F1 = "Department"
Case "年级"
F1 = "Grade"
Case "班级"
F1 = "ClassNo"
Case Else
F1 = ""
End Select
Select Case cmbField2.Text
Case "学号"
F2 = "ID"
Case "卡号"
F2 = "CardNo"
Case "姓名"
F2 = "Name"
Case "性别"
F2 = "Sex"
Case "专业"
F2 = "Department"
Case "年级"
F2 = "Grade"
Case "班级"
F2 = "ClassNo"
Case Else
F2 = ""
End Select
Select Case cmbField3.Text
Case "学号"
F3 = "ID"
Case "卡号"
F3 = "CardNo"
Case "姓名"
F3 = "Name"
Case "性别"
F3 = "Sex"
Case "专业"
F3 = "Department"
Case "年级"
F3 = "Grade"
Case "班级"
F3 = "ClassNo"
Case Else
F3 = ""
End Select
'将参数传递给实体selectstring
selectstring.Field1 = F1
selectstring.Field2 = F2
selectstring.Field3 = F3
selectstring.Operator1 = cmbOperator1.Text
selectstring.Operator2 = cmbOperator2.Text
selectstring.Operator3 = cmbOperator3.Text
selectstring.ResearchInfo1 = Trim(txtResearchInfo1.Text)
selectstring.ResearchInfo2 = Trim(txtResearchInfo2.Text)
selectstring.ResearchInfo3 = Trim(txtResearchInfo3.Text)
selectstring.Combin1 = cmbCombin1.Text
selectstring.Combin2 = cmbCombin2.Text
'将查询的结果给表并显示
dt = binquirestudentinfo.InquireStudentInfo(selectstring)
dgv.DataSource = dt
dgv.AllowUserToAddRows = False
End Sub
这里UI层主要负责收集收据,并将数据传递给实体类。
BLL层,即业务处理层。主要负责业务逻辑的整合。
''' <summary>
''' Module ID:B18
''' Depiction:查询学生基本信息类,用于完成查询学生基本信息
''' Author:李保强
''' Create Dates:2012-05-06
''' </summary>
''' <remarks></remarks>
Public Class InquireStudentInfo
''' <summary>
''' 查询学生信息
''' </summary>
''' <param name="selectstring">实体选择字符串</param>
''' <returns>datatable表</returns>
''' <remarks></remarks>
Public Function InquireStudentInfo(ByVal selectstring As Entity.SelectString) As DataTable
Dim getinfobystring As New DAL.GetInfoByString
Dim dt As New DataTable
Try
dt = getinfobystring.GetInfoByString("Student", selectstring)
Return dt
Catch ex As Exception
Return dt
End Try
End Function
End Class
BLL层调用DAL层的方法,因为BLL层对应于UI层,所以BLL层是知道该功能对应的数据表的名称的,之所以在这里设置表名称,是为了用一个方法完成不同功能,当然方法不止一种,大家的方法我也看过几种,大同小异吧。
DAL层,即数据层。
''' <summary>
''' 通过字符串获得信息。
''' </summary>
''' <remarks></remarks>
Public Class GetInfoByString
''' <summary>
''' 通过字符串获得信息。
''' </summary>
''' <param name="table">数据库表名称</param>
''' <param name="selectstring">实体选择字符串</param>
''' <returns>datatable表</returns>
''' <remarks>author:李保强</remarks>
Public Function GetInfoByString(ByVal table As String, ByVal selectstring As Entity.SelectString) As DataTable
Dim helper As DBSQLHelper.SQLHelper
helper = New DBSQLHelper.SQLHelper
Dim dt As DataTable
dt = New DataTable
Dim selectstring1 As New Entity.SelectString
Dim sql As String
sql = selectstring.SelectString(table, selectstring)
Try
dt = helper.ExcuteReader(sql)
Return dt
Catch ex As Exception
Return dt
End Try
End Function
End Class
数据层主要和数据库打交道。在这里DAL层调用了实体的方法selectstring(table,selectstring),这里实体中加入方法是我处理组合查询的突破口,下面实体中大家可以看看这个方法的使用。
Entity层,即实体。(部分代码)
''' <summary>
''' 生成选择字符串
''' </summary>
''' <param name="table">数据库表名称</param>
''' <param name="ss">Selectstring ,实体选择字符串</param>
''' <returns>字符串,sql语句</returns>
''' <remarks>author:李保强</remarks>
Public Function SelectString(ByVal table As String, ByVal ss As SelectString) As String
Dim sql As String
Try
sql = "select * from " & table & " where " & ss.Field1 & " " & ss.Operator1 & " '" & ss.ResearchInfo1 & "'"
If ss.Combin1 = "" Then
Return sql
Else
sql = sql & " " & ss.Combin1 & " " & ss.Field2 & " " & ss.Operator2 & " '" & ss.ResearchInfo2 & "'"
If ss.Combin2 = "" Then
Return sql
Else
sql = sql & " " & ss.Combin2 & " " & ss.Field3 & " " & ss.Operator3 & " '" & ss.ResearchInfo3 & "'"
Return sql
End If
End If
Catch ex As Exception '错误处理
sql = ""
Return sql
End Try
End Function
End Class
这里实体只给处理selectstirng方法部分。
selectstring方法的功能是根据传入的参数合成SQL语句,包括表的名称,这样做可以对不同功能进行组合查询,但是只限于该功能所查询的数据在一张表中或是在同一视图中。
通过这样处理的组合查询,很好的遵守了三层架构的要求,实现了解耦的目的。