首先,说明不是只要执行多条语句创建个存储过程就完事了,有些情况下用了存储过程还要该一系列的代码,这个繁复程度不亚于直接执行语句。博主就被困在这里良久。
这里以结账为例。
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方法和新的实体类,还是直接查询方便些。