【C#机房重构】 结账---存储过程帮了大忙了！

前言

之前在用vb做机房收费系统的时候，结账部分调用了大量的数据库中的表，非常之麻烦，这次是用七层的方式进行重构，如果还是按照固有的七个层次去调用表，想想就得疯！最后小编通过查找资料，利用存储过程可以减少超多的代码量！
所以当我们要操作多个表的时候，我们需要想想存储过程喽！

在结账之前，我们要有流程图，涉及到几个表，这个在之前的vb的项目画过，很麻烦，这里给一个博客链接https://blog.csdn.net/jerry11112/article/details/78759788 里边有个超大的流程图！

接下来我们分析，对数据库的操作无非是增删改查，我们可以通过存储过程进行分块化！

IDAL层

    //查找信息
    DataTable SelectInfo(Entity.SettleCount count);//包含冲值表，退卡表

    // 查找用户的id
    DataTable SelectUserInfo(Entity.SettleCount count);

    //查找实际总的消费金额
    DataTable SelectConsumeCash(Entity.SettleCount count);

    //更新需要更新的所有表
    int updateInfo(Entity.SettleCount count);
	//同时更新充值表，学生表，退卡表，line表

存储过程

(不同的@count在UI层执行不同的sql语句)

USE [charge_sys]
GO
/****** Object:  StoredProcedure [dbo].[PROC_SettleAccount]    Script Date: 2018/8/30 11:08:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		李光
-- Create date: 2018-8
-- Description:	Settlaccounts
-- =============================================
ALTER PROCEDURE [dbo].[PROC_SettleAccount]
	-- Add the parameters for the stored procedure here
	@Count int,
	@RechargeCash numeric(10,2),
	@CancelCash numeric(10,2),
	@ConsumeCash numeric(10,2),
	@AllCash numeric(10,2),
	@date date,
	--@TeacherID nvarchar(50),
	@UserID nvarchar(50)
AS
	declare @IsCheck char(10)
BEGIN
	SET NOCOUNT ON;
	set @IsCheck='未结账'

	--充值情况：
	if @Count=1
	begin
		select cardNo,addmoney,date,time from Recharge_Info where status='未结账' and userID=@UserID
	end
	--退卡情况：
	if @Count=2
	begin
		select cardNo,CancelCash,date,time from CancelCard_Info where status='未结账' and userID=@UserID
	end
	----更新情况：
	if @Count=3
	begin 
		update student_Info set status='结账' where isCheck=@IsCheck
		update Recharge_Info set status='结账' where userID=@UserID and status=@IsCheck
		update CancelCard_Info set status='结账' where userID=@UserID and status =@IsCheck
		update Line_Info set Ischeck='结账' where Ischeck =@IsCheck
		insert into checkWeek_Info values (@RechargeCash,@ConsumeCash,@CancelCash,@AllCash,@date,@UserID)
	end
END

D层

public class SettleCountDAL:IDAL.SettleCountIDAL
    {
        public DataTable SelectInfo(Entity.SettleCount count)
        {
            SqlHelper sqlhelper = new SqlHelper();
            SqlParameter[] sqlparams = { new SqlParameter("@UserID",count.UserID),
                                         new SqlParameter("@RechargeCash",count.RechargeCash),
                                         new SqlParameter("@ConsumeCash",count.ConsumeCash),
                                         new SqlParameter("@CancelCash",count.CancelCash),
                                         new SqlParameter("@AllCash",count.AllCash),
                                         new SqlParameter("@date",count.Date),
                                         new SqlParameter("@Count",count.count)
        };
            string sql = "PROC_SettleAccount";
            DataTable dt = sqlhelper.ExecuteQuery(sql, sqlparams, CommandType.StoredProcedure);
            return dt;
             
        }

        public int updateInfo(Entity.SettleCount count)
        {
            SqlHelper sqlhelper = new SqlHelper();
            SqlParameter[] sqlparams = { new SqlParameter("@UserID",count.UserID),
                                         new SqlParameter("@RechargeCash",count.RechargeCash),
                                         new SqlParameter("@ConsumeCash",count.ConsumeCash),
                                         new SqlParameter("@CancelCash",count.CancelCash),
                                         new SqlParameter("@AllCash",count.AllCash),
                                         new SqlParameter("@date",count.Date),
                                         new SqlParameter("@Count",count.count)
        };

            string sql = "PROC_SettleAccount";
            int result = sqlhelper.ExecuteNonQuery(sql, sqlparams, CommandType.StoredProcedure);
            return result;
        }
        //查询操作员的用户id和姓名
        public DataTable SelectUserInfo(Entity.SettleCount count)
        {
            SqlHelper sqlhelper = new SqlHelper();
            SqlParameter[] sqlparams = { new SqlParameter("@Level", "操作员") };
            string sql = "SELECT * FROM User_Info WHERE level=@Level";
            DataTable dt = sqlhelper.ExecuteQuery(sql,sqlparams, CommandType.Text);
            return dt;
        }

      public  DataTable SelectConsumeCash(Entity.SettleCount count)
        {
            SqlHelper sqlhelper = new SqlHelper();
            SqlParameter[] sqlparams = { new SqlParameter("@Ischeck", "未结账") };
            string sql = "SELECT sum(consume) FROM Line_Info WHERE Ischeck=@Ischeck";
            DataTable dt = sqlhelper.ExecuteQuery(sql,sqlparams,CommandType.Text);
            return dt;
        }
    }

UI层

  public partial class frmSettleAccounts : Form
    {
        public frmSettleAccounts()
        {
            InitializeComponent();
            tabControl1.Enabled = false;
        }

        #region 定义变量

        Entity.SettleCount Account = new Entity.SettleCount();
        Facade.SettleAountFacade FSettleAcount = new Facade.SettleAountFacade();

        Dictionary<string, string> dic = new Dictionary<string, string>(); //定义一个字典

        double sumCancel = 0;      //计算退卡总金额
        double sumRecharge = 0;   //计算充值总金额 
        #endregion


        #region 加载窗体
        private void frmSettleAccounts_Load(object sender, EventArgs e)
        {
            //表userinfo
            Facade.SettleAountFacade FOpeInfo = new Facade.SettleAountFacade();
            DataTable tableOpeinfo = FOpeInfo.selectOpeInfo(Account);
            //加载user表中的数据到字典中
            for (int i = 0; i < tableOpeinfo.Rows.Count; i++)
            {
                dic.Add(Convert.ToString(tableOpeinfo.Rows[i]["userID"]), Convert.ToString(tableOpeinfo.Rows[i]["UserName"]));
                comboBox1.Items.Add(Convert.ToString(tableOpeinfo.Rows[i]["userID"]));
            }
        }
        #endregion

        
        #region tabControl1空件不同选项卡触发不同的事件
        private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
        {
            // Account.UserID= comboBox1.Text.Trim();
            Account.RechargeCash = 0;
            Account.CancelCash = 0;
            Account.ConsumeCash = 0;
            Account.AllCash = 0;
            Account.Date = "";



            #region  //触发充值选项卡

            if (tabControl1.SelectedTab.Name == "tabCharge")
            {
                Account.count = 1;
                DataTable dtRecharge = FSettleAcount.selectInfo(Account);
                dtgRecharge.DataSource = dtRecharge;
                dtgRecharge.Columns["cardno"].HeaderText = "卡号";
                dtgRecharge.Columns["addmoney"].HeaderText = "充值金额";
                dtgRecharge.Columns["date"].HeaderText = "充值日期";
                dtgRecharge.Columns["time"].HeaderText = "充值时间";

                sumRecharge = 0;
                //求和
                if (dtRecharge.Rows.Count > 0)
                {
                    for (int i = 0; i < dtRecharge.Rows.Count; i++)
                    {
                        sumRecharge = Convert.ToInt32(dtRecharge.Rows[i]["addmoney"]) + sumRecharge;
                        Account.RechargeCash = sumRecharge;
                    }
                }
                dtgRecharge.AllowUserToAddRows = false;
            }
           
            #endregion


            //触发退卡选项卡
            if (tabControl1.SelectedTab.Name == "tabCancelCard")
            {
                Account.count = 2;
                DataTable dtCancel = FSettleAcount.selectInfo(Account);
                dtgCancelCard.DataSource = dtCancel;

                dtgCancelCard.Columns["cardNo"].HeaderText = "卡号";
                dtgCancelCard.Columns["CancelCash"].HeaderText = "退还金额";
                dtgCancelCard.Columns["Date"].HeaderText = "退卡日期";
                dtgCancelCard.Columns["time"].HeaderText = "退卡时间";
                sumCancel = 0;

                if (dtCancel.Rows.Count > 0)
                {
                    for (int i = 0; i < dtCancel.Rows.Count; i++)
                    {
                        sumCancel = Convert.ToInt32(dtCancel.Rows[i]["CancelCash"]) + sumCancel;
                        Account.CancelCash = sumCancel;
                    }
                }
                dtgCancelCard.AllowUserToAddRows = false;
            }
            //触发结账选项卡
            if (tabControl1.SelectedTab.Name == "tabAllcash")
            {
                txtRechargeCash.Text = Convert.ToString(sumRecharge);
                txtCancelCash.Text = Convert.ToString(sumCancel);
                //计算consumecash
                DataTable tableConsumeCash = FSettleAcount.selectConsumeCash(Account);
                txtConsumeCash.Text = Convert.ToString(tableConsumeCash.Rows[0][0]);
            }

            //触发退出选项卡
            if (tabControl1.SelectedTab.Name == "tabExit")
            {
                this.Close();
            }
        } 
        #endregion

        //点击用户id下拉框，出现相应内容
        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {

            Facade.SettleAountFacade FOpeInfo = new Facade.SettleAountFacade();
          
            Account.UserID = comboBox1.Text.Trim();
            //通过选中字典中键，映射到值
            string strvalue;
            if (dic.ContainsKey(comboBox1.Text.Trim()))
            {
                bool result = dic.TryGetValue(comboBox1.Text.Trim(), out strvalue);
                if (result == true)
                    txtUserName.Text = strvalue;
            }
            tabControl1.Enabled = true;
            btnOK.Enabled = true;
        }

        //结账按钮
        private void btnOK_Click(object sender, EventArgs e)
        {
            Account.count = 3;
            Account.Date = DateTime.Now.ToShortDateString();
            bool flagAccount = FSettleAcount.updateInfo(Account);
            if (flagAccount==true)
            {
                MessageBox.Show("结账成功");
            }
            else
            {
                MessageBox.Show("已经结账");
                btnOK.Enabled = false;
            }
        }

        private void tabAllcash_Click(object sender, EventArgs e)
        {

        }
    }