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 = cmdType 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获取数据库表名的 Asdeclare @TempSql varchar(500)--临时存放sql语句 --CHAR(32)是空格,CHAR(39)单引号 beginset @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
这样之后我们需要的那几个组合查询，就只需要创建一个存储过程，我们通过参数让他从不同的表里查询。