how can i use these two message output in C#

Question

0.00/5 (No votes)

See more:

ALTER Procedure [library].[updateisbn]
(
@Schoolid nvarchar(150),
@bookid int,
@id nvarchar(max),
@isbn nvarchar(max),
@shelfno nvarchar(max),
@msg nvarchar(max) output,
@msg1 nvarchar(max) output
)
as
begin
declare @pos int,@I int, @number int, @shelfcount int, @Spos int,@count int,@c nvarchar(max),@Pos1 int,@ii nvarchar(max),@s nvarchar(max),@Pos2 int
	set @Spos=1
	set @msg=''

set @I=1
set @count=  len(@isbn)-len(REPLACE(@isbn,',','')) 
while(@I<=@count)
	begin
		SET @Pos = CHARINDEX(',', @isbn, 1)
		SET @Pos1=CHARINDEX(',',@id,1)
		SET @Pos2=CHARINDEX(',',@shelfno,1)

		set @c = substring(@isbn,@Spos,@Pos-1)
		set @ii= substring(@id,@Spos,@Pos1-1)
		set @s=substring(@shelfno,@Spos,@Pos2-1)
		
		
    set @number =(select count(isbn) from library.bookISBNno where id=@ii and isbn=@c)
		
			if(@number=0)
			 begin			
			   set @shelfcount =(select count(shelfNo) from library.bookISBNno where isbn=@c and shelfNo=@s)			
			    if(@shelfcount=0)
			       begin
			       Update library.bookisbnno set isbn=@c,shelfno=@s where id=@ii and bookid=@bookid and schoolid=@Schoolid
					
					set @msg=@msg+''+@c+'Updated,'
					set @msg1=@msg+''+''+@s +''+'Updated,'
			       end
			    else
			       begin
			       Update library.bookisbnno set isbn=@c where id=@ii and bookid=@bookid and schoolid=@Schoolid
			        set @msg=@msg+''+@c+'Updated,'
					set @msg1=@msg+''+''+@s +''+'Exist,'
			     end
			  end
			else
			  begin
			   set @shelfcount =(select count(shelfNo) from library.bookISBNno where isbn=@c and shelfNo=@s)			
			    if(@shelfcount=0)
			       begin
			       Update library.bookisbnno set shelfno=@s where id=@ii and bookid=@bookid and schoolid=@Schoolid
			       			        
			        set @msg=@msg+''+@c+'Exist,'
					set @msg1=@msg+''+''+@s +''+'Updated,'
			       end
			    else
			       begin
			        set @msg=@msg+''+@c+'Exist,'
					set @msg1=@msg+''+''+@s +''+'Exist,'
			    
			     end
			end
					
	    set @isbn= stuff(@isbn, @Spos, @Pos,'')
		set @id=stuff(@id,@Spos,@Pos1,'')
		set @shelfno=stuff(@shelfno,@Spos,@Pos2,'')

		set @I=@I+1
	end
end

i am using this procedure for two output. procedure give the output but .. how can i use this in my C# program.

Posted 13-Jun-13 1:21am

Member 9693918

Updated 13-Jun-13 1:41am

v3

Add a Solution

Comments

Member 9693918 13-Jun-13 7:22am

Please give me the solution as soon as possible

[no name] 13-Jun-13 7:32am

Please improve whatever your question or problem is as soon as possible. A code dump is not a question or any kind of a description of a problem.

Shambhoo kumar 13-Jun-13 7:36am

please elaborate your question .

Shambhoo kumar 13-Jun-13 7:37am

I think You want two out parameter with your procedure.

Member 9693918 13-Jun-13 7:42am

yes... and its giving me.. but problem is that i dont know how can i use it. in c#

2 solutions

Solution 2

You can do this like

C#

CREATE PROCEDURE proc_name

(

@Parameter1    AS  INT OUTPUT,

@Parameter2    AS  INT OUTPUT

)

AS

BEGIN

--Now set this two parameters value inside

SET Parameter1 = 10

SET Parameter1 = 20

--At the end select your data from temp table...above two lines automatically return your parameters value and also return the result by the following query

SELECT * FROM #temp_table_name

END

And You can also use this code for same problem

Please check the below store procedure

C#

--created simple store procedure here
Create Procedure GetDataWithOuput
(
	@Out1 int output,
	@Out2 int output
)
As


Create Table #Temp
(
	Id int identity(1,1),
	Name Varchar(50)
)
Insert into #Temp
Select 'Yogesh'
Union All
Select 'Bhadauriya'


Set @Out1=1;
Set @Out1=2;


-

now call this store procedure as below C# Code..

private void GetDataWithOutput()
        {
            using (SqlConnection con = new SqlConnection("ConnectionString"))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "GetDataWithOuput"; //store procedure name
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add("@Out1",SqlDbType.Int).Direction=ParameterDirection.Output;
                    cmd.Parameters.Add("@Out2", SqlDbType.Int).Direction = ParameterDirection.Output;

                    using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adp.Fill(ds); //get select list from temp table

                        //get output param list
                        int Count1 = Convert.ToInt32(cmd.Parameters["@Out1"].Value);
                        int Count2 = Convert.ToInt32(cmd.Parameters["@Out2"].Value);
                    }
                }
            }
        }

Thanks & Regard
Sham

Posted 13-Jun-13 1:48am

Shambhoo kumar

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Savalia Manoj M · Accepted Answer · 2013-06-13T01:38:00

Hello..
In your C# code, In your SqlParameter collection, set the Direction to Output, and add it to the SqlCommand's Parameters collection.
Then execute the stored procedure and get the value of the parameter.

Using your code sample:

// SqlConnection and SqlCommand are IDisposable, so stack a couple using()'s
using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand("updateisbn", conn)
{
   // Create parameter with Direction as Output 
   SqlParameter outputMsg = new SqlParameter("@msg", SqlDbType.VarChar)
   { 
      Direction = ParameterDirection.Output 
   };

   SqlParameter outputMsg1 = new SqlParameter("@msg1", SqlDbType.VarChar)
   { 
      Direction = ParameterDirection.Output 
   };

   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Parameters.Add(outputMsg);
   cmd.Parameters.Add(outputMsg1);

   conn.Open();
   cmd.ExecuteNonQuery(); 
 
   string MsgStringOutput = int.Parse(outputMsg.Value.ToString());
   string MsgStringOutput1 = int.Parse(outputMsg1.Value.ToString());
 
   conn.Close();
}