这几天一直在做机房收费系统中组合查询的那几个窗体。这次选择了利用数组来查询。下面就给大家说一下这个方法,希望大家指正,互相学习。
这里我要讲解的是在三层架构下实现的例子,如果你不了解三层,或者是没有听说过三层,那建议你在百度输入“三层 提高班.CSDN ”就可以看到有关三层的介绍。我们提高班的许多同学都对三层做了一些总结,对你学习、理解三层会有好处的。
言归正传,先看一下窗体的组成(U层)。
其中 ,3个“字段”下拉列表、3个“操作”下拉列表以及2个“组合关系”下拉列表中的条目分别相同。依次是“卡号、学号、姓名、性别”、“<、>、=、<>”、“与、或”。并且所要查询的记录在一个表中。
(注:下面代码只是为了讲解,对于命名规范等方面没有做考虑,望见谅。)
Private Sub cmdOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdOk.Click
Select Case cbxName1.Text.Trim()
Case "卡号"
f1 = "CardNo"
Case "学号"
f1 = "SID"
Case "姓名"
f1 = "Name"
Case "性别"
f1 = "Sex"
End Select
Select Case cbxName2.Text
Case "" ‘此处很重要
f2 = ""
Case "卡号"
f2 = "CardNo"
Case "学号"
f2 = "SID"
Case "姓名"
f2 = "Name"
Case "性别"
f2 = "Sex"
End Select
Select Case cbxName3.Text
Case "" ’此处很重要
f3 = ""
Case "卡号"
f3 = "CardNo"
Case "学号"
f3= "SID"
Case "姓名"
f3 = "Name"
Case "性别"
f3 = "Sex"
End Select
Dim myarr As Entity.StudentE() = {New Entity.StudentE(), New Entity.StudentE(), New Entity.StudentE()}
myarr(0).Name = f1
myarr(1).Name = f2
myarr(2).Name = f3
Dim myarr1() As Entity.StudentE = {New Entity.StudentE, New Entity.StudentE, New Entity.StudentE}
myarr1(0).Operation = cbxOperation1.Text
myarr1(1).Operation = cbxOperation2.Text
myarr1(2).Operation = cbxOperation3.Text
Dim myarr2() As Entity.StudentE = {New Entity.StudentE, New Entity.StudentE, New Entity.StudentE}
myarr2(0).txtName = txtName1.Text
myarr2(1).txtName = txtName2.Text
myarr2(2).txtName = txtName3.Text
Dim e0 As String = ""
Dim e1 As String = ""
Select Case cbxCompone1.Text
Case "" ‘此处表示查询时只需要按照前三个条件进行查询
e0 = ""
Case "与"
e0 = "and"
Case "或"
e0 = "or"
End Select
Select Case cbxCompone2.Text
Case ""
e1 = ""
Case "与"
e1 = "and"
Case "或"
e1 = "or"
End Select
Dim myarr3() As Entity.StudentE = {New Entity.StudentE, New Entity.StudentE, New Entity.StudentE}
myarr3(0).Compone = e0
myarr3(1).Compone = e1
Dim mybll As New BLL.StudentBLL
Dim dt As New DataTable
dt = mybll.ShowSinfoCom(myarr, myarr1, myarr2, myarr3)
If dt.Rows.Count() > 0 Then
DataGridView1.DataSource = dt
Else
MessageBox.Show("没有记录")
End If
End Sub
其中,f1、f2、f3是全局变量。另外,对于其他的一些判断,例如当第一个“组合关系”为空的时候,后面的下拉列表和文本框等都不能允许操作等要求,在此并没有进行设置。
下面再来看看业务逻辑层(B层)
Function ShowSinfoCom(ByVal myarr() As Entity.StudentE, ByVal myarr1() As Entity.StudentE, ByVal myarr2() As Entity.StudentE, ByVal myarr3() As Entity.StudentE) As DataTable
Dim mydal As New DAL.StudentDAL
Dim dt As New DataTable
dt = mydal.GetOnLineCom(myarr, myarr1, myarr2, myarr3)
Return dt
End Function
最后在来看一下DAL层,在这一层中,只向大家显示查询的SQL语句
Function GetOnLineCom(ByVal myarr() As Entity.StudentE, ByVal myarr1() As Entity.StudentE, ByVal myarr2() As Entity.StudentE, ByVal myarr3() As Entity.StudentE) As DataTable
Dim sql As String = "select * from Student_Info where " & myarr(0).Name & myarr1(0).Operation & " " & myarr2(0).txtName & myarr3(0).Compone & " " & myarr(1).Name & myarr1(1).Operation & " " & myarr2(1).txtName & myarr3(1).Compone & " " & myarr(2).Name & myarr1(2).Operation & myarr2(2).txtName & ""
Return myDataTable
End Function
这就是利用数组来进行的组合查询,当然你还可以利用拼接字符串等方法。