T-SQL: returning the new INSERT identity to C#

Question

i'm putting values into SQL Server using a Stored Procedure. The Procedure will add an ID to the row that is added. I need to get this ID back to my code.

Currently I can get the I see the output id in the OUTPUT window of Visual Studio, but can't seem to capture it in my code. Here is a summarized version of the proc:

SQL:

CREATE PROCEDURE dbo.DoSomething
(
    @var1 INT = NULL,
    @var2 INT = NULL,
    @var3 DATE = NULL
)
AS

BEGIN

    INSERT INTO atable
    (
        vara,
        varb,
        varc
    )
    VALUES
    (
        @var1,
        @var2,
        @var3
    )

    RETURN SCOPE_IDENTITY()

END

C#:

int result = 0;

/// create command
SqlCommand cmd = new SqlCommand("DoSomething", this.OpenSqlConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@var1", thing.value1);
cmd.Parameters.AddWithValue("@var2", thing.value2);
cmd.Parameters.AddWithValue("@var3", thing.value3);


/// send data to db
result = (int)cmd.ExecuteScalar();

So I'm getting an error: Object reference not set to an instance of an object. when it gets to the (int)cmd.ExecuteScalar().

Any ideas?

Answer 1

...
SELECT SCOPE_IDENTITY()

The RETURN value actually comes back as an special OUTPUT parameter. SELECT gives a result set for ExecuteScalar.

You can use the OUTPUT clause instead of a separate SELECT too:

...
AS
BEGIN
    INSERT INTO atable
    (
        vara,
        varb,
        varc
    )
    OUTPUT INSERTED.IDCol
    VALUES
    (
        @var1,
        @var2,
        @var3
    )
END
GO

This will work for multiple rows too.

Answer 2

You need to add another parameter that has a direction of ReturnValue:

var param = cmd.Parameters.Create("@ReturnValue");
param.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(param);

The value of this parameter after execution will be the return value:

var newId = cmd.Parameters["@ReturnValue"].Value;

Or

var newId = param.Value;

Answer 3

SqlParameter retval = cmd.Parameters.Add("@returnValue", SqlDbType.Int); 
            retval.Direction = ParameterDirection.ReturnValue;