如何在asp.net中处理sp的输出参数 [英] How to handle output parameter of sp in asp.net
问题描述
大家好,
我对如何在asp.net中使用sp的输出参数有疑问。
(我我正在使用sql server 2008和vs-4.0 c#)
这里我有两个案例。第一个工作正常,但我没有得到如何处理第二个...
案例1:
Hi all,
I am having doubt in how to use the output parameter of sp in asp.net.
(I am using sql server 2008 and vs-4.0 c#)
here I am having two cases. 1st is working fine but I am not getting how to handle 2nd one..
case 1:
------------------SP------------
ALTER proc [dbo].[adduser]
(
@Fname Varchar(20),
@Lname Varchar(20),
@Uid Varchar(20),
@Password Varchar(255),
@Role Varchar(20),
@createdby varchar(10)
)
AS
BEGIN
if not exists( select top 1 * from UserLogin(nolock) where UserId=@Uid)
begin
insert into UserLogin(F_Name,L_Name,UserId,Password,Role,CreatedOn,CreatedBy)
values(@Fname,@Lname,@Uid,@Password,@Role,GETDATE(),@createdby);
end
end
----------------- Code------------
public int Adduser(string F_Name,string L_Name,string UserId,string Password,string Role,string CreatedBy)
{
SqlConnection conn = new SqlConnection(con.getConnection());
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.adduser";
cmd.Parameters.AddWithValue("@Fname", F_Name);
cmd.Parameters.AddWithValue("@Lname", L_Name);
cmd.Parameters.AddWithValue("@Uid", UserId);
cmd.Parameters.AddWithValue("@Password", Password);
cmd.Parameters.AddWithValue("@Role", Role);
cmd.Parameters.AddWithValue("@Createdby", CreatedBy);
cmd.Connection = conn;
int res = cmd.ExecuteNonQuery();
return res;
}
以上是工作正常,但如果我改变SP为......
案例2:
Above is working fine but if I change the SP as ...
Case 2:
------------SP-----------------
ALTER proc [dbo].[adduser_test]
(
@Fname Varchar(20),
@Lname Varchar(20),
@Uid Varchar(20),
@Password Varchar(255),
@Role Varchar(20),
@createdby varchar(10),
@out varchar(30) output
)
AS
BEGIN
if not exists( select top 1 * from UserLogin(nolock) where UserId=@Uid)
begin
insert into UserLogin(F_Name,L_Name,UserId,Password,Role,CreatedOn,CreatedBy)
values(@Fname,@Lname,@Uid,@Password,@Role,GETDATE(),@createdby);
set @out='Inserted'
end
else
set @out='Not inserted'
end
-------------Code------------------------
public string Adduser(string F_Name,string L_Name,string UserId,string Password,string Role,string CreatedBy)
{
SqlConnection conn = new SqlConnection(con.getConnection());
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.adduser_test";
cmd.Parameters.AddWithValue("@Fname", F_Name);
cmd.Parameters.AddWithValue("@Lname", L_Name);
cmd.Parameters.AddWithValue("@Uid", UserId);
cmd.Parameters.AddWithValue("@Password", Password);
cmd.Parameters.AddWithValue("@Role", Role);
cmd.Parameters.AddWithValue("@Createdby", CreatedBy);
SqlParameter @out = cmd.Parameters.Add("@Out",string);
@out.Direction = ParameterDirection.Output;
cmd.Connection = conn;
/* What do i need to write here ??
int res = cmd.ExecuteNonQuery();
@out=cmd.Parameters["@Out"].Value.ToString();
This is not right i know, but not getting how to handle the output..
*/
}
我也试过谷歌搜索但没有得到满意的答案。
将是btr,如果有人可以提供任何链接,因为我不想只有解决方案,但也没有更多的知识..
提前谢谢...
I have tried googling also but not getting satisfactory answer.
would be btr if anyone can provide any link also as i don't want only solution but also little more knowledge about it..
thanks in advance...
推荐答案
我在你的存储过程中没有看到任何输出参数....如果你想要@out作为输出参数你有将其指定为
I don't see any Output parameter in your Stored Procedure.... if you want @out as Output Parameter u have to Specify it as
ALTER proc [dbo].[adduser_test]
(
@Fname Varchar(20),
@Lname Varchar(20),
@Uid Varchar(20),
@Password Varchar(255),
@Role Varchar(20),
@createdby varchar(10),
@out varchar(30) OUTPUT
查看此链接:
http://msdn.microsoft.com/en-US/library/ms378108(v = sql.90).aspx
http://msdn.microsoft.com/en- US / library / ms378371(v = sql.90).aspx
我已经解决了它..
Hi,
I have solved it..
public string Adduser(string F_Name,string L_Name,string UserId,string Password,string Role,string CreatedBy)
{
SqlConnection conn = new SqlConnection(con.getConnection());
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.adduser_test";
cmd.Parameters.AddWithValue("@Fname", F_Name);
cmd.Parameters.AddWithValue("@Lname", L_Name);
cmd.Parameters.AddWithValue("@Uid", UserId);
cmd.Parameters.AddWithValue("@Password", Password);
cmd.Parameters.AddWithValue("@Role", Role);
cmd.Parameters.AddWithValue("@Createdby", CreatedBy);
cmd.Parameters.Add("@Out", SqlDbType.Char, 500);
cmd.Parameters["@Out"].Direction = ParameterDirection.Output;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
string @out = (string)cmd.Parameters["@Out"].Value;
return @out;
现在工作正常..
and now its working fine..
这篇关于如何在asp.net中处理sp的输出参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!