个人重构机房收费系统——存储过程的介绍

最新推荐文章于 2024-07-04 16:54:26 发布

肖红

最新推荐文章于 2024-07-04 16:54:26 发布

阅读量1.1k

点赞数

分类专栏： .NET

本文链接：https://blog.youkuaiyun.com/xh921/article/details/27642119

版权

.NET 专栏收录该内容

15 篇文章

订阅专栏

开始敲个人版机房之前,师哥师姐的提前介绍就多次提到了个人版的机房要尝试用到存储过程、触发器、视图等。对于这方面的学习还差的很远，今天开始学习存储过程，并实现在“机房”中使用存储过程。

一、简单介绍概念

存储过程就是一组为了完成某种功能而自己编写的一组SQL语句，通过这样一个SQL语句集完成若干个SQL语句实现的功能，形象的说就是包装SQL语句，使数据库操作更方便。

二、存储过程的格式：

简单实例：

1、创建带输入参数的存储过程

<strong><span style="font-size:18px;">Create procedure GetUserID (@username varchar(50)
As 
Select * from UserInfo where username=@username
Go
‘执行存储过程
Execute GetUserID ‘admin
‘删除存储过程
Drop procedure GetUserID
</span></strong>

2、创建带输出参数的存储过程

注意，输出函数需要指定output为标识

声明变量需要使用declare

给变量指定值需要select

<strong><span style="font-size:18px;">Create procedure pro_2
@in_x int, @out_y int output
as
declare @x int ,@y int
Select @x=1,@y=2
If @in_x<=5  Print'请输入大于的数：'
 Else 
 While @x<=@in_x 
 Begin  
 Select @y=@y*@x 
 Select @x=@x+1 
 End  
 Select @out_y=@y
Declare @out_sum int
Execute pro_2 6,@out_sum output
Select @out_sum as result
</span></strong>

（这是在网上找到的一个例子，自己成功运行了，但是自己写的关于带有输出参数的存储过程还没有成功，继续探索、、、、、）

（注事项：不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程）

机房中的组合查询应用存储过程：

一、界面设计：

（注意各种控件的命名）

二、实体层的设计：

<strong><span style="font-size:18px;">'用于组合查询的实体
Public Class CombinationQueryEntity
    Private strField1 As String
    Private strField2 As String
    Private strField3 As String
    Public Property Field1() As String
        Get
            Return strField1
        End Get
        Set(value As String)
            strField1 = value
        End Set
    End Property
    Public Property Field2() As String
        Get
            Return strField2
        End Get
        Set(value As String)
            strField2 = value
        End Set
    End Property
    Public Property Field3() As String
        Get
            Return strField3
        End Get
        Set(value As String)
            strField3 = value
        End Set
    End Property


    Private strOperator1 As String
    Private strOperator2 As String
    Private strOperator3 As String
    Public Property Operator1() As String
        Get
            Return strOperator1
        End Get
        Set(value As String)
            strOperator1 = value
        End Set
    End Property
    Public Property Operator2() As String
        Get
            Return strOperator2
        End Get
        Set(value As String)
            strOperator2 = value
        End Set
    End Property
    Public Property Operator3() As String
        Get
            Return strOperator3
        End Get
        Set(value As String)
            strOperator3 = value
        End Set
    End Property



    Private strQueryContext1 As String
    Private strQueryContext2 As String
    Private strQueryContext3 As String
    Public Property QueryContext1() As String
        Get
            Return strQueryContext1
        End Get
        Set(value As String)
            strQueryContext1 = value
        End Set
    End Property
    Public Property QueryContext2() As String
        Get
            Return strQueryContext2
        End Get
        Set(value As String)
            strQueryContext2 = value
        End Set
    End Property
    Public Property QueryContext3() As String
        Get
            Return strQueryContext3
        End Get
        Set(value As String)
            strQueryContext3 = value
        End Set
    End Property



    Private strRelation1 As String
    Private strRelation2 As String
    Public Property Relation1() As String
        Get
            Return strRelation1
        End Get
        Set(value As String)
            strRelation1 = value
        End Set
    End Property
    Public Property Relation2() As String
        Get
            Return strRelation2
        End Get
        Set(value As String)
            strRelation2 = value
        End Set
    End Property
End Class
</span></strong>

（在组合查询中，所有的要查询的条件及为实体的属性）

三、数据库中设计存储过程：

<strong><span style="font-size:18px;">CREATE proc [dbo].[PROC_StudentOnlineCount]
@field1 varchar(40),
@operator1 varchar(10),
@queryContext1 varchar(50),
@relation1 varchar(10),

@field2 varchar(40),
@operator2 varchar(10),
@queryContext2 varchar(50),
@relation2 varchar(10),

@field3 varchar(40),
@operator3 varchar(10),
@queryContext3 varchar(50)
as 
declare @tempSql varchar(500)
begin 
	if (@relation1 ='')
	set @relation1 =null
	
	if (@relation2 ='')
	set @relation2 =null
	set @tempSql ='select cardID,onLineDate,onLineTime,offLineDate,offLineTime,consumCash,userName from T_LineInfo where '+@field1 +@operator1 + char(39) + @queryContext1 +char(39)
	if (@relation1 is not null )
	set @tempSql =@tempSql +@relation1 +char(32)+@field2 +@operator2 +char(39) +@queryContext2 +char(39)
	if (@relation2 is not null)
	set @tempSql =@tempSql +@relation2 +char(32) +@field3 +@operator3 +char(39)+@queryContext3 +char(39)
	execute (@tempSql)
end
GO
</span></strong>

四、设计IDAL接口层：

<strong><span style="font-size:18px;">Imports Entity
Public Interface IStudentOnlineCount
    '查询学生上机记录(组合查询)
    Function SelectOnlineCount(ByVal enStudentOnboardState As CombinationQueryEntity) As DataTable
End Interface
</span></strong>

五、设计DAL层的组合查询

‘调用存储过程进行组合查询，查询条件及为选择的条件

<strong><span style="font-size:18px;">'添加引用
Imports IDAL
Imports Entity
Imports System.Data.SqlClient
Public Class SqlserverStudentOnlineCount : Implements IStudentOnlineCount
    Public Function SelectOnlineCount(enStudentOnlineCount As CombinationQueryEntity) As DataTable Implements IStudentOnlineCount.SelectOnlineCount
        Dim table As DataTable
        Dim sql As String = "PROC_StudentOnlineCount"
        Dim enTempOnboardRecord As New CombinationQueryEntity

        enTempOnboardRecord = TransformField(enStudentOnlineCount)

        Dim paras As SqlParameter() = {New SqlParameter("@field1", enTempOnboardRecord.Field1),
                                       New SqlParameter("@operator1", enTempOnboardRecord.Operator1),
                                       New SqlParameter("@queryContext1", enTempOnboardRecord.QueryContext1),
                                       New SqlParameter("@relation1", enTempOnboardRecord.Relation1),
                                       New SqlParameter("@field2", enTempOnboardRecord.Field2),
                                       New SqlParameter("@operator2", enTempOnboardRecord.Operator2),
                                       New SqlParameter("@queryContext2", enTempOnboardRecord.QueryContext2),
                                       New SqlParameter("@relation2", enTempOnboardRecord.Relation2),
                                       New SqlParameter("@field3", enTempOnboardRecord.Field3),
                                       New SqlParameter("@operator3", enTempOnboardRecord.Operator3),
                                       New SqlParameter("@queryContext3", enTempOnboardRecord.QueryContext3)
            }
        table = SqlHelper.SqlHelper.GetDataTable(sql, CommandType.StoredProcedure, paras)
        Return table
End Function
</span></strong>

'对于窗体中选择的条件进行转换，与数据库中的字段相对应

<strong><span style="font-size:18px;"> Private Function TransformField(ByVal enStudentOnlineCount As CombinationQueryEntity) As CombinationQueryEntity
        Dim tempString As String

        '转换字段一
        Select Case enStudentOnlineCount.Field1
            Case "卡号"
                tempString = "cardID"
            Case "操作员"
                tempString = "userName"
            Case "上机日期"
                tempString = "onLineDate"
            Case "上机时间"
                tempString = "onLineTime"
            Case "下机日期"
                tempString = "offLineDate"
            Case "下机时间"
                tempString = "offLineTime"
            Case "消费金额"
                tempString = "consumCash"
          
            Case Else
                tempString = ""
        End Select
        enStudentOnlineCount.Field1 = tempString


        '转换字段二
        Select Case enStudentOnlineCount.Field2
            Case "卡号"
                tempString = "cardID"
            Case "上机日期"
                tempString = "onLineDate"
            Case "上机时间"
                tempString = "onLineTime"
            Case "下机日期"
                tempString = "offLineDate"
            Case "下机时间"
                tempString = "offLineTime"
            Case "消费金额"
                tempString = "consumCash"
            Case "操作员"
                tempString = "userName"
            Case Else
                tempString = ""
        End Select
        enStudentOnlineCount.Field2 = tempString


        '转换字段三
        Select Case enStudentOnlineCount.Field3
            Case "卡号"
                tempString = "cardID"
            Case "操作员"
                tempString = "userName"
            Case "上机日期"
                tempString = "onLineDate"
            Case "上机时间"
                tempString = "onLineTime"
            Case "下机日期"
                tempString = "offLineDate"
            Case "下机时间"
                tempString = "offLineTime"
            Case "消费金额"
                tempString = "consumCash"          
            Case Else
                tempString = ""
        End Select
        enStudentOnlineCount.Field3 = tempString


        '改变组合关系字段一
        Select Case enStudentOnlineCount.Relation1
            Case "与"
                tempString = "and"
            Case "或"
                tempString = "or"
            Case Else
                tempString = ""
        End Select
        enStudentOnlineCount.Relation1 = tempString

        '改变组合关系字段二
        Select Case enStudentOnlineCount.Relation2
            Case "与"
                tempString = "and"
            Case "或"
                tempString = "or"
            Case Else
                tempString = ""
        End Select
        enStudentOnlineCount.Relation2 = tempString


        Return enStudentOnlineCount

    End Function

End Class
</span></strong>

六、设计抽象工厂层方法

<strong><span style="font-size:18px;">'创建学生上机统计组合查询的工厂方法
    Public Function CreateStudentOnlineCount() As IStudentOnlineCount
        Dim className As String = AssemblyName + "." + db + "StudentOnlineCount"
        Dim istudentOnlineCount As IStudentOnlineCount

        istudentOnlineCount = CType(Assembly.Load(AssemblyName).CreateInstance(className), IStudentOnlineCount)

        Return istudentOnlineCount
End Function
</span></strong>

七、设计B层：

B层的职责是中转参数，调用工厂和接口，接收返回的table

<strong><span style="font-size:18px;">'学生上机统计信息（应用于组合查询）
    Public Function SelectOnlineCount(ByVal enStudentOnlineCount As CombinationQueryEntity) As DataTable
        Dim table As DataTable
        Dim factory As New DataAccess
        Dim istudentOnlineCount As IStudentOnlineCount

        istudentOnlineCount = factory.CreateStudentOnboardRecord
        table = istudentOnlineCount.SelectOnlineCount(enStudentOnlineCount)

        Return table

End Function
</span></strong>

八、设计U层：

U层的职责是判断输入情况，获得实体的属性也即要查询的条件，调用B层的方法

<strong><span style="font-size:18px;">Imports Entity
Imports BLL
Public Class FrmStudentQueryOnlineCount

    Private Sub btnQuery_Click(sender As Object, e As EventArgs) Handles btnQuery.Click
        Dim flag As Boolean
        Dim table As DataTable
               
        If comboRelation1.Text = "" Then
            Dim arrayCt1(2) As Control
            arrayCt1(0) = comboField1
            arrayCt1(1) = comboOperator1
            arrayCt1(2) = txtQueryContext1
            flag = SomeIsEmpty(arrayCt1)
        End If

        If comboRelation1.Text <> "" And comboRelation2.Text = "" Then
            Dim arrayCt1(6) As Control
            arrayCt1(0) = comboField1
            arrayCt1(1) = comboOperator1
            arrayCt1(2) = txtQueryContext1
            arrayCt1(3) = comboRelation1
            arrayCt1(4) = comboField2
            arrayCt1(5) = comboOperator2
            arrayCt1(6) = txtQueryContext2
            flag = SomeIsEmpty(arrayCt1)
        End If

        If comboRelation1.Text <> "" And comboRelation2.Text <> "" Then
            flag = IsAllEmpty(Me)
        End If
		Dim enStudentOnlineCount As New CombinationQueryEntity

        enStudentOnlineCount.Field1 = comboField1.Text
        enStudentOnlineCount.Field2 = comboField2.Text
        enStudentOnlineCount.Field3 = comboField3.Text
        enStudentOnlineCount.Operator1 = comboOperator1.Text
        enStudentOnlineCount.Operator2 = comboOperator2.Text
        enStudentOnlineCount.Operator3 = comboOperator3.Text
        enStudentOnlineCount.QueryContext1 = txtQueryContext1.Text
        enStudentOnlineCount.QueryContext2 = txtQueryContext2.Text
        enStudentOnlineCount.QueryContext3 = txtQueryContext3.Text
        enStudentOnlineCount.Relation1 = comboRelation1.Text
        enStudentOnlineCount.Relation2 = comboRelation2.Text

        Try
      Dim strSelectOnlineCount As New BLL.Line      Table=strSelectOnlineCount.SelectOnlineCount(enStudentOnlineCount)
            DataGridView1.DataSource = table
        Catch ex As Exception
            MsgBox(ex.Message, , "提示信息")
        End Try
    End Sub
End Class
</span></strong>

九、判断文本框或者组合框输入为空的函数

<strong><span style="font-size:18px;">Public Function IsAllEmpty(ByVal frm As Form) As Boolean

        Dim control As New Control
        For Each ct1 As Control In frm.Controls
            If ct1.GetType() Is GetType(TextBox) Then
                If ct1.Text.Length = 0 Then
                    MsgBox("信息不完整，请把信息填写完整")
                    ct1.Focus()
                    Return True
                    Exit Function
                End If
            ElseIf ct1.GetType Is GetType(ComboBox) Then
                If ct1.Text.Length = 0 Then
                    MsgBox(ct1.Tag.ToString + "不能为空！")
                    ct1.Focus()
                    Return True
                    Exit Function
                End If

            End If
        Next
        Return False

    End Function


    Public Function SomeIsEmpty(ByVal arrayCt1() As Control) As Boolean
        Dim control As New Control
        For Each ct1 As Control In arrayCt1
            If ct1.GetType() Is GetType(TextBox) Then
                If ct1.Text.Length = 0 Then
                    MsgBox(ct1.Tag.ToString + "不能为空！", vbOK, "提示信息")
                    ct1.Focus()
                    Return True
                    Exit Function
                End If
            ElseIf ct1.GetType() Is GetType(ComboBox) Then
                If ct1.Text.Length = 0 Then
                    MsgBox(ct1.Tag.ToString + "不能为空！", vbOK, "信息提示")
                    ct1.Focus()
                    Return True
                    Exit Function
                End If
            End If
        Next

        Return False
End Function
</span></strong>

关于这个判断的函数可以放在U层，考虑到系统还有其他的组合查询的功能，我把这个函数放在了类模块中。

组合查询的难点就是对于字符串的查询不会，涉及多表的查询不会，利用存储过程的优点就是将这些复杂的查询语句封装到一起，在数据库中只需要一个简单的调用存储过程即可。另外一个就是对于条件的选取，文本框和组合框输入为空的情况，利用一个函数轻松解决文本框为空判断的情况，本实例中的判断文本框为空的函数有点问题，下面是我参考的一篇博客：

http://blog.youkuaiyun.com/dreamcatchergo/article/details/7291167