首页 > 代码库 > VB.NET版机房收费系统---组合查询
VB.NET版机房收费系统---组合查询
查询的意思就是查找,寻找,指在某一个或几个地方找出自己所要的信息,假如我想搜索一下我自己写的博客,名字叫做初雪之恋,我在百度的搜索框中输入丁国华三个字,会有怎样的惊喜等着我?
啊哦,这个信息并不是我想要的,于是我再搜,“丁国华 廊坊师范学院 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>
运行效果如下:
自此学生基本信息维护就暂时告一段落,开始敲学生基本信息维护的时候,不知道从哪儿下手,各种纠结,各种逃避,各种不想敲,可是,我已经长大了,我不能再由着我自己的性子来做事,我要为我自己做的事情负责任,走过去了,真的有那么困难么?我想其中的滋味只有走过的人才能够真正明白。
曾经看到米老师给一篇博文这样的评语,这篇博客的主要内容,我记不清楚了,好像是解决一个问题,然后成功解决的一篇博文,米老师说,这个过程是不是很养人,对,这个过程不光养人,而且锻炼人,考验我们面对新知识的心态,是一拖再拖,今目标红了又红,还是坦然面对,两天把三个组合查询一一搞定,机房收费系统未完,待续......