之前第一遍机房收费系统,组合查询虽然做出来了,但是感觉很乱,云里雾里的。这次重构版,又重新理清了思路,但是按照自己思路走着走着就碰到了障碍:在SQL语句“SELECT * from Student where 字段 操作符 @查询内容 ”中,如果用变量代替字段、变量代替操作符的时候,就会出现非布尔类型的错误,意思是三个变量虽然替换了字段、操作符和查询内容,但是由于不是布尔类型的判定,比如“CardID”“=”“12345”这个不是布尔类型的,只是三个string放到了一起。解决方法就是利用存储过程实现动态SQL语句。 这是主界面:
下面是创建一个名字是proc_stuBasInfo的存储过程:
ALTER PROCEDURE [dbo].[proc_stuBasInfo]
-- Add the parameters for the stored procedure here
@Fields1 varchar(50),
@Operators1 varchar(50),
@Check1 varchar(50),
@Relation1 varchar(50),
@Fields2 varchar(50),
@Operators2 varchar(50),
@Check2 varchar(50),
@Relation2 varchar(50),
@Fields3 varchar(50),
@Operators3 varchar(50),
@check3 varchar(50)
AS
declare @strSQL varchar(100)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
set @strSQL='select * from student where' +char(32)+@Fields1 +char(32)+@Operators1 +char(32)+ @check1+char(32)+@Relation1 +char(32)+@Fields2 +char(32)+@Operators2 +char(32)+@Check2 +char(32)+@Relation2 +char(32)+@Fields3 +char(32)+@Operators3 +char(32)+@check3
exec (@strSQL)
END
这个存储过程是把11个变量来拼接SQL语句的字符串,然后放到string类型的变量@strSQL中。再exec(@strSQL)就实现了根据变化的变量来实现不同的sql语句。从而就能达到三种情况组合查询的目的。
然后D层代码:
'组合查询
Public Function GroupInquire(enstudent As ChargeEntity.Student) As List(Of ChargeEntity.Student) Implements [Interface].IStudent.GroupInquire
Dim GroupInquireSQLHelper As New ChargeDAL.SQLHelper
Dim GroupInquireMODHelper As New ChargeEntity.ModelHelper
Dim medt1 As DataTable
Dim mylist As New List(Of ChargeEntity.Student)
strSQL = "proc_stubasinfo" '存储过程
Dim sqlpara As SqlParameter() = {New SqlParameter("@Fields1", enstudent.Fields1),
New SqlParameter("@Operators1", enstudent.Operators1),
New SqlParameter("@Check1", enstudent.Check1),
New SqlParameter("@Relation1", enstudent.Relation1),
New SqlParameter("@Fields2", enstudent.Fields2),
New SqlParameter("@Operators2", enstudent.Operators2),
New SqlParameter("@Check2", enstudent.Check2),
New SqlParameter("@Relation2", enstudent.Relation2),
New SqlParameter("@Fields3", enstudent.Fields3),
New SqlParameter("@Operators3", enstudent.Operators3),
New SqlParameter("@Check3", enstudent.Check3)}
medt1 = GroupInquireSQLHelper.ParaSelect(strSQL, CommandType.StoredProcedure, sqlpara)
mylist = GroupInquireMODHelper.convertToList(Of ChargeEntity.Student)(medt1)
Return mylist
End Function
D层用实体enstudent来填充11个变量(字段3个、操作符3个、查询内容3个、组合关系2个)的值,并返回mylist。通过B层的判断在U层显示:
'返回查询结果()
Dim GroupInquireStuBas As New ChargeBLL.GroupInquireStuBasManager
Dim mylist As List(Of ChargeEntity.Student)
mylist = GroupInquireStuBas.GroupInquireStuBas(enstudent)
If mylist.Count = 0 Then
MsgBox("没有信息")
Else
DataGridView.DataSource = mylist
End If
这样,就能在DataGridView中显示查询条件和查询内容了。