查询的意思就是查找,寻找,指在某一个或几个地方找出自己所要的信息,假如我想搜索一下我自己写的博客,名字叫做初雪之恋,我在百度的搜索框中输入丁国华三个字,会有如何的惊喜等着我?
啊哦,这个信息并非我想要的,于是我再搜,“丁国华 廊坊师范学院 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 '实例化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 = sql & "studentNo" & cmbOperator1 & "'" & cardNo & "'"
flag(0) = True
End If
If Not (studentNo = "") Then '假设学号不为空,且卡号不为空,运行第一个sql语句,假设学号不为空,卡号为空运行第二个sql语句
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 '假设系别不为空,且学号和卡号中有一个被选择,或者两个都选,运行第一个sql语句,否则运行第二个sql语句
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 '假设年级不为空,且学号,卡号,系别中有一个被选择,或者一个or两个被选择运行第一个sql语句,否则运行第二句
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 '调用D层
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 '调用B层
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 = "学号" '把相关信息在datagridview中显示出来
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 '窗口载入时,须要注意的一些细节问题,比方那些button能点,那些不能点
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 '退出button
Me.Close()
End Sub
Private Sub chkCardNo_Click(sender As Object, e As EventArgs) Handles chkCardNo.Click '假设卡号被选中,则txtCardNo和cmbOperator1能够点否者不能点,而且文本框的内容自己主动情况,恢复默认值
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>
执行效果例如以下:
自此学生基本信息维护就临时告一段落,開始敲学生基本信息维护的时候,不知道从哪儿下手,各种纠结,各种逃避,各种不想敲,但是,我已经长大了,我不能再由着我自己的性子来做事,我要为我自己做的事情负责任,走过去了,真的有那么困难么?我想当中的滋味仅仅有走过的人才可以真正明确。
以前看到米老师给一篇博文这种评语,这篇博客的主要内容,我记不清楚了,好像是解决一个问题,然后成功解决的一篇博文,米老师说,这个过程是不是非常养人,对,这个过程不光养人,并且锻炼人,考验我们面对新知识的心态,是一拖再拖,今目标红了又红,还是坦然面对,两天把三个组合查询一一搞定,机房收费系统未完,待续......