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

●前言

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

●存储过程

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

●结账思路

     前言中也说道，在结账中需要很多张表， 如果一张张去查就加大了代码量，果断使用了存储过程，在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 ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- 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 bitBEGINSET 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')  endEND

●总结

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