How to return Identity Column from SQL Server procedure in C# code

Question

I am looking to Insert a row into a table and return the Identity column in c# code. I cannot seem to get the syntax quite right.

Here is the storted procedure

ALTER PROCEDURE [dbo].[sp_InsertIssue]

       @Application                       nchar(20)      = NULL  , 
       @Version                     nchar(10)              = NULL    , 
       @CreatedBy                       NVARCHAR(30)       = NULL  , 
       @AssignedTo                    nVARCHAR(max)         = NULL  , 
       @Description                         nVARCHAR(max)   = NULL  , 
       @UserId                    INT                   
AS 
BEGIN 
     SET NOCOUNT ON 

     INSERT INTO dbo.Issue
          ( 

            Application                     ,
            Version                   ,
            CreatedBy                     ,
            AssignedTo                   ,
            Description                          ,
            UserId                  
          ) 

     VALUES 
          ( 

       @Application                         , 
       @Version                          , 
       @CreatedBy                       , 
       @AssignedTo                      , 
       @Description                           , 
       @UserId                  
          ) 

         RETURN SCOPE_IDENTITY() 
END

Here is the C# Code

SqlCommand cmd = new SqlCommand();

        cmd.CommandText = "sp_InsertIssue ";
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.Connection = m_Connection;

        SqlParameter parm = new SqlParameter("@IssueId", SqlDbType.Int);
        parm.Direction = ParameterDirection.ReturnValue;


        cmd.Parameters.Add("@Application", SqlDbType.VarChar).Value = p_Application;
        cmd.Parameters.Add("@Version", SqlDbType.VarChar).Value = p_Version;
        cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = p_CreatedBy;
        cmd.Parameters.Add("@AssignedTo", SqlDbType.VarChar).Value = p_AssignedTo;
        cmd.Parameters.Add("@Description", SqlDbType.VarChar).Value = p_Description;
        cmd.Parameters.Add("@UserId", SqlDbType.Int).Value = p_UserId;

        var returnParameter = cmd.Parameters.Add("IssueId", SqlDbType.Int);
        returnParameter.Direction = ParameterDirection.ReturnValue;

        /// send data to db
        Int32 id = (int)cmd.ExecuteNonQuery();

Return -1 not the identity column

http://stackoverflow.com/questions/13416811/fetch-scope-identity-value-in-c-sharp-code-from-stored-procedure-in-3-tier-archi — Jim Hewitt, Aug 01 '16 at 16:06
Two issues here. First you should NOT use the return value to return data. It is intended to return a status of the execution. You should use an OUTPUT parameter instead. Second issue is the sp_ prefix. You should change the prefix to something else, or even better remove the prefix entirely. http://sqlperformance.com/2012/10/t-sql-queries/sp_prefix — Sean Lange, Aug 01 '16 at 16:43

dana · Answer 1 · 2016-08-01T16:19:30.283

1

Changing my answer completely after reading your code more closely :) You were so close

Change:

Int32 id = (int)cmd.ExecuteNonQuery();

To:

cmd.ExecuteNonQuery(); // this returns the # of "rows affected"
Int32 id = (int)returnParameter.Value

edited Aug 01 '16 at 16:19

answered Aug 01 '16 at 16:05

dana

17,267
6
64
88

score 1 · Accepted Answer · answered Aug 01 '16 at 16:19

1

You have to make some changes :

SELECT SCOPE_IDENTITY() is beter than Return SCOPE_IDENTITY()
in your code you have to change also cmd.ExecuteNonQuery() with int id = Convert.ToInt32(cmd.ExecuteScalar());

answered Aug 01 '16 at 16:19

user3764415

91
5

How to return Identity Column from SQL Server procedure in C# code

2 Answers2