CODEWITHSUNDEEP
c#asp.netsql-server-2008stored-procedures
Mahdi_Nine
Mahdi_Nine

Reputation: 14751

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

Upvotes: 7

Views: 27976

Answers (5)

Gregor
Gregor

Reputation: 388

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

Upvotes: 0

marc_s
marc_s

Reputation: 754598

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

Upvotes: 15

gavin
gavin

Reputation: 1346

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.

Upvotes: 0

Dustin Hodges
Dustin Hodges

Reputation: 4195

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

Upvotes: 5

user1228
user1228

Reputation:

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.

Upvotes: 1

Related Questions