存储过程实现组合查询

这几天重构机房收费系统比以前上手多了,解决每一个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 PropertyEnd 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.总结：虽然说存储过程有它的缺点，比如移植性差等，但是我不能投鼠忌器。基本的内容还是要踏实地掌握。