我怎样才能在C＃中使用这两个消息输出 [英] how can i use these two message output in C#

问题描述

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我使用这个程序进行两次输出。程序给出输出，但..我怎么能在我的C＃程序中使用它。

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

推荐答案

你好..

在你的C＃代码中，在SqlParameter集合中，将Direction设置为Output，并将其添加到SqlCommand的Parameters集合中。

然后执行存储过程并获取参数的值。



使用代码示例：

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

你可以这样做

You can do this like

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

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

谢谢&注意

Sham

Thanks & Regard
Sham

这篇关于我怎样才能在C＃中使用这两个消息输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！