Retrieving SQL Server output variables in c#

I have a stored procedure:

ALTER PROCEDURE [dbo].[pr_Tbl_Test_Insert]
    @guidid uniqueidentifier output,
    @sname nvarchar(50)
AS
-- INSERT a new row in the table.
INSERT [dbo].[Tbl_Test]
(
    [id],
    [name]
)
VALUES
(
    ISNULl(@guidid, (newid())),
    @sname
)

I need the id in C# and put it output in c#:
cmd.Parameters.AddWithValue("@guidid",_id);//_id is SqlGuid

cmd.Parameters.AddWithValue("@sname", "mehdi");

cmd.ExecuteNonQuery();
MessageBox.Show(_id.ToString());

but messagebox show the null value!!

How can I return the id?

I changed it to:

ALTER PROCEDURE [dbo].[pr_Tbl_Test_Insert] 
  @guidid uniqueidentifier output, 
  @sname nvarchar(50) 
AS 

DECLARE @NewID UNIQUEIDENTIFIER 
SET @NewID = newid(); 

-- INSERT a new row in the table. 
INSERT [dbo].[Tbl_Test]([id], [name]) VALUES(@NewID, @sname); 

SET @guidid = @NewID

and C#
SqlParameter outparam = cmd.Parameters.Add("@guidid",SqlDbType.UniqueIdentifier);
outparam.Direction = ParameterDirection.Output;

cmd.Parameters.AddWithValue("@sname", "mehdi");

cmd.ExecuteNonQuery();
MessageBox.Show(_id.Value.ToString());

but it doesn’t return anything

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

First of all – if it’s an OUTPUT parameter, you cannot use .AddWithValue in C# – you need to use:

SqlParameter outParam = cmd.Parameters.Add("@guidid", SqlDbType.Uniqueidentifier);
outParam.Direction = ParameterDirection.Output;

and also, in your T-SQL code, you need to assign the new value to the output parameter!
ALTER PROCEDURE [dbo].[pr_Tbl_Test_Insert]
  @guidid uniqueidentifier output,
  @sname nvarchar(50)
AS

DECLARE @NewID UNIQUEIDENTIFIER
SET @NewID = newid();

-- INSERT a new row in the table.
INSERT [dbo].[Tbl_Test]([id], [name]) VALUES(@NewID, @sname);

SET @guidid = @NewID

Update: if you run this in your SQL Server Mgmt Studio – does it show anything??
DECLARE @insertedID UNIQUEIDENTIFIER

EXEC dbo.pr_Tbl_Test_Insert @guidid = @insertedID OUTPUT,
                            @sname = N'TestUser' -- nvarchar(50)

SELECT @insertedID

and in your C# – you have to read out the value of the output parameter after calling ExecuteNonQuery!
SqlParameter outparam = cmd.Parameters.Add("@guidid",SqlDbType.UniqueIdentifier);
outparam.Direction = ParameterDirection.Output;

cmd.Parameters.AddWithValue("@sname", "mehdi");

cmd.ExecuteNonQuery();

Guid newlyInsertedID = new Guid(cmd.Parameters["@guidid"].Value);
MessageBox.Show(newlyInsertedID.ToString());

Method 2

Before you execute the query you need to specify the direction of the parameter, in this case output. e.g.:

cmd.Parameters.AddWithValue("@guidid",_id);//_id is SqlGuid

cmd.Parameters.AddWithValue("@sname", "mehdi");

cmd.Parameters["@guidid"].Direction = ParameterDirection.Output

cmd.ExecuteNonQuery();
MessageBox.Show(cmd.Parameters["@guidid"].Value.ToString());

Method 3

You need to construct a SqlParameter using one of the constructors that lets you specify a ParameterDirection, such as this one. Alternatively, construct your parameter and then set the direction using the Direction property.

Check this link on MSDN for more information.

Method 4

Why are you setting the @guidid uniqueidentifier output as an output parameter? It means it will override it once you execute the stored procedure. If that’s your intention, then you need to add a statement after the insert statement to set the output parameter to the value you want. something like this: select @guidid = @generatedID. Yeah look at marc_s code, that’s the way you are supposed to do it.

Method 5

I also found this very frustrating and I could not understand the issue. Although many answers are correct, there was one simple line that was often overlooked by me and others, namely the command needs to be store procedure not just any sql with parameters, so I hope this helps:

           cmd.CommandType = CommandType.StoredProcedure;

cmd.Txt should look like this:
           @"my_stored_proct "

NOT
           @"my_stored_proct @p1, @p2, @p3 out"

So putting it all together. You might want to separate it into several methods. and add TimeOuts etc. However these are what I think are the critical parts that differ from other commands witout output Parameters.
      using (SqlCommand cmd= new SqlCommand())
      {
           cmd.Text= ...;
           cmd.CommandType = CommandType.StoredProcedure;
           SqlParameter outParam = cmd.Parameters.Add("@guidid", SqlDbType.Uniqueidentifier);
           outParam.Direction = ParameterDirection.Output;

           using (var connection = new SqlConnection(this.myConnectionString))
           {
            connection.Open();
            cmd.Connection = connection;

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch
            {
             //    put your sql catches etc. here..
              throw;
            }  
          }  
       var outValue = outParam.Value; 
       //query outValue e.g. ToString()   
       }


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x