如何编写单个存储过程以进行插入和更新。 [英] How to write a single stored procedure for insert and update.

问题描述

我正在使用单个按钮来插入记录并且还更新记录。我也是隐藏字段，所以就像在隐藏字段为空后单击该按钮一样，然后必须插入带有给定新值的记录，如果隐藏字段是not null，表示已经存在id，然后必须使用修改后的值更新记录。



我尝试了什么：

I am taking single button for insert records and also to update records.I am also taken hidden field, so like after clicking that button if hidden field is null, then record must be inserted with given new values and if hidden field is not null,means already id is there,then record must be updated with modified values.

What I have tried:

DAL:
 public void save_Click(int pid, string pname, string desc, int qnty, decimal pric)
    {
        SqlCommand cmd = new SqlCommand("SPSAVEDATA", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@P_id", pid);
        cmd.Parameters.AddWithValue("@P_name", pname);
        cmd.Parameters.AddWithValue("@P_desc", desc);
        cmd.Parameters.AddWithValue("@P_qty", qnty);
        cmd.Parameters.AddWithValue("@P_price", pric);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }

BAL:
 public void Product(int pid, string pname, string desc, int qnty, decimal pric)
    {
        dll.save_Click(pid, pname, desc, qnty, pric );
    }

CS:
protected void Btn_save_Click(object sender, EventArgs e)
{
    if (HiddenField1.Value == "")
    {
        bll.Product(txt_proName.Text,txt_ldesc.Text,Convert.ToInt32(txt_qnty.Text),                             Convert.ToDecimal(txt_price.Text));
        Response.Write("<script>alert('Product Added Successfully')</script>");
    }
    else
    {
 bll.Product(Convert.ToInt16(HiddenField1.Value), txt_proName.Text,  txt_ldesc.Text, Convert.ToInt32(txt_qnty.Text), Convert.ToDecimal(txt_price.Text));
        }         
        Response.Write("<script>alert('Product Updated Successfully')</script>");
    }
    GridView1.DataSource = bll.LoadProduct();
    GridView1.DataBind();

}

Stored Procedure

CREATE PROC SPSAVEDATA
@P_id int,
@P_name varchar(50),
@P_desc varchar(400),
@P_qty int,
@P_price money
AS 
BEGIN 
if(@P_id=0)
BEGIN
insert into Products1 (P_name,P_desc,P_qty,P_price) values (@P_name,@P_desc,@P_qty,@P_price) 
END  
ELSE
BEGIN
update Products1 set P_name=@P_name,P_desc=@P_desc,P_qty=@P_qty,P_price=@P_price where P_id=@P_id
END  
END

i已尝试上述代码但无法获得预期结果。

i have tried above code but unable to get expected results.

推荐答案

Quote：

public void Product(int pid, string pname, string desc, int qnty, decimal pric)
...
bll.Product(txt_proName.Text, txt_ldesc.Text, Convert.ToInt32(txt_qnty.Text), Convert.ToDecimal(txt_price.Text));

你的 Product 方法需要五个参数。您只传递了四个参数。

Your Product method expects five parameters. You are only passing four parameters.

CREATE PROC SPSAVEDATA
	@P_id int,
	@P_name varchar(50),
	@P_desc varchar(400),
	@P_qty int,
	@P_price money
AS 
BEGIN 
	UPDATE Products1 SET P_name=@P_name,
						 P_desc=@P_desc,
						 P_qty=@P_qty,
						 P_price=@P_price
						 WHERE P_id=@P_id
	
	IF (@@ROWCOUNT = 0)
	BEGIN
		INSERT INTO Products1 (P_name,P_desc,P_qty,P_price) 
		VALUES (@P_name,@P_desc,@P_qty,@P_price) 
	END
END

I prefer to use this pattern because a record is only inserted once but could be updated many times therefore it makes sense to assume that you will be updating the record.

If after attempting the UPDATE operation the @@ROWCOUNT = 0 it means it could not find a record to update there it will perform an INSERT operation.

这篇关于如何编写单个存储过程以进行插入和更新。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！