结账-存储过程走过的坑

首先，说明不是只要执行多条语句创建个存储过程就完事了，有些情况下用了存储过程还要该一系列的代码，这个繁复程度不亚于直接执行语句。博主就被困在这里良久。
这里以结账为例。

U层

        private void comboUserID_SelectedIndexChanged(object sender, EventArgs e)
        {
            Facade.frmAdmUserFacade facade = new Facade.frmAdmUserFacade();
            Entity.UserInfo user = new Entity.UserInfo();
            List<Entity.UserInfo> list = new List<Entity.UserInfo>();
            user.UserID = Convert.ToInt32(comboUserID.Text.Trim());
            list = facade.SelectUserIDList(user);
            txtUserName.Text = list[0].UserName;

            int Tab = TabAccount.SelectedIndex;//获取索引
            int SellCount = 0;//记录售卡张数
            int CancelCount = 0;//记录退卡张数
            int RechargeMoney = 0;//计算充值金额
            int CancelMoney = 0;//计算退卡金额
            int recandcan = 0;//计算充值记录和退卡记录总和
            int count = 0;//计算各个记录的总值
            //把值传给实体
            Entity.Check Account = new Entity.Check();
            Account.consumeMoney = 0;
            Account.rechargeMoney = 0;
            Account.returnMoney = 0;
            Account.date = DateTime.Now.ToString("yyyy-MM-dd");
            Account.userID = Convert.ToInt32(comboUserID.Text.Trim());
            //查询注册
            TabAccount.TabIndex = 1;
            Account.Count = TabAccount.TabIndex;

            List<Entity.Check> lists = new List<Entity.Check>();
            Facade.frmAdmAccountFacade fact = new Facade.frmAdmAccountFacade();
            lists = fact.check(Account);
            count = count + lists.Count;
            if (lists.Count != 0)
            {
                this.dataGridViewbuy.DataSource=lists;
                recandcan = recandcan + lists.Count;
            }

            SellCount = lists.Count; //记录购卡张数

            //查询充值
            TabAccount.TabIndex = 2;
            Account.Count = TabAccount.TabIndex;
            lists = fact.check(Account);
            if (lists.Count != 0)
            {
                this.dataGridViewRecharge.DataSource = lists;
                //recandcan = recandcan + lists.Count;
                for (int i = 0; i < lists.Count; i++)
                {
                    RechargeMoney = RechargeMoney + Convert.ToInt32(lists[i].addmoney);
                }
            }

            //查询退卡
            TabAccount.TabIndex = 3;
            Account.Count = TabAccount.TabIndex;
            lists = fact.check(Account);
            count = count + lists.Count;
            if (lists.Count != 0)
            {
                this.dataGridViewOut.DataSource = lists;
                //recandcan = recandcan + lists.Count;
                for (int i = 0; i < lists.Count; i++)
                {
                    CancelMoney = CancelMoney + Convert.ToInt32(lists[i].CancelCash);
                }
            }

            CancelCount = lists.Count;//记录退卡张数
            Account.Count = count;

            //汇总
            //TabAccount.TabIndex = 4;
            //Account.Count = TabAccount.TabIndex;
            txtSellCard.Text = recandcan.ToString();
            txtRechargeMoney.Text = RechargeMoney.ToString();
            txtCancelCard.Text = CancelCount.ToString();
            txtCancelMoney.Text = CancelMoney.ToString();
            txtAllCard.Text = (recandcan - CancelCount).ToString();
            txtAllmoney.Text = (RechargeMoney - CancelMoney).ToString();


        }

        private void butAccount_Click(object sender, EventArgs e)
        {
            Entity.Check AccountMoney = new Entity.Check();
            Facade.frmAdmAccountFacade fact = new Facade.frmAdmAccountFacade();
            Entity.StaticUser.userid = Convert.ToInt32(comboUserID.Text.Trim());
            bool lists = fact.checkMoney(AccountMoney);
            MessageBox.Show("结账成功");

        }

D层

        //有参 结账 查询表
        public List<Check> Check(Check check)
        {
            SqlParameter[] sqlParams = { new SqlParameter("@userID", check.userID), new SqlParameter("@Count", check.Count),
                                         new SqlParameter("@rechargeMoney", check.rechargeMoney),new SqlParameter("@consumeMoney", check.consumeMoney),
                                         new SqlParameter("@returnMoney", check.returnMoney),new SqlParameter("@date", check.date)};
            string sql = "Proc_Check";

            DAL.SqlHelper sqlhelper = new SqlHelper();
            DataTable table = sqlhelper.ExecuteNonQuery(sql, CommandType.StoredProcedure, sqlParams);

            //将datatable转换成泛型
            ConvertHelper ctl = new ConvertHelper();
            List<Check> list = new List<Check>();
            list = ctl.convertToList<Check>(table);
            return list;
        }
        //有参 结账 更新表
        public bool CheckMoney(Check check)
        {
            SqlParameter[] sqlParams = { new SqlParameter("@userID",StaticUser.userid) };
            string sql = "Proc_CheckMoney";
            SqlHelper sqlhelper = new SqlHelper();
            bool table = sqlhelper.ExecuteNoonQuery(sql, CommandType.StoredProcedure, sqlParams);

            return table;

        }

这里将查询与结账分开来进行，建议如果敲的话写成一个存储过程即可。第一次可以写成两个，耗时长一些，不过方便熟悉代码与过程。

存储过程

ALTER PROCEDURE [dbo].[Proc_Check] 
@userID int,  
@Count int,  
@rechargeMoney int,  
@consumeMoney int,  
@returnMoney int,  
@date varchar(10) 
AS
declare   @False varchar(10)
BEGIN

    SET NOCOUNT ON;
    --set @False ='未结账'  
    --注册情况     
    if @Count =1    
        begin    
          select studentNo,cardno,date,time from student_Info where UserID=@userID  and Ischeck='未结账'  
        end    
  --充值情况    
       if @Count =2    
       begin    
            select studentNo,cardno,addmoney,date,time from ReCharge_Info where UserID=@userID and status='未结账'  
       end    
  --退卡情况    
       if @Count =3     
       begin   
            select studentNo,cardNo,CancelCash,Date,time from CancelCard_Info where UserID=@userID and status='未结账'    
       end
       END

ALTER PROCEDURE [dbo].[Proc_CheckMoney] 
    @userID int
AS
BEGIN

    SET NOCOUNT ON;
 update CancelCard_Info set status ='结账' where UserID =@userID   and status='未结账'    
 update ReCharge_Info set status='结账' where UserID =@userID   and status='未结账'   
 update student_Info  set Ischeck='结账' where UserID  =@userID   and Ischeck='未结账'    
End

这里的坑主要在第二个更新表这里，为了实现这个存储过程还要重新建一个SqlServer方法和新的实体类，还是直接查询方便些。