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?

Tim Schmelter · Accepted Answer · 2012-02-16 22:06:57Z

71

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

edited Feb 16, 2012 at 22:06

answered Feb 16, 2012 at 21:49

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alberto Juliao O. Over a year ago

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

Shimon Doodkin Over a year ago

made me an error characters found after end of sql statement

KM. · Accepted Answer · 2012-02-16 21:47:18Z

13

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)

answered Feb 16, 2012 at 21:47

KM.

104k34 gold badges183 silver badges214 bronze badges

2 Comments

Louis Somers Over a year ago

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...

KM. Over a year ago

@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`.

Jesse · Accepted Answer · 2012-02-16 21:54:52Z

0

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

answered Feb 16, 2012 at 21:54

Jesse

3071 silver badge5 bronze badges

1 Comment

marc_s Over a year ago

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

Collectives™ on Stack Overflow

Return value from SQL Server Insert command using c#

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related