机房重构结账之存储过程

机房重构实现结账的时候用到了存储过程，其实开始的时候我并没有用存储过程，然后敲到需要更新表的时候顿时懵了，oh，my god ！ So many tables 。 于是我又从头再来，开始使用存储过程。这也告诉我一定要先把思路理清楚了，把思路逻辑写下来，不要一边想一边敲。

接下来是部分代码展示：

UI：

    Private Sub combOperatorID_SelectedIndexChanged(sender As Object, e As EventArgs) Handles combOperatorID.SelectedIndexChanged        Dim facade As New Facade.SettleAccountFacade        Dim myList As New List(Of Entity.UserEntity)        Dim ht As New Hashtable()  '使用哈希表保存操作员ID和姓名，键值对        myList = facade.SelectOperator()        For i = 0 To myList.Count - 1            ht.Add(myList(i).UserID.Trim(), myList(i).UserName.Trim())        Next        If ht.Contains(combOperatorID.Text.Trim()) Then            txtOperatorName.Text = ht(combOperatorID.Text)        End If        Dim enBillInfo As New Entity.BillEntity        enBillInfo.RechargeCash = 0        enBillInfo.CancelCash = 0        enBillInfo.CheckDate = ""        enBillInfo.CheckTime = ""        enBillInfo.Head = ""        '购卡：        enBillInfo.Count = 1        Dim dtCard As New DataTable        Dim myListCard As New List(Of Entity.CardEntity)        enBillInfo.WorkerID = combOperatorID.Text.Trim()        dtCard = facade.SelectInfo(enBillInfo)        If dtCard.Rows.Count > 0 Then            myListCard = ConvertHelper.ConvertToList(Of Entity.CardEntity)(dtCard)        End If        dgvRegisteCard.DataSource = myListCard        dgvRegisteCard.Columns.Remove("Cash")        dgvRegisteCard.Columns.Remove("Status")        dgvRegisteCard.Columns.Remove("UserID")        dgvRegisteCard.Columns.Remove("IsCheck")        Entity.SettleAccountEntity.RegisteNum = myListCard.Count  '售卡张数        '充值：        enBillInfo.Count = 2        Dim dtRechargeInfo As New DataTable        Dim myListRecharge As New List(Of Entity.RechargeEntity)        enBillInfo.WorkerID = combOperatorID.Text.Trim()        dtRechargeInfo = facade.SelectInfo(enBillInfo)        If dtRechargeInfo.Rows.Count > 0 Then            myListRecharge = ConvertHelper.ConvertToList(Of Entity.RechargeEntity)(dtRechargeInfo)        End If        dgvRecharge.DataSource = myListRecharge        dgvRecharge.Columns.Remove("IsCheck")        dgvRecharge.Columns.Remove("UserID")        dgvRecharge.Columns.Remove("BeginDate")        dgvRecharge.Columns.Remove("EndDate")        Entity.SettleAccountEntity.RechargeNum = myListRecharge.Count  '充值张数        Dim sumRecharge As Double        For i = 0 To myListRecharge.Count - 1            sumRecharge += myListRecharge(i).RechargeCash        Next        SettleAccountEntity.RechargeCash = sumRecharge  '充值金额        '退卡：        enBillInfo.Count = 3        Dim dtReturnCard As New DataTable        Dim myListCancelCard As New List(Of Entity.CancelCardEntity)        enBillInfo.WorkerID = combOperatorID.Text.Trim()        dtReturnCard = facade.SelectInfo(enBillInfo)        If dtReturnCard.Rows.Count > 0 Then            myListCancelCard = ConvertHelper.ConvertToList(Of Entity.CancelCardEntity)(dtReturnCard)        End If        dgvCancelCard.DataSource = myListCancelCard        dgvCancelCard.Columns.Remove("IsCheck")        dgvCancelCard.Columns.Remove("UserID")        dgvCancelCard.Columns.Remove("BeginDate")        dgvCancelCard.Columns.Remove("EndDate")        Entity.SettleAccountEntity.ReturnCardNum = myListCancelCard.Count '退卡张数        Dim sumReturn As Double        For i = 0 To myListCancelCard.Count - 1            sumReturn += myListCancelCard(i).ReturnCash        Next        Entity.SettleAccountEntity.ReturnCash = sumReturn   '退卡金额        '汇总：        txtRegisteCardNum.Text = Entity.SettleAccountEntity.RegisteNum     '售卡张数        txtCancelCardNum.Text = Entity.SettleAccountEntity.ReturnCardNum   '退卡张数        txtRechargeCardNum.Text = Entity.SettleAccountEntity.RechargeNum    '充值张数                  txtRecharge.Text = Entity.SettleAccountEntity.RechargeCash   '充值金额        txtCancelCardCash.Text = Entity.SettleAccountEntity.ReturnCash   '退卡金额        txtSettleCash.Text = SettleAccountEntity.RechargeCash - SettleAccountEntity.ReturnCash   '应收金额    End Sub    Private Sub TabControlAccount_SelectedIndexChanged(sender As Object, e As EventArgs) Handles TabControlAccount.SelectedIndexChanged        '退出：        If TabControlAccount.SelectedIndex = 4 Then            SettleAccount.Close()        End If    End Sub    Private Sub btnSettleAccount_Click(sender As Object, e As EventArgs) Handles btnSettleAccount.Click        Dim facade As New Facade.SettleAccountFacade        Dim enBillInfo As New Entity.BillEntity        Dim flag As Boolean        enBillInfo.RechargeCash = txtRecharge.Text.Trim()        enBillInfo.CancelCash = txtCancelCardCash.Text.Trim()        enBillInfo.CheckDate = DateTime.Now.ToString("yyyy-MM-dd")        enBillInfo.CheckTime = DateTime.Now.ToString("HH:mm:ss")        enBillInfo.WorkerID = combOperatorID.Text.Trim()        enBillInfo.Head = CommonVariable.UserID        enBillInfo.Count = 4        flag = facade.UpdateInfo(enBillInfo)        If flag = True Then            MsgBox("结账成功！")        Else            MsgBox("结账失败！")        End If    End SubEnd Class

Facade：

Public Class SettleAccountFacade    Public Function SelectOperator() As List(Of Entity.UserEntity)        Dim selectOperatorBll As New BLL.SettleAccountBLL        Return selectOperatorBll.SelectOperator()    End Function    Public Function SelectInfo(ByVal enBillInfo As Entity.BillEntity) As DataTable        Dim selectInfoBll As New BLL.SettleAccountBLL        Return selectInfoBll.SelectInfo(enBillInfo)    End Function    Public Function UpdateInfo(ByVal enBillInfo As Entity.BillEntity) As Boolean        Dim updateInfoBll As New BLL.SettleAccountBLL        Return updateInfoBll.UpdateInfo(enBillInfo)    End FunctionEnd Class

BLL：

Public Class SettleAccountBLL    Public Function SelectOperator() As List(Of Entity.UserEntity)        Dim factory As New Factory.SettleAccountFactory        Dim IselectOperator As IDAL.ISettleAccount        IselectOperator = factory.CreateISettleAccount()        Return IselectOperator.SelectOperator()    End Function    Public Function SelectInfo(ByVal enBillInfo As Entity.BillEntity) As DataTable        Dim factory As New Factory.SettleAccountFactory        Dim IselectInfo As IDAL.ISettleAccount        Dim dt As New DataTable        IselectInfo = factory.CreateISettleAccount()        dt = IselectInfo.SelectInfo(enBillInfo)        Return dt    End Function    Public Function UpdateInfo(ByVal enBillInfo As Entity.BillEntity) As Boolean        Dim factory As New Factory.SettleAccountFactory        Dim IupdateInfo As IDAL.ISettleAccount        Dim flag As Boolean        IupdateInfo = factory.CreateISettleAccount()        flag = IupdateInfo.UpdateInfo(enBillInfo)        Return flag    End FunctionEnd Class

IDAL：

Public Interface ISettleAccount    Function SelectOperator() As List(Of Entity.UserEntity)    Function SelectInfo(ByVal enBillInfo As Entity.BillEntity) As DataTable    Function UpdateInfo(ByVal enBillInfo As Entity.BillEntity) As BooleanEnd Interface

DAL：

Public Class SQLserverSettleAccountDAL : Implements IDAL.ISettleAccount    Dim sqlhelper As SQLHelper.SqlHelper = New SQLHelper.SqlHelper    Public Function SelectInfo(enBillInfo As BillEntity) As DataTable Implements ISettleAccount.SelectInfo        Dim sql As String = "PROC_SettleAccount"        Dim dt As New DataTable        Dim sqlParams As SqlParameter() = {New SqlParameter("@RechargeCash", enBillInfo.RechargeCash),                                          New SqlParameter("@CancelCash", enBillInfo.CancelCash),                                          New SqlParameter("@CheckDate", enBillInfo.CheckDate),                                          New SqlParameter("@CheckTime", enBillInfo.CheckTime),                                          New SqlParameter("@WorkerID", enBillInfo.WorkerID),                                          New SqlParameter("@Head", enBillInfo.Head),                                          New SqlParameter("@Count", enBillInfo.Count)}        dt = sqlhelper.ExecSelect(sql, CommandType.StoredProcedure, sqlParams)        Return dt    End Function    Public Function UpdateInfo(enBillInfo As BillEntity) As Boolean Implements ISettleAccount.UpdateInfo        Dim sql As String = "PROC_SettleAccount"        Dim flag As Boolean        Dim sqlParams As SqlParameter() = {New SqlParameter("@RechargeCash", enBillInfo.RechargeCash),                                          New SqlParameter("@CancelCash", enBillInfo.CancelCash),                                          New SqlParameter("@CheckDate", enBillInfo.CheckDate),                                          New SqlParameter("@CheckTime", enBillInfo.CheckTime),                                          New SqlParameter("@WorkerID", enBillInfo.WorkerID),                                          New SqlParameter("@Head", enBillInfo.Head),                                          New SqlParameter("@Count", enBillInfo.Count)}        flag = sqlhelper.ExecAddDelUpdate(sql, CommandType.StoredProcedure, sqlParams)        Return flag    End Function    Public Function SelectOperator() As List(Of UserEntity) Implements ISettleAccount.SelectOperator        Dim sql As String        Dim dt As New DataTable        Dim myList As New List(Of Entity.UserEntity)        Dim sqlParams As SqlParameter() = {New SqlParameter("@UserLevel", "操作员")}        sql = "select * from User_Info where userLevel=@UserLevel"        dt = sqlhelper.ExecSelect(sql, CommandType.Text, sqlParams)        If dt.Rows.Count > 0 Then            myList = ConvertHelper.ConvertToList(Of Entity.UserEntity)(dt)        End If        Return myList    End FunctionEnd Class

存储过程：

USE [ComputerCharge_sys]GO/****** Object:  StoredProcedure [dbo].[PROC_SettleAccount]    Script Date: 2017/9/17 17:01:30 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:Lyc-- Create date: 2017-9-- Description:Settle accounts-- =============================================ALTER PROCEDURE [dbo].[PROC_SettleAccount]-- Add the parameters for the stored procedure here@Count int,@RechargeCash numeric(10,2),@CancelCash numeric(10,2),@CheckDate date,@CheckTime time,@WorkerID char(10),@Head char(10)ASdeclare @IsCheck char(10)BEGINSET NOCOUNT ON;set @IsCheck='未结账'--注册情况：if @Count=1beginselect cardNo,type,registeDate from Card_Info where isCheck='未结账'and userID=@WorkerIDend--充值情况：if @Count=2beginselect cardNo,rechargeCash,rechargeDate,rechargeTime from Recharge_Info where isCheck='未结账' and userID=@WorkerIDend--退卡情况：if @Count=3beginselect cardNo,cancelDate,cancelTime,returnCash from CancelCard_Info where isCheck='未结账' and userID=@WorkerIDend--更新情况：if @Count=4begin update Card_Info set isCheck='已结账' where userID=@WorkerID and isCheck=@IsCheckupdate Recharge_Info set isCheck='已结账' where userID=@WorkerID and isCheck=@IsCheckupdate CancelCard_Info set isCheck='已结账' where userID=@WorkerID and isCheck =@IsCheckinsert into Bill_Info values (@RechargeCash,@CancelCash,@CheckDate,@CheckTime,@WorkerID,@Head)endEND

感谢您的阅读~