8

I'm working on an ASP.NET project (C#) with SQL Server 2008.

When I insert a row into a table in the database, I would like to get the last inserted ID, which is the table's IDENTITY (Auto Incremented).

I do not wish to use another query, and do something like...

SELECT MAX(ID) FROM USERS;

Because - even though it's only one query - it feels lame...

When I insert something I usually use ExecuteNonQuery(), which returns the number of affected rows.

int y = Command.ExecuteNonQuery();

Isn't there a way to return the last inserted ID without using another query?

Ali Bassam
  • 9,691
  • 23
  • 67
  • 117
  • 2
    http://stackoverflow.com/a/9319609/284240 – Tim Schmelter Jan 12 '13 at 23:40
  • 2
    For anyone who is wondering why Select MAX(ID) is a bad idea, is because it creates a race condition. If someone else inserts at the same time, the second query may execute AFTER the next insert, giving back the wrong ID – Serguei Fedorov Sep 15 '13 at 23:11

4 Answers4

18

Most folks do this in the following way:

INSERT dbo.Users(Username)
VALUES('my new name');

SELECT NewID = SCOPE_IDENTITY();

(Or instead of a query, assigning that to a variable.)

So it's not really two queries against the table...

However there is also the following way:

INSERT dbo.Users(Username)
OUTPUT inserted.ID
VALUES('my new name');

You won't really be able to retrieve this with ExecuteNonQuery, though.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
5

You can return the id as an output parameter from the stored procedure, e.g. @userId int output

Then, after the insert, SET @userId = scope_identity()

levelnis
  • 7,665
  • 6
  • 37
  • 61
  • -1. A stored procedure instead of - just asking for the value. Gratulations, you jsut generated a maintenance nightmare. – TomTom Jan 12 '13 at 23:40
  • 1
    @TomTom I'm not sure I agree that a stored procedure is a maintenance nightmare. It all depends on whether you want to make SQL changes inside the stored procedure or make changes in ad hoc code in your application, recompile, redeploy, etc. – Aaron Bertrand Jan 12 '13 at 23:41
  • 3
    @TomTom I don't use LINQ (I'm not a developer) but I've done plenty of performance troubleshooting for folks who have. And I'm not talking about testing at all. Are you honestly going to tell me that if the DBA needs to make changes to the SQL logic (let's say, add an index hint, or there has been a schema change that the query needs to incorporate), that deploying those changes in C# is going to be easier? Why does the app developer and the T-SQL author have to be the same person? – Aaron Bertrand Jan 13 '13 at 00:14
  • 7
    @TomTom Anyway I think that's a pretty lame reason to give the guy a -1. Just because you don't like stored procedures doesn't mean we should all abandon them and jump on the ORM train, and that anyone who suggests using one is creating nightmares for people. – Aaron Bertrand Jan 13 '13 at 00:14
3
  • even though it's only one query - it feels lame...

It actually is also wrong as you can have multiple overlapping iserts.

That is one thing that I always fuind funny - people not reading the documentation.

SELECT SCOPE_IDENTITY()

returns the last identity value generated in a specific scope and is syntactically correct. It also is properly documented.

Isn't there a way to return the last inserted ID without using another query?

Yes. Ask for the number in the saame SQL batch.

INSERT (blablab9a); SELECT SCOPE_IDENTITY ();

as ONE string. ExecuteScalar.

You can have more than one SQL statement in one batch.

TomTom
  • 61,059
  • 10
  • 88
  • 148
  • 1
    Can you edit your answer a little bit so it can be more C# friendly? Because I'm imagining an SqlReader now in my head... how will I be able to read the SELECT SCORE_IDENTITY() ? – Ali Bassam Jan 12 '13 at 23:42
  • @AliBassam By learning C#. ExecuteScalar returns a value that is the first column of the first rowlNo need for you to handle a SqlReader. Nothing "C# friendly" in knowing the basics of ADO.NET, you know. http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx – TomTom Jan 12 '13 at 23:45
2

If you want to execute query from C# code & want to get last inserted id then you have to find the following code.

SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        connection.Open();
        string sql = "Insert into [Order] (customer_id) values (" + Session["Customer_id"] + "); SELECT SCOPE_IDENTITY()";
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = connection;
        cmd.CommandText = sql;
        cmd.CommandType = CommandType.Text;
        var order_id =  cmd.ExecuteScalar();

        connection.Close();
        Console.Write(order_id);
Sapnandu
  • 620
  • 7
  • 9