T-SQL: returning new INSERT identifier to C #

Question

I put values ​​in SQL Server using a stored procedure. The procedure will add the identifier to the added row. I need to return this identifier to my code.

Currently, I can get the output identifier in the OUTPUT window of Visual Studio, but it seems I cannot display it in my code. Here is a generic version of 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 

WITH#

 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 get the error message: The object reference is not installed in the object instance. when it gets into (int) cmd.ExecuteScalar ().

Any ideas?

Answer 1

 ... SELECT SCOPE_IDENTITY() 

The RETURN value is indeed returned as a special OUTPUT parameter. SELECT gives the result for ExecuteScalar.

You can use OUTPUT instead of a separate SELECT:

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

This will work for multiple lines as well.

Answer 2

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

 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;