机房重构——透过变量和存储过程看结账

偏执的太偏执、 2022-08-21 00:07 253阅读 0赞

强烈推荐一个大神的人工智能的教程：[http://www.captainbed.net/zhanghan][http_www.captainbed.net_zhanghan]

# ●前言 #

敲机房重构到结账这一块想了两天。结账的第一个Tab（售卡）需要去查卡表，查询出相应操作员注册的卡；第二个Tab(充值）需要去查充值表，查询出相应操作员充值的记录；第三个Tab（退卡）需要去查询退卡表，查询出相应操作员退卡记录，第四个Tab（临时用户）需要去查询卡表，哪些是临时用户，在第五个Tab中临时用户收费的文本框中要显示临时用户的收费，我查询的是一个视图，将未结账的该操作员注册的卡费用查出赋值。需要查的表非常的多，所以就考虑用了存储过程。

# ●存储过程 #

存储过程(Stored Procedure)是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中，经过一次编译后，第二次调用不用再次编译。用户通过指定存储过程的名字并给出参数（如果该存储过程需要参数）来执行他，创建方法可参看这篇文章：[存储过程创建方法][Link 1]，假如存储过程需要参数，那么每一个调用该存储过程的方法都需要对这个存储过程传入相应的参数，否则不可以使用同一个存储过程。

# ●结账思路 #

前言中也说道，在结账中需要很多张表， 如果一张张去查就加大了代码量，果断使用了存储过程，在U层定义一个变量用来存放需要循环的值，将此值赋值给实体，传到存储过程中，根据变量的值去判断执行哪一个SQL语句，这样也大大减少了实体的量，节省了很多的空间。

# ●代码展示 #

## U层（加载各个Tab) ##

Private Sub comUserId_SelectedIndexChanged(sender As Object, e As EventArgs) Handles comUserId.SelectedIndexChanged
            Dim a As Integer  '用来获取comUserId控件的listindex
            Dim i As Integer  '用来存放dt的记录数
            '当userid的内容发生改变，username的内容自动对应改变
            a = comUserId.SelectedIndex
            comUserName.SelectedIndex = a
            Dim Tab As Integer  '用来记录Tab控件的index
            Tab = TabAccount.SelectedIndex
            Static SellCount As Integer    '记录购卡张数
            Static CancelCount As Integer  '记录退卡张数
            Static RechargeMoney As Integer '计算充值金额
            Static CancelMoney As Integer   '计算退卡金额
            Static TempMoney As Integer     '计算临时用户消费
            Static recandcan As Integer     '计算充值记录和退卡记录总和
            Static count As Integer         '计算各个记录的总值
            count = 0
            recandcan = 0
            RechargeMoney = 0
            CancelMoney = 0
            TempMoney = 0
            Account.UserId = comUserId.Text
            Account.dateNow = DateTime.Now.ToString("yyyy-MM-dd")
            '查询注册
            Account.Count = 1
            dt = UserFacade.SelectDt(Account)
            count = count + dt.Rows.Count
            If dt.Rows.Count <> 0 Then
                DGV1.Rows.Add(dt.Rows.Count)
                For i = 0 To (dt.Rows.Count - 1)
                    DGV1.Rows(i).Cells(0).Value() = dt.Rows(i).Item(0)
                    DGV1.Rows(i).Cells(1).Value() = dt.Rows(i).Item(1)
                    DGV1.Rows(i).Cells(2).Value() = dt.Rows(i).Item(2)
                    DGV1.Rows(i).Cells(3).Value() = dt.Rows(i).Item(3)
                    If dt.Rows(i).Item(4) = "False" Then
                        recandcan = recandcan + 1
                    End If
                Next
    
            End If
            SellCount = dt.Rows.Count  '记录购卡张数
            '查询充值
            Account.Count = 2
            dt = UserFacade.SelectDt(Account)
            count = dt.Rows.Count
            If dt.Rows.Count <> 0 Then
                DGV2.Rows.Add(dt.Rows.Count)
                For i = 0 To dt.Rows.Count - 1
                    DGV2.Rows(i).Cells(0).Value() = dt.Rows(i).Item(0)
                    DGV2.Rows(i).Cells(1).Value() = dt.Rows(i).Item(1)
                    DGV2.Rows(i).Cells(2).Value() = Format(dt.Rows(i).Item(2), "yyyy-MM-dd")
                    DGV2.Rows(i).Cells(3).Value() = dt.Rows(i).Item(3)
                    If dt.Rows(i).Item(4) = "False" Then
                        recandcan = recandcan + 1
                        '计算充值金额
                        RechargeMoney = RechargeMoney + dt.Rows(i).Item(1)
                    End If
                Next
                
            End If
         
            '查询退卡
            Account.Count = 3
            dt = UserFacade.SelectDt(Account)
            count = count + dt.Rows.Count
            If dt.Rows.Count <> 0 Then
                DGV3.Rows.Add(dt.Rows.Count)
                For i = 0 To dt.Rows.Count - 1
                    DGV3.Rows(i).Cells(0).Value() = dt.Rows(i).Item(0)
                    DGV3.Rows(i).Cells(1).Value() = dt.Rows(i).Item(1)
                    DGV3.Rows(i).Cells(2).Value() = dt.Rows(i).Item(2)
                    DGV3.Rows(i).Cells(3).Value() = dt.Rows(i).Item(3)
                    If dt.Rows(i).Item(4) = "False" Then
                        recandcan = recandcan + 1
                        '计算退卡金额
                        CancelMoney = CancelMoney + dt.Rows(i).Item(3)
                    End If
                Next
                
            End If
            CancelCount = dt.Rows.Count '记录退卡张数
            Account.sumcount = count
            Account.countlot = recandcan
            '查询临时用户
            Account.Count = 4
            dt = UserFacade.SelectTemp(Account)
            DGV4.DataSource = dt
            DGV4.Columns(0).HeaderText = "学号"
            DGV4.Columns(1).HeaderText = "卡号"
            DGV4.Columns(2).HeaderText = "日期"
            DGV4.Columns(3).HeaderText = "时间"
    
            '计算临时用户消费金额
            Account.Count = 5
            dt = UserFacade.SelectDt(Account)
            If dt.Rows.Count <> 0 Then
    
                For i = 0 To dt.Rows.Count - 1
                    If dt.Rows(i).Item(1) = "临时用户" Then
                        TempMoney = TempMoney + dt.Rows(i).Item(0)
                    Else
                        TempMoney = TempMoney
                    End If
    
                Next
            Else
                TempMoney = 0
            End If
            txtSell.Text = SellCount
            txtCancel.Text = CancelCount
            txtRecharge.Text = RechargeMoney
            txtCancelCash.Text = CancelMoney
            txtTemp.Text = TempMoney
            txtMakeCash.Text = RechargeMoney - CancelMoney
            Account.rechargeMoney = RechargeMoney
            Account.consumeMoney = txtMakeCash.Text.Trim()
            Account.returnMoney = CancelMoney
        End Sub

## Facade层 ##

Public Function SelectDt(ByVal Account As Entity.AccountEntity) As DataTable
            Dim AccountBLL As New BLL.AccountBLL '实例化B层实体
            Dim dt As New DataTable       
            dt = AccountBLL.SelectDt(Account)    '调用B层
            Return dt
        End Function

## BLL层 ##

Public Function SelectDt(ByVal Account As Entity.AccountEntity) As DataTable
            IUser = UserFactory.CreateIUser  '创建接口
            dt = IUser.SelectDt(Account)     '实现接口
            Return dt
        End Function

## DAL层 ##

Public Function SelectDt(Account As Entity.AccountEntity) As DataTable Implements IDAL.IAccount.SelectDt
            '参数赋值
            Parameter = {New SqlParameter("@UserId", Account.UserId),
                         New SqlParameter("@Count", Account.Count),
                         New SqlParameter("@rechargeMoney", Account.rechargeMoney),
                         New SqlParameter("@consumeMoney", Account.consumeMoney),
                         New SqlParameter("@returnMoney", Account.returnMoney),
                         New SqlParameter("@date", Account.dateNow)}
            Dim strText As String = "proc_Account"    '查询语句
            dt = SqlHelper.ExecSelect(strText, CommandType.StoredProcedure, Parameter)   '执行查询
            Return dt
        End Function

## 存储过程 ##

USE [ChargeSystem]
    GO
    /****** Object:  StoredProcedure [dbo].[proc_Account]    Script Date: 2016/03/6 14:25:52 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		张欢
    -- Create date: <Create Date,,>
    -- Description:	<Description,,>
    -- =============================================
    ALTER PROCEDURE [dbo].[proc_Account]
    	@UserId nvarchar(11),
    	@Count int,
    	@rechargeMoney int,
            @consumeMoney int,
            @date nvarchar(10),
            @returnMoney int
      AS
        declare
            @False bit
    BEGIN
    	
    	SET NOCOUNT ON;
    	set @False='False'
    	--set @Temp='%' + '临时用户'+ '%'
      --注册情况
    	if @Count=1
            begin
                 exec('select studentNo,studentCardNo,date,time,isCheck from T_StudentCard where userId='+@UserId+'')
            end
      --充值情况
           if @Count=2
           begin
                 exec('select studentCardNo,addMoney,date,time,isCheck from T_RechargeInfo where userId='+@UserId+'')
           end
      --退卡情况
           if @Count=3 
           begin
                 exec('select studentCardNo,date,time,returnMoney,isCheck from T_CancelCard where userId='+@UserId+'')
           end
      --临时用户消费情况
           if @Count=5
          begin
                 exec('select consumeMoney,type from V_AccTempMoney where isCheck='+@False+'')
          end
      --插入表情况
          if @Count=6
          begin
      --set @date=GETDATE()
                update T_CancelCard set isCheck ='True' where userId=@UserId and isCheck=@False
                update T_RechargeInfo set isCheck='True' where userId=@UserId and isCheck=@False
                update T_StudentCard set isCheck='True' where userid=@UserId and isCheck=@False
                insert into T_Bill(rechargeMoney,consumeMoney,returnMoney,date,userId)values(@rechargeMoney,@consumeMoney,@returnMoney,@date,@UserId)

--返回表
                exec('select * from T_Bill')
      end
    END

# ●总结 #

表结构或者字段有修改的时候去修改存储过程就是一个麻烦的事情，哪里用到了这张表就需要去修改存储过程，在网上也查到可以查出都是存储过程中哪些地方用到了相应的表，可以进行简单的修改，没有亲身体验过，尚且不知道是否可以解决这个问题。传变量看似也是一次次的去查，但的确省了很多事！

[http_www.captainbed.net_zhanghan]: http://www.captainbed.net/zhanghan
[Link 1]: http://www.2cto.com/kf/201504/396125.html