大佬教程收集整理的这篇文章主要介绍了VB.NET版机房收费系统之组合查询，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。
第一次用VB做它的时候，用字符串拼接做了一个感觉特麻烦，当时在师傅的指导下了解到不用那么麻烦，将条件都设成一个个的变量，给他们一个默认值“1=1”，如果选择了一个条件，就给那个默认值覆盖掉就OK啦，所有这次果断就用他了。
界面：
U层：
'定义一个实体
Dim enCombination As New Entity.CombinationEntity
Private Sub FrmstudentBasic_Load(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles MyBase.Load
'给实体的值赋初值
enCombination.ProField = "1"
enCombination.ProFieldTwo = "1"
enCombination.ProFieldThree = "1"
enCombination.ProOperator = "="
enCombination.ProOperatorTwo = "="
enCombination.ProOperatorThree = "="
enCombination.ProContent = "1"
enCombination.ProContentTwo = "1"
enCombination.ProContentThree = "1"
enCombination.ProCombination = "and"
enCombination.ProCombinationTwo = "and"
End Sub
'*********************************
'U层查询上机状态的事件
'返回值：dataTable
'*******************************
Private Sub btnInquire_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles btnInquire.Click
'清空dataGridview控件的记录
DataGridView1.Datasource = vbNull
'判断输入是否有空值！
If cmbField1.Text = "" Or cmbOperator1.Text = "" Or txTinquire1.Text = "" Then
MsgBox("第一个查询条件有空！")
Exit Sub
Else
If Not cmbCombination1.Text = "" Then
If cmbField2.Text = "" Or cmbOperator2.Text = "" Or txTinquire2.Text = "" Then
MsgBox("第二个查询条件有空！")
Exit Sub
Else
If Not cmbCombination2.Text = "" Then
If cmbField3.Text = "" Or cmbOperator3.Text = "" Or txTinquire3.Text = "" Then
MsgBox("第三个查询条件有空！")
Exit Sub
End If
'给实体赋值（第三个条件）
enCombination.ProCombinationTwo = GetEnglish(cmbCombination2.Text.ToString())
enCombination.ProFieldThree = GetEnglish(cmbField3.Text.ToString())
enCombination.ProOperatorThree = cmbOperator3.Text.ToString()
enCombination.ProContentThree = txTinquire3.Text.ToString()
End If
End If
'给实体赋值（第二个条件）
enCombination.ProCombination = GetEnglish(cmbCombination1.Text.ToString())
enCombination.ProFieldTwo = GetEnglish(cmbField2.Text.ToString())
enCombination.ProOperatorTwo = cmbOperator2.Text.ToString()
enCombination.ProContentTwo = txTinquire2.Text.ToString()
End If
'给实体赋值(第一个条件）
enCombination.ProField = GetEnglish(cmbField1.Text.ToString())
enCombination.ProOperator = cmbOperator1.Text.ToString()
enCombination.ProContent = txTinquire1.Text.ToString()
End If
Dim studentBasic As New BLl.studentBasicBLL
Dim tb As New DataTable
Try
tb = studentBasic.InquirestudentBasic(enCombination)
Catch ex As Exception
MsgBox(ex.message.ToString())
End Try
DataGridView1.Datasource = tb
End Sub
'****************************
'函数：将控件中的汉字变为相应的英文
'*********************************
Public Function GetEnglish(ByVal strControl As String) As String
SELEct Case (strControl)
Case "卡号"
Return "cardNo"
Case "学号"
Return "studentNo"
Case "性别"
Return "sex"
Case "姓名"
Return "studentName"
Case "年级"
Return "grade"
Case "系别"
Return "department"
Case "班号"
Return "class"
Case "与"
Return "and"
Case "或"
Return "or"
Case Else
Return ""
End SELEct
End Function
B层：
Public Function InquirestudentBasic(ByVal enCombination As Entity.CombinationEntity) As DataTable Dim dataAccess As New Factory.FactoryDataBase '定义工厂 Dim ICard As IDAl.ICard '定义接口 Dim tb As New DataTable '定义一个DataTable ICard = dataAccess.CreateICard() '创建接口 tb = ICard.SELEctCard(enCombination) '调用接口的方法 Return tb '返回值 End Function
D层：
开始没有存储过程时：
'定义数据库连接对象，并利用反射将连接字符串赋给conn
Dim conn As New SqlClient.SqlConnection
conn.ConnectionString = ConfigurationManager.AppSetTings("strConn")
'定义执行SQL查询语句
Dim strSQL As String
strSQL = "SELEct * from View_studentCard where " + enCombination.ProField + enCombination.ProOperator + " '" & enCombination.ProContent & "'" + " " + enCombination.ProCombination + " " + enCombination.ProFieldTwo + enCombination.ProOperatorTwo + "'" & enCombination.ProContentTwo & "' " + " " + enCombination.ProCombinationTwo + " " + enCombination.ProFieldThree + enCombination.ProOperatorThree + " '" & enCombination.ProContentThree & "'"
'定义一个dataAdapter对象
Dim da As New SqlDataAdapter(strSQL,conn)
'定义一个dataTable对象
Dim tb As New DataTable
'将结果集填充到tb中()
da.Fill(tb)
'函数返回值
Return tb
后来加上存储过程：
Dim strSQL As String Dim paras As SqlParameter() enCombination.ProTablename = " View_studentCard" '确定数据库表名 paras = New SqlParameter() {New SqlParameter("@cmbFieldA",enCombination.ProField),New SqlParameter("@cmbFieldB",enCombination.ProFieldTwo),New SqlParameter("@cmbFieldC",enCombination.ProFieldThreE),New SqlParameter("@cmbOperatorA",enCombination.ProOperator),New SqlParameter("@cmbOperatorB",enCombination.ProOperatorTwo),New SqlParameter("@cmboperatorC",enCombination.ProOperatorThreE),New SqlParameter("@txTinquireA",enCombination.ProContent),New SqlParameter("@txTinquireB",enCombination.ProContentTwo),New SqlParameter("@txTinquireC",enCombination.ProContentThreE),New SqlParameter("@cmbCombinationA",enCombination.ProCombination),New SqlParameter("@cmbCombinationB",enCombination.ProCombinationTwo),New SqlParameter("@tablename",enCombination.ProTableName) } strSQL = "PROC_CombinationInquire" Dim tb As New DataTable tb = DAl.SqlHelper.ExecuteReader(strSQL,CommandType.StoredProcedure,paras) Return tb End Function
SQLHelPER里的ExecuteReader方法：
'********************************** '带参数的返回结果集的sql查询或存储过程 '参数：sql语句，command类型（存储过程为storedProcedure，其他为text），参数集合 '返回值：datatable '********************************** Public Shared Function ExecuteReader(ByVal strSQL As String,ByVal cmdType As CommandType,ByVal paras As SqlParameter()) As DataTable Dim conn As New SqlClient.SqlConnection conn.ConnectionString = ConfigurationManager.AppSetTings("strConn") Dim cmd As New SqlCommand(strSQL,conn) cmd.Commandtype = cR_832_11845@dType cmd.Parameters.AddRange(paras) '打开数据库连接 If conn.State = ConnectionState.Closed Then conn.open() End If Dim reader As SqlDataReader Dim tb As New DataTable reader = cmd.ExecuteReader tb.Load(reader) '关闭数据库连接 If conn.State = ConnectionState.open Then conn.Close() End If Return tb End Function
create procedure PROC_studentBasicInquire @cmbFieldA varchar(20),@cmbOperatorn varchar(20),@txTinquireA varchar(20),@cmbCombinationA varchar(20),@cmbFieldB varchar(20),@cmbOperatorB varchar(20),@txTinquireB varchar(20),@cmbCombinationB varchar(20),@cmbFieldC varchar(20),@cmboperatorC varchar(20),@txTinquireC varchar(20),@TempSql varchar(20),@tablename Sql varchar(20)) --tablename获取数据库表名的 As declare @TempSql varchar(500)--临时存放sql语句 --CHAR(32)是空格,CHAR(39)单引号 begin set @TempSql='SELEct * from ' +@tablename +' where '+CHAR(32)+ @cmbFieldA +@cmbOperatorA+CHAR(39) +@txTinquireA +CHAR(39) +@cmbCombinationA +CHAR(32)+ @cmbFieldB + @cmbOperatorB +CHAR(39)+ @txTinquireB +CHAR(39) +@cmbCombinationB + CHAR(32)+ @cmbFieldC + @cmboperatorC ++CHAR(39)+@txTinquireC+CHAR(39) execute (@TempSql) end
这样之后我们需要的那几个组合查询，就只需要创建一个存储过程，我们通过参数让他从不同的表里查询。
以上是大佬教程为你收集整理的VB.NET版机房收费系统之组合查询全部内容，希望文章能够帮你解决VB.NET版机房收费系统之组合查询所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。