查詢的意思就是查找，尋找，指在某一個或幾個地方找出自己所要的信息，假如我想搜索一下我自己寫的博客，名字叫做初雪之戀，我在百度的搜索框中輸入丁國華三個字，會有怎樣的驚喜等著我？
啊哦，這個信息並不是我想要的，於是我再搜，“丁國華 廊坊師范學院 csdn”搜索結果如下：
很顯然，這個還不是我想要的結果，我接著搜，“丁國華 廊坊師范學院 csdn 初雪之戀”；
在我的理解中，組合查詢就是通過條件層層限制，一步一步精確，得到我們想要的答案，在機房收費系統中，有好幾處涉及組合查詢，接下來，以學生基本信息維護為例，講解組合查詢。在第二次機房收費系統中，我沒有按原來胡陽版的模板來，原版的通過卡號，學號，姓名，性別，系別，年級，班號七個字段，來進行查詢，操作符分別為等於，大於，小於，不等於，可是，現實生活中哪有性別大於什麼，姓名小於什麼的條件，所以我重新設計了一下窗體，從中挑選了四個字段名作為組合查詢的條件簡化了組合查詢的難度，主界面設計如下：
接下來，看看我們的具體實現過程，首先實體層---E層；
<span style="font-size:18px;">Public Class Studentinfo
Public studentNo As String
Public Property _studentNo As String
Get
Return studentNo
End Get
Set(value As String)
studentNo = value
End Set
End Property
Public cardNo As String
Public Property _cardNo As String
Get
Return cardNo
End Get
Set(value As String)
cardNo = value
End Set
End Property
Public studentName As String
Public Property _studentName As String
Get
Return studentName
End Get
Set(value As String)
studentName = value
End Set
End Property
Public sex As String
Public Property _sex As String
Get
Return sex
End Get
Set(value As String)
sex = value
End Set
End Property
Public department As String
Public Property _department
Get
Return department
End Get
Set(value)
department = value
End Set
End Property
Public grade As String
Public Property _grade
Get
Return grade
End Get
Set(value)
grade = value
End Set
End Property
Public stuclass As String
Public Property _stuclass
Get
Return stuclass
End Get
Set(value)
stuclass = value
End Set
End Property
Public remark As String
Public Property _remark
Get
Return remark
End Get
Set(value)
remark = value
End Set
End Property
Public Handler As String
Public Property _Handler
Get
Return Handler
End Get
Set(value)
Handler = value
End Set
End Property
End Class
</span>
數據訪問層---D層
<span style="font-size:18px;">Imports System.Data.SqlClient
Public Class StuBaInforPreserveDAO
Public Function query(ByVal cmbOperator1 As String, ByVal cardNo As String, ByVal cmbOperator2 As String, ByVal studentNo As String, ByVal cmbOperator3 As String, ByVal department As String, ByVal cmbOperator4 As String, ByVal grade As String) As DataTable
Dim db As New Entity.Dbutil '實例化
Dim dt As New DataTable
Using conn As New SqlConnection(db.connstring)
conn.Open()
Dim sql As String
Dim cmd As SqlCommand
Dim dataAdpter As New SqlDataAdapter
Dim dst As New DataSet
Dim flag(3) As Boolean
sql = "select studentNo,cardNo,studentName,sex,department,grade,class,remark,handler from Student_info where "
If Not (cardNo = "") Then
sql = sql & "studentNo" & cmbOperator1 & "'" & cardNo & "'"
flag(0) = True
End If
If Not (studentNo = "") Then
flag(1) = True
If flag(0) = True Then
sql = sql & "and studentNo " & cmbOperator2 & "'" & studentNo & "'"
Else
sql = sql & "studentNo" & cmbOperator2 & "'" & studentNo & "'"
End If
End If
If Not (department = "") Then
flag(2) = True
If flag(0) = True Or flag(1) = True Then
sql = sql & "and department " & cmbOperator3 & "'" & department & "'"
Else
sql = sql & "department" & cmbOperator3 & "'" & department & "'"
End If
End If
If Not (grade = "") Then
flag(3) = True
If flag(0) = True Or flag(1) = True Or flag(2) = True Then
sql = sql & "and grade" & cmbOperator4 & "'" & grade & "'"
Else
sql = sql & "grade" & cmbOperator4 & "'" & grade & "'"
End If
End If
cmd = New SqlCommand(sql, conn)
dataAdpter.SelectCommand = cmd
dataAdpter.Fill(dst, "Student_info")
dt = dst.Tables("Student_info")
End Using
Return dt
End Function
End Class
</span>
業務邏輯層---B層；
<span style="font-size:18px;">Public Class StuBaInforPreserveManager
Public Function query(ByVal cmbOperator1 As String, ByVal cardNo As String, ByVal cmbOperator2 As String, ByVal studentNo As String, ByVal cmbOperator3 As String, ByVal department As String, ByVal cmbOperator4 As String, ByVal grade As String) As DataTable
Dim StuBaInforPreserveDAO As New DAL.StuBaInforPreserveDAO
Return StuBaInforPreserveDAO.query(cmbOperator1, cardNo, cmbOperator2, studentNo, cmbOperator3, department, cmbOperator4, grade)
End Function
End Class</span>
顯示層---U層；
<span style="font-size:18px;">Imports BLL
Public Class frmStuBaInforPreserve
Private Sub btnInquery_Click(sender As Object, e As EventArgs) Handles btnInquery.Click
Dim StuBaInforPreserveManager As New BLL.StuBaInforPreserveManager
Dim dt As New DataTable
dt = StuBaInforPreserveManager.query(cmbOperator1.Text, txtCardNo.Text, cmbOperator2.Text, txtStuNo.Text, cmbOperator3.Text, txtDepartment.Text, cmbOperator4.Text, txtGrade.Text)
DataGridView1.AutoGenerateColumns = True '自動創建列
DataGridView1.DataSource = dt
DataGridView1.Columns(0).HeaderText = "學號"
DataGridView1.Columns(1).HeaderText = "卡號"
DataGridView1.Columns(2).HeaderText = "姓名"
DataGridView1.Columns(3).HeaderText = "性別"
DataGridView1.Columns(4).HeaderText = "系別"
DataGridView1.Columns(5).HeaderText = "年級"
DataGridView1.Columns(6).HeaderText = "班級"
DataGridView1.Columns(7).HeaderText = "備注"
End Sub
Private Sub frmStuBaInforPreserve_Load(sender As Object, e As EventArgs) Handles MyBase.Load
cmbOperator1.Enabled = False
txtCardNo.Text = ""
txtCardNo.Enabled = False
cmbOperator1.Items.Add("=")
cmbOperator1.Items.Add(">")
cmbOperator1.Items.Add("<")
cmbOperator1.Items.Add("<>")
cmbOperator1.SelectedIndex = 0
cmbOperator2.Enabled = False
txtStuNo.Text = ""
txtStuNo.Enabled = False
cmbOperator2.Items.Add("=")
cmbOperator2.Items.Add(">")
cmbOperator2.Items.Add("<")
cmbOperator2.Items.Add("<>")
cmbOperator2.SelectedIndex = 0
cmbOperator3.Enabled = False
txtDepartment.Text = ""
txtDepartment.Enabled = False
cmbOperator3.Items.Add("=")
cmbOperator3.Items.Add("<>")
cmbOperator3.SelectedIndex = 0
cmbOperator4.Enabled = False
txtGrade.Text = ""
txtGrade.Enabled = False
cmbOperator4.Items.Add("=")
cmbOperator4.Items.Add("<>")
cmbOperator4.SelectedIndex = 0
End Sub
Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
Me.Close()
End Sub
Private Sub chkCardNo_Click(sender As Object, e As EventArgs) Handles chkCardNo.Click
If chkCardNo.Checked Then
txtCardNo.Enabled = True
cmbOperator1.Enabled = True
Else
txtCardNo.Enabled = False
cmbOperator1.Enabled = False
txtCardNo.Text = ""
cmbOperator1.SelectedIndex = 0
End If
End Sub
Private Sub chkStuNo_Click(sender As Object, e As EventArgs) Handles chkStuNo.Click
If chkStuNo.Checked Then
txtStuNo.Enabled = True
cmbOperator2.Enabled = True
Else
txtStuNo.Enabled = False
cmbOperator2.Enabled = False
txtStuNo.Text = ""
cmbOperator2.SelectedIndex = 0
End If
End Sub
Private Sub chkDepartment_Click(sender As Object, e As EventArgs) Handles chkDepartment.Click
If chkDepartment.Checked Then
txtDepartment.Enabled = True
cmbOperator3.Enabled = True
Else
txtDepartment.Enabled = False
cmbOperator3.Enabled = False
txtDepartment.Text = ""
cmbOperator3.SelectedIndex = 0
End If
End Sub
Private Sub chkGrade_Click(sender As Object, e As EventArgs) Handles chkGrade.Click
If chkGrade.Checked Then
txtGrade.Enabled = True
cmbOperator4.Enabled = True
Else
txtGrade.Enabled = False
cmbOperator4.Enabled = False
txtGrade.Text = ""
cmbOperator4.SelectedIndex = 0
End If
End Sub
End Class</span>
運行效果如下：
自此學生基本信息維護就暫時告一段落，開始敲學生基本信息維護的時候，不知道從哪兒下手，各種糾結，各種逃避，各種不想敲，可是，我已經長大了，我不能再由著我自己的性子來做事，我要為我自己做的事情負責任，走過去了，真的有那麼困難麼？我想其中的滋味只有走過的人才能夠真正明白。
曾經看到米老師給一篇博文這樣的評語，這篇博客的主要內容，我記不清楚了，好像是解決一個問題，然後成功解決的一篇博文，米老師說，這個過程是不是很養人，對，這個過程不光養人，而且鍛煉人，考驗我們面對新知識的心態，是一拖再拖，今目標紅了又紅，還是坦然面對，兩天把三個組合查詢一一搞定，機房收費系統未完，待續......
作者：csdn博客 丁國華
