用户购买产品时如何更新最新数量?数据库没有减少数量

Question

protected void btnBuyNow_Click(object sender, EventArgs e)
{
Int32 UserID = Convert.ToInt32(Session["USERID"].ToString());
int PID = Convert.ToInt32(Request.QueryString["PID"]);
using (SqlConnection con = new SqlConnection(CS))
using (SqlCommand cmd = new SqlCommand("SP_UpdateProducts", con) { CommandType = CommandType.StoredProcedure })
{
DataTable dt = new DataTable();
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
sda.Fill(dt);
}
foreach (DataRow row in dt.Rows)
{
int myQty = row.Field<int>("Qty");
int PQuantity = row.Field<int>("PQuantity");
if (myQty < PQuantity)
{
int updateQty = row.Field<int>("Qty");
cmd.Parameters.AddWithValue("@Quantity", PQuantity - updateQty);
cmd.Parameters.AddWithValue("@PID", PID);
cmd.ExecuteNonQuery();
con.Open();
Response.Redirect("OrderConfirmation.aspx");
}
}
}
}

SP_UpdateProducts:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_UpdateProducts]
(@PID int,
@Quantity int)
AS
BEGIN
SET NOCOUNT ON;
UPDATE tblProducts 
SET PQuantity = @Quantity 
WHERE PID = @PID
END

Answer 1

1. [Updated]SqlDataAdapter支持本文档中所写的Insert、Update和Delete命令。对于当前场景，您不必使用SqlDataAdapter
2. 在执行ExceuteNonQuery()之前，您需要确保连接已打开
3. updateQty很奇怪，你从数据库中得到数量。不确定你想达到什么目的
4. 停止使用.AddWithValue()
5. 归功于@marc_s，您不应该用前缀"命名存储过程；sp_">
6. 对于存储过程，您需要更改脚本以获得当前的PQuantity和传入的Quantity

后端(.aspx。cs(代码：

protected void btnBuyNow_Click(object sender, EventArgs e)
{
Int32 UserID = Convert.ToInt32(Session["USERID"].ToString());
int PID = Convert.ToInt32(Request.QueryString["PID"]);
int qty = /* Expected qty to be deducted in product */
using (SqlConnection con = new SqlConnection(CS))
{
con.Open();
using (SqlCommand cmd = new SqlCommand("UpdateProducts", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Quantity", SqlDbType.int).Value = updateQty;
cmd.Parameters.Add("@PID", SqlDbType.int).Value = PID;
cmd.ExecuteNonQuery();
Response.Redirect("OrderConfirmation.aspx");
}
}
}    

存储过程：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER PROCEDURE [dbo].[UpdateProducts]
(@PID int,
@Quantity int)
AS
BEGIN
SET NOCOUNT ON;
UPDATE tblProducts 
SET PQuantity = PQuantity - @Quantity 
WHERE PID = @PID
END