存储过程实现组合查询

这几天重构机房收费系统比以前上手多了,解决每一个bug都很有成就感。在做组合查询时,我在网上找了一些资料,发现有用字符串拼接的,但是总感觉字符串的判断条件放在D层不太好,不如将这些复杂的东西都交给数据库解决,我在数据库中创建了一个带参数的存储过程.下面就是我实现这个功能的总结.

1界面如下:通过界面可以看到要传递的参数有11个.所以创建的Entity也有11个属性.

Entity创建代码:

Public Class QueryEntity
    Private field0 As String
    Private field1 As String
    Private field2 As String
    Private operate0 As String
    Private operate1 As String
    Private operate2 As String
    Private content0 As String
    Private content1 As String
    Private content2 As String
    Private relation0 As String
    Private relation1 As String

    Public Property _field0 As String   '字段0
        Get
            Return field0
        End Get
        Set(value As String)
            field0 = value
        End Set
    End Property

    Public Property _field1 As String   '字段1
        Get
            Return field1
        End Get
        Set(value As String)
            field1 = value
        End Set
    End Property

    Public Property _field2 As String    '字段2
        Get
            Return field2
        End Get
        Set(value As String)
            field2 = value
        End Set
    End Property

    Public Property _operate0 As String   '操作符0
        Get
            Return operate0
        End Get
        Set(value As String)
            operate0 = value
        End Set
    End Property

    Public Property _operate1 As String    '操作符1
        Get
            Return operate1
        End Get
        Set(value As String)
            operate1 = value
        End Set
    End Property

    Public Property _operate2 As String    '操作符2
        Get
            Return operate2
        End Get
        Set(value As String)
            operate2 = value
        End Set
    End Property

    Public Property _content0 As String    '查询内容0
        Get
            Return content0
        End Get
        Set(value As String)
            content0 = value
        End Set
    End Property

    Public Property _content1 As String   '查询内容1
        Get 
            Return content1
        End Get
        Set(value As String)
            content1 = value
        End Set
    End Property

    Public Property _content2 As String   '查询内容2
        Get
            Return content2
        End Get
        Set(value As String)
            content2 = value
        End Set
    End Property

    Public Property _relation0 As String   '关系0
        Get
            Return relation0
        End Get
        Set(value As String)
            relation0 = value
        End Set
    End Property

    Public Property _relation1 As String   '关系1
        Get
            Return relation1
        End Get
        Set(value As String)
            relation1 = value
        End Set
    End Property

End Class

2在U层中主要实现的功能就是给Entity赋值,和将返回的值显示在datagridview中。

3在B层中的逻辑简单，就是传递参数调用D层的函数，返回datatable给U层.

 

4在D层中将参数传给数据库执行存储过程.并将返回的datatable给B层。D层的代码为：

    Public Overloads Function SelectCard(ByVal enQuery As Entity.QueryEntity) As DataTable
        Using conn As New SqlConnection(DBUtil.strConnection)

            Dim cmd As SqlCommand
            cmd = New SqlCommand("QueryStuInfo", conn)
            cmd.CommandType = CommandType.StoredProcedure   '执行的是存储过程
            cmd.Parameters.Add(New SqlParameter("@field0", enQuery._field0))         '添加的一系列参数
            cmd.Parameters.Add(New SqlParameter("@operate0", enQuery._operate0))
            cmd.Parameters.Add(New SqlParameter("@content0", enQuery._content0))
            cmd.Parameters.Add(New SqlParameter("@relation0", enQuery._relation0))
            cmd.Parameters.Add(New SqlParameter("@field1", enQuery._field1))
            cmd.Parameters.Add(New SqlParameter("@operate1", enQuery._operate1))
            cmd.Parameters.Add(New SqlParameter("@content1", enQuery._content1))
            cmd.Parameters.Add(New SqlParameter("@relation1", enQuery._relation1))
            cmd.Parameters.Add(New SqlParameter("@field2", enQuery._field2))
            cmd.Parameters.Add(New SqlParameter("@operate2", enQuery._operate2))
            cmd.Parameters.Add(New SqlParameter("@content2", enQuery._content2))

            Dim adp As New SqlDataAdapter(cmd)
            Dim dt As New DataTable
            Try
                conn.Open()
                adp.Fill(dt)          ’填充数据表
            Catch ex As Exception
                Throw ex
            End Try
            Return dt
        End Using
    End Function

 

5在SQL Sever中创建的存储过程的T-SQL语句为：

create procedure QueryStuInfo 
(@field0 char(10),
 @operate0 char(10),
 @content0 char(20),
 @relation0 char(10),
 @field1 char(10)=default,
 @operate1 char(10)=default,
 @content1 char(20)=default,
 @relation1 char(10),
 @field2 char(10)=default,
 @operate2 char(10)=default,
 @content2 char(20)=default
 )
 as 
 begin
 
 declare @SQL varchar(2000)       '实现拼接字符串
 select @SQL = 'select * from V_StuCards where ' + @field0 +@operate0 +@content0 
 if @relation0 != ''
    select @SQL =@SQL + @relation0 + @field1 +@operate1 + @content1
 if @relation1 != ''
    select @SQL =@SQL + @relation1 + @field2 +@operate2 +@content2
    
 exec(@SQL) 
 
 end

6.总结：虽然说存储过程有它的缺点，比如移植性差等，但是我不能投鼠忌器。基本的内容还是要踏实地掌握。