How to handle output parameter of sp in asp.net

See more:

ASP.NET

SQL-Server

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;
    
	}

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..

      */

     	
	}

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...