Return value from SQL Server Insert command using c#

Question

Using C# in Visual Studio, I'm inserting a row into a table like this:

INSERT INTO foo (column_name)
VALUES ('bar')

I want to do something like this, but I don't know the correct syntax:

INSERT INTO foo (column_name)
VALUES ('bar')
RETURNING foo_id

This would return the foo_id column from the newly inserted row.

Furthermore, even if I find the correct syntax for this, I have another problem: I have SqlDataReader and SqlDataAdapter at my disposal. As far as I know, the former is for reading data, the second is for manipulating data. When inserting a row with a return statement, I am both manipulating and reading data, so I'm not sure what to use. Maybe there's something entirely different I should use for this?

Longdrawnout

Answer 1

SCOPE_IDENTITY returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

You can use SqlCommand.ExecuteScalar to execute the insert command and retrieve the new ID in one query.

using (var con = new SqlConnection(ConnectionString)) {
    int newID;
    var cmd = "INSERT INTO foo (column_name)VALUES (@Value);SELECT CAST(scope_identity() AS int)";
    using (var insertCommand = new SqlCommand(cmd, con)) {
        insertCommand.Parameters.AddWithValue("@Value", "bar");
        con.Open();
        newID = (int)insertCommand.ExecuteScalar();
    }
}

Timbre

what a great example Tim, helped me a lot. Thanks for posting a question like this Neko, exactly what I was looking for! – Depository

made me an error characters found after end of sql statement – Belloc

Answer 2

try this:

INSERT INTO foo (column_name)
OUTPUT INSERTED.column_name,column_name,...
VALUES ('bar')

OUTPUT can return a result set (among other things), see: OUTPUT Clause (Transact-SQL). Also, if you insert multiple values (INSERT SELECT) this method will return one row per inserted row, where other methods will only return info on the last row.

working example:

declare @YourTable table (YourID int identity(1,1), YourCol1 varchar(5))

INSERT INTO @YourTable (YourCol1)
OUTPUT INSERTED.YourID
VALUES ('Bar')

OUTPUT:

YourID
-----------
1

(1 row(s) affected)

Nichani

This does not work when there are Triggers on the table. In that case you will get the following error: The target table of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause. Not sure what they mean with without INTO clause though, since my INSERT statement does have an INTO clause... – Grappling

@Louis Somers, just an plain OUTPUT clause will return a result set. If you add an INTO it will be inserted into a table. Create a table variable: DECLARE @Temp table (xyz int, abc varchar(5)) and you can have the OUTPUT rows go into this table, then after the INSERT you can just SELECT the @Temp table to return a result set. Use something like this: OUTPUT INSERTED.col1, INSERTED.col2 INTO @Temp then after the INSERT something like:` ;SELECT * FROM @Temp`. – Nichani

Answer 3

I think you can use @@IDENTITY for this, but I think there's some special rules/restrictions around it?

using (var con = new SqlConnection("connection string"))
{
    con.Open();
    string query = "INSERT INTO table (column) VALUES (@value)";

    var command = new SqlCommand(query, con);
    command.Parameters.Add("@value", value);
    command.ExecuteNonQuery();

    command.Parameters.Clear();
    command.CommandText = "SELECT @@IDENTITY";

    int identity = Convert.ToInt32(command.ExecuteScalar());
}

Gey

You should really use SCOPE_IDENTITY() and NOT @@IDENTITY - that might return surprisingly wrong results! – Figuration