如何在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屋！