如何通过标志存储过程以在一个SP中执行插入，更新和删除操作? [英] how to pass flag to store procedure to perform insert,update and delete in one SP?

问题描述

你好，
我是SP的新手.我想在一个存储过程中执行插入，更新和删除操作.但是我不知道如何将标志或类似的东西传递给SP，SP知道此标志适用于此特定操作.

这是我的代码和存储过程:

hello,
i am newbie to SPs. i want to perform insert,update and delete operations in one stored procedure.but i don''t know how to pass flag or something like that to SP that SP knows that this flag is for this particular operation.

here is my code and store procedure:

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
   {
       con.Open();
       string id = grd.Rows[e.RowIndex].Cells[0].Text;
       //cust.deleteRecord(string id);
       //SqlCommand cmd = new SqlCommand("delete from tblEmp where id=@id", con);
       SqlCommand cmd = new SqlCommand("deleteRecord", con);
       cmd.CommandType = CommandType.StoredProcedure;
       cmd.Parameters.AddWithValue("@id", id);
       cmd.ExecuteNonQuery();
       grd.EditIndex=-1;
       con.Close();
       binddata();
   }


protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        con.Open();
        string id = grd.Rows[e.RowIndex].Cells[0].Text;
        string Name = ((TextBox)grd.Rows[e.RowIndex].Cells[1].Controls[0]).Text;
        string City = ((TextBox)grd.Rows[e.RowIndex].Cells[2].Controls[0]).Text;
        string State = ((TextBox)grd.Rows[e.RowIndex].Cells[3].Controls[0]).Text;
        string Salary = ((TextBox)grd.Rows[e.RowIndex].Cells[4].Controls[0]).Text;
        //SqlCommand cmd = new SqlCommand("update tblEmp set Name=@Name,City=@City,State=@State,Salary=@Salary where id=@id", con);
        SqlCommand cmd = new SqlCommand("updateRecord", con);
        cmd.CommandType = CommandType.StoredProcedure;
        //cust.updateRecord(string id, string Name,string City,string State,string Salary);
        cmd.Parameters.AddWithValue("@id", id);
        cmd.Parameters.AddWithValue("@Name", Name);
        cmd.Parameters.AddWithValue("@City", City);
        cmd.Parameters.AddWithValue("@State", State);
        cmd.Parameters.AddWithValue("@Salary", Salary);
        cmd.ExecuteNonQuery();
        grd.EditIndex = -1;
        con.Close();
        binddata();
    }

ALTER PROCEDURE [dbo].[operations]
@Id int,
@Name varchar(20),
@City varchar(20),
@State varchar(20),
@Salary int,
@Mode tinyint
as
Begin
	If @Mode = 1
		Begin
			insert into tblEmp(Name,City,[State],Salary) values(@Name,@City,@State,@Salary) 
		End
	If @Mode = 2
		Begin 
			update tblEmp
			set Name=@Name,City=@City,[State]=@State,Salary=@Salary where id=@id
		End
	If @Mode = 3
		Begin 
			delete from tblEmp where id=@id
		End
END

请帮助.

谢谢&问候，
克鲁纳尔·卡卡迪亚


[edit]添加了代码块-OriginalGriff [/edit]

kindly please help.

Thanks & Regards,
Krunal kakadiya


[edit]Code block added - OriginalGriff[/edit]

推荐答案

1)修改存储过程的参数，如下所示

1)modify parameters of store procedure as below

ALTER PROCEDURE [dbo].[operations]
@Id int,
@Name varchar(20)=Null,
@City varchar(20)=Null,
@State varchar(20)=Null,
@Salary int = 0,
@Mode tinyint=0

2)

为GridView1_RowInserting添加以下内容

2)

add following for GridView1_RowInserting

cmd.Parameters.AddWithValue("@Mode", 1);

为GridView1_RowUpdating添加以下内容

add following for GridView1_RowUpdating

cmd.Parameters.AddWithValue("@Mode", 2);

为GridView1_RowDeleting添加以下内容

add following for GridView1_RowDeleting

cmd.Parameters.AddWithValue("@Mode", 3);

这篇关于如何通过标志存储过程以在一个SP中执行插入，更新和删除操作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！