ADO.NET SqlCommand not returning correct value for SCOPE_IDENTITY()

Question

I have a simple table :

CREATE TABLE [MyTable]
(
    [ID] [INT] IDENTITY(1,1) NOT NULL,
    [NAME] [NVARCHAR](100) NOT NULL,
    [DATA] [NVARCHAR](max) NOT NULL,

    CONSTRAINT [PK_MyTable] 
        PRIMARY KEY CLUSTERED ([ID] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I have a stored procedure that inserts a row into this table and returns the value of the PK IDENTITY column via SCOPE_IDENTITY()

CREATE PROCEDURE [InsertMyTable] 
    @NAME NVARCHAR(100),
    @DATA NVARCHAR(MAX)  
AS
BEGIN
    INSERT INTO [MyTable] ([NAME], [DATA])
    VALUES (@NAME, @DATA)  

    RETURN SCOPE_IDENTITY()
END

I've tested this stored procedure via SSMS and other tools and it behaves as expected.

Each time the following script is run, the returned value increments by 1 :

DELETE FROM [MyTable]
GO

DECLARE @return INT

EXEC @return = [InsertMyTable] @NAME='MyName', @DATA='Data'

SELECT @return
GO

• First run returns 1
• Second run returns 2
• etc.

However when I perform analogous actions in C# code using System.Data.SqlClient the stored procedure return value is always 1. It doesn't seem to reflect that the identity is incremented each time the row is deleted and re-inserted.

    using (var connection = new SqlConnection({connection-string}))
    using (var command = connection.CreateCommand())
    {
        command.CommandType = CommandType.Text;
        command.CommandText = "delete from [MyTable] where [NAME]='MyName'";

        connection.Open();

        command.ExecuteNonQuery();
    }

    using (var connection = new SqlConnection({connection-string}))
    using (var command = connection.CreateCommand())
    {
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = "InsertMyTable";

        command.Parameters.Add(new SqlParameter($"@NAME", "MyName"));
        command.Parameters.Add(new SqlParameter($"@DATA", "data"));

        connection.Open();

        var returnValue = command.ExecuteNonQuery();
    }

• First run returnValue = 1
• Second run returnValue = 1
• etc.

Answer 1

Your stored procedure returns the ID as the stored procedure return value, which should only be used for success or failure, never for data.

SqlCommand.ExecuteNonQuery() returns the total of all the rowcount messages for the executed batch.

You should use an output parameter

CREATE PROCEDURE [InsertMyTable] 
  @NAME NVARCHAR(100)
, @DATA NVARCHAR(MAX) 
, @ID INT OUTPUT
AS
BEGIN
INSERT INTO [MyTable]
           ([NAME]
           ,[DATA])
     VALUES
           ( @NAME
           , @DATA)  
set @ID = SCOPE_IDENTITY()
END

or using a resultset, and get the value with SqlCommand.ExecuteScalar(), or SqlCommand.ExecuteReader().

CREATE PROCEDURE [InsertMyTable] 
  @NAME NVARCHAR(100)
, @DATA NVARCHAR(MAX)  
AS
BEGIN
INSERT INTO [MyTable]
           ([NAME]
           ,[DATA])
     VALUES
           ( @NAME
           , @DATA)  
SELECT SCOPE_IDENTITY() ID
END

You can make what you have work by binding an extra SqlParameter, but it's bad practice.