如何使用存储过程编写插入更新删除的查询....它应该在单个存储过程中。以及如何在.cs文件中使用此(存储过程)进行插入和更新 [英] how to write the query for insert update delete by using stored procedure....it should be in single stored procedure.and how to use this(stored procedure) for both insert and update in .cs file
问题描述
如何使用存储过程编写插入更新删除的查询....它应该在单个存储过程中...
以及如何在代码隐藏文件中使用该存储过程( .cs)...
这里我已分别为插入和更新编写了查询...
我的疑问是如何使用这个代码插入和更新后面的代码(.cs)...
创建 proc sp_userinformation
(
@ username varchar ( 20 ),
@ password varchar ( 20 ),
@ firstname varchar ( 20 ),
@ lastname VARCHAR ( 20 ),
@ email varchar ( 20 ),
@ phoneno varchar ( 20 ),
@ location varchar ( 15 ),
@ created_by < span class =code-keyword> varchar ( 20 ),
@ ERROR char ( 500 )输出)
as
insert into userinformation(用户名,密码,名字,姓氏,电子邮件,phoneno,location,created_by)值( @ username , @ password , @ firstname , @ lastname , @ email , @ phoneno , @ location , @ created_by )
set @ ERROR = ' 已成功插入'
创建 proc sp_userinformation1
(
@ username < span class =code-keyword> varchar ( 20 ),
@ password varchar ( 20 ),
@名字 varchar ( 20 ),
@ lastname varchar ( 20 ),
@ email varchar (< span class =code-digit> 20 ),
@ phoneno varchar ( 20 ),
@ location varchar ( 15 ),
@ created_by varchar ( 20 ),
@ ERROR char ( 500 ) output )
as
update userinformation set
username = @ userna我,密码= @密码,firstname = @ firstname,lastname = @ lastname ,, location = @ location,created_by = @ created_by 其中 email = @ email 或 phoneno = @ phoneno
set @ ERROR = ' 已成功更新'
protected void btnsubmit_Click( object sender,EventArgs e)
{
< span class =code-keyword> string UserName = txtuser.Text;
string 密码= txtpwd.Text;
string ConfirmPassword = txtcnmpwd.Text;
string FirstName = txtfname.Text;
string LastName = txtlname.Text;
string Email = txtEmail.Text;
string Phoneno = txtphone.Text;
string Location = txtlocation.Text;
string Created_By = txtuser.Text;
int count = 0 ;
SqlConnection con = new SqlConnection( 数据Source =(local); Initial Catalog = sample; Integrated Security = True);
con.Open();
SqlCommand cmd = new SqlCommand( sp_userinformation ,con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue( @ UserName,UserName);
cmd.Parameters.AddWithValue( @ Password,密码);
cmd.Parameters.AddWithValue( @ FirstName,FirstName);
cmd.Parameters.AddWithValue( @ LastName,LastName);
cmd.Parameters.AddWithValue( @ Email,电子邮件);
cmd.Parameters.AddWithValue( @ PhoneNo,Phoneno);
cmd.Parameters.AddWithValue( @ Location,Location);
cmd.Parameters.AddWithValue( @ Created_By,Created_By);
cmd.Parameters.Add( @ ERROR,SqlDbType.Char, 500 跨度>);
cmd.Parameters [ @ ERROR]。Direction = ParameterDirection.Output;
count = cmd.ExecuteNonQuery();
message =( string )cmd.Parameters [ @ERROR跨度>]值。
con.Close();
Page.RegisterStartupScript( UserMsg, <脚本语言='javascript'>提醒(' + < span class =code-string>成功插入值 + ');< / script>)
lblErrorMsg.Text = message;
}
这里我写了插入查询...
我怀疑是如何在代码隐藏文件(.cs)中使用此编码进行插入和更新...
请帮助我...
已添加代码块[/编辑]
您好,
来自前端通行证一个指标要更新或插入然后使用SP中的指标并在同一个SP中执行。
create proc sp_userinformation
(
@ username varchar ( 20 ),
@ password varchar ( 20 ),
@ firstname varchar ( 20 ),
@ lastname varchar ( 20 ),
@ email varchar ( 20 ),
@ phoneno varchar ( 20 ),
@ location varchar ( 15 ),
@ created_by varchar ( 20 ),
@ ERROR char ( 500 )输出),
@ indicator char ( 1 ) - --- U-update和I - 插入
if @ indicator = ' 我'
开始
插入 进入用户信息(用户名,密码) ,名字,姓氏,电子邮件,phoneno,location,created_by)值( @ username , @ password , @ firstname , @ lastname , @ email , @ phoneno , @ location , @ created_by )
set @ ERROR = ' 成功插入'
end
如果 @ indicator = ' U'
开始
更新用户信息 set
username = @ username,password = @ password,firstname = @ firstname,lastname = @ lastname ,, location = @ location ,created_by = @ created_by 其中 email = @ email 或 phoneno = @ phoneno
set @ ERROR = ' 已成功更新'
end
已添加代码块[/ Edit]
how to write the query for insert update delete by using stored procedure....it should be in single stored procedure...
and how to use that stored procedure in code behind file(.cs)...
here i have written the query separately for insert and update...
My doubt is how to use this coding for both insert and update in code behind(.cs)...
create proc sp_userinformation
(
@username varchar(20),
@password varchar(20),
@firstname varchar(20),
@lastname varchar(20),
@email varchar(20),
@phoneno varchar(20),
@location varchar(15),
@created_by varchar(20),
@ERROR char(500) output)
as
insert into userinformation(username,password,firstname,lastname,email,phoneno,location,created_by)values (@username,@password,@firstname,@lastname,@email,@phoneno,@location,@created_by)
set @ERROR='Sucessfully Inserted'
create proc sp_userinformation1
(
@username varchar(20),
@password varchar(20),
@firstname varchar(20),
@lastname varchar(20),
@email varchar(20),
@phoneno varchar(20),
@location varchar(15),
@created_by varchar(20),
@ERROR char(500) output)
as
update userinformation set
username=@username,password=@password,firstname=@firstname, lastname =@lastname, ,location=@location,created_by=@created_by where email=@email or phoneno=@phoneno
set @ERROR='Sucessfully Updated'
protected void btnsubmit_Click(object sender, EventArgs e)
{
string UserName = txtuser.Text;
string Password = txtpwd.Text;
string ConfirmPassword = txtcnmpwd.Text;
string FirstName = txtfname.Text;
string LastName = txtlname.Text;
string Email = txtEmail.Text;
string Phoneno = txtphone.Text;
string Location = txtlocation.Text;
string Created_By = txtuser.Text;
int count = 0;
SqlConnection con = new SqlConnection("Data Source=(local);Initial Catalog=sample;Integrated Security=True");
con.Open();
SqlCommand cmd = new SqlCommand("sp_userinformation", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserName", UserName);
cmd.Parameters.AddWithValue("@Password", Password);
cmd.Parameters.AddWithValue("@FirstName", FirstName);
cmd.Parameters.AddWithValue("@LastName", LastName);
cmd.Parameters.AddWithValue("@Email", Email);
cmd.Parameters.AddWithValue("@PhoneNo", Phoneno);
cmd.Parameters.AddWithValue("@Location", Location);
cmd.Parameters.AddWithValue("@Created_By", Created_By);
cmd.Parameters.Add("@ERROR", SqlDbType.Char, 500);
cmd.Parameters["@ERROR"].Direction = ParameterDirection.Output;
count = cmd.ExecuteNonQuery();
message = (string) cmd.Parameters["@ERROR"].Value;
con.Close();
Page.RegisterStartupScript("UserMsg", "<Script language='javascript'>alert('" + "The Values are inserted Successfully " + "');</script>")
lblErrorMsg.Text = message;
}
here i have written the query for insert...
My doubt is how to use this coding for both insert and update in code behind file(.cs)...
Please help me...
[Edit]Code block added[/Edit]
Hi,
From front end pass one indicator to update or insert then use the indicator in the SP and do it in same SP.
create proc sp_userinformation ( @username varchar(20), @password varchar(20), @firstname varchar(20), @lastname varchar(20), @email varchar(20), @phoneno varchar(20), @location varchar(15), @created_by varchar(20), @ERROR char(500) output), @indicator char(1)-----U-update and I--Insert as if @indicator='I' begin insert into userinformation(username,password,firstname,lastname,email,phoneno,location,created_by)values (@username,@password,@firstname,@lastname,@email,@phoneno,@location,@created_by) set @ERROR='Sucessfully Inserted' end If @indicator='U' begin update userinformation set username=@username,password=@password,firstname=@firstname, lastname =@lastname, ,location=@location,created_by=@created_by where email=@email or phoneno=@phoneno set @ERROR='Sucessfully Updated' end
[Edit]Code block added[/Edit]
这篇关于如何使用存储过程编写插入更新删除的查询....它应该在单个存储过程中。以及如何在.cs文件中使用此(存储过程)进行插入和更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!