T-SQL: возврат нового идентификатора INSERT в C#

Question

Я помещаю значения в SQL Server с помощью хранимой процедуры. Процедура добавит идентификатор в добавляемую строку. Мне нужно вернуть этот идентификатор в мой код.

В настоящее время я могу получить выходной идентификатор в окне OUTPUT Visual Studio, но не могу зафиксировать его в своем коде. Вот краткая версия процедуры:

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

Поэтому я получаю сообщение об ошибке: ссылка на объект не установлена ​​на экземпляр объекта. когда он попадает в (int)cmd.ExecuteScalar().

Есть идеи?

Answer 1

...
SELECT SCOPE_IDENTITY()

Значение RETURN фактически возвращается как специальный параметр OUTPUT. SELECT дает набор результатов для ExecuteScalar.

Вы также можете использовать предложение OUTPUT вместо отдельного SELECT:

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

Это будет работать для нескольких строк тоже.

Answer 2

Вам нужно добавить еще один параметр, который имеет направление ReturnValue:

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

Значение этого параметра после выполнения будет возвращаемым значением:

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;