SQL Server and C#: get last inserted id

Question

public static void CreateSocialGroup(string FBUID)
{
    string query = "INSERT INTO SocialGroup (created_by_fbuid) VALUES (@FBUID); SELECT @@IDENTITY AS LastID";

    using (SqlConnection connection = new SqlConnection(ConnectionString))
    {
        SqlCommand command = new SqlCommand(query, connection);
        command.Parameters.AddWithValue("@FBUID", FBUID);

        connection.Open();
        command.ExecuteNonQuery();
    }
}

Is this the right way to do it? And how do i get LastID in to a variable? Thanks

possible duplicate of [How to get last inserted id?](http://stackoverflow.com/questions/5228780/how-to-get-last-inserted-id) — 200_success, Aug 13 '14 at 08:07

score 26 · Answer 1 · answered May 24 '11 at 11:41

26

OUTPUT clause?

string query = "INSERT INTO SocialGroup (created_by_fbuid) 
                OUTPUT INSERTED.IDCol  --use real column here
                VALUES (@FBUID)";
...
int lastId = (int)command.ExecuteScalar();

answered May 24 '11 at 11:41

gbn

422,506
82
585
676

Works, but i get 2 rows inserted, why? – Johan May 24 '11 at 11:52
@Johan: it is being called twice somewhere. The SQL given inserts one row only. – gbn May 24 '11 at 11:55
1

it might be better to use SCOPE_IDENTITY() rather than OUTPUT as OUTPUT will give you errors in case you have triggers set on the table (as stated on [this question link](http://stackoverflow.com/questions/330707/how-to-insert-a-record-and-return-the-newly-created-id-using-a-single-sqlcommand)) – marquito Jul 31 '12 at 12:23

score 10 · Answer 2 · answered May 24 '11 at 11:29

10

You can use ExecuteScalar to get the last value from a Sqlcommand.

The scope_identity() function is safer than @@identity.

answered May 24 '11 at 11:29

faester

14,886
5
45
56

score 8 · Answer 3 · answered May 24 '11 at 11:44

If your server supports the OUTPUT clause you could try it with this one:

public static void CreateSocialGroup(string FBUID)
{
    string query = "INSERT INTO SocialGroup (created_by_fbuid) OUTPUT INSERTED.IDENTITYCOL VALUES (@FBUID)";

    using (SqlConnection connection = new SqlConnection(ConnectionString))
    {
        SqlCommand command = new SqlCommand(query, connection);
        command.Parameters.AddWithValue("@FBUID", FBUID);

        connection.Open();
        var _id = command.ExecuteScalar();
    }
}

INSERTED.IDENTITYCOL doesn't work in SQL Azure. However if your identity column is named for example 'ID' then INSERTED.ID works! — Adrian Toman, Jul 07 '12 at 01:58

score 5 · Answer 4 · answered May 24 '11 at 11:36

Personally, I would re-write your code to use Parameters. You could either use an InputOutput parameter or an Output Parameter. However, using a Return Value in your SQL would also work.

Full examples on this can be found on MSDN.

I would also use Scope_Identity() rather than @@Identity this will ensure that you will reveice the ID that relates to the current transaction. Details on Scope_Identity can be found here.

score 3 · Answer 5 · answered May 24 '11 at 11:26

3

U can try ExecuteScalar for getting the LastID value.

answered May 24 '11 at 11:26

varadarajan

514
1
3
9

Works, but i get 2 rows inserted, why? – Johan May 24 '11 at 11:40

score 2 · Answer 6 · answered May 24 '11 at 11:33

2

I'd recommend to use a stored procedure to do this. You can give it an OUTPUT parameter which you can use to return the id value back to your app.

answered May 24 '11 at 11:33

takrl

6,356
3
60
69

score 0 · Answer 7 · edited Aug 01 '19 at 00:16

0

cmd = new SqlCommand("Insert into table values (1,2,3); SELECT SCOPE_IDENTITY()", conn);

lastRecord = cmd.ExecuteScalar().ToString();

edited Aug 01 '19 at 00:16

AS Mackay

2,831
9
19
25

answered Jul 31 '19 at 23:59

Profesor

1
3

Pankaj · Answer 8 · 2011-05-24T12:00:26.700

-3

Use Stored Procedure only for the queries and use SCOPE_IDENTITY to get max value.

edited May 24 '11 at 12:00

answered May 24 '11 at 11:35

Pankaj

9,749
32
139
283

3

This is parameterised already. The `Parameters.AddWithValue` tells us this. – gbn May 24 '11 at 11:54
While writing the queries and sending to network can produce congestion. Like OP wrote "string query = "INSERT INTO SocialGroup (created_by_fbuid) VALUES (@FBUID); SELECT @@IDENTITY AS LastID"; " This is not a good practice. This should be in stored procedure – Pankaj May 24 '11 at 11:57
2

do you have an authoritative reference to describe that behaviour? A stored proc call is still sent over the network too. – gbn May 24 '11 at 12:43
Thank you for 30 downvotes: you really shouldn't post in public if you can't deal with criiticsm. Note: your rep profile gives this downvoting away so don't deny it... FYI: http://meta.stackexchange.com/questions/28756 – gbn May 24 '11 at 13:18

score -16 · Answer 9 · edited May 24 '11 at 11:37

-16

SqlCommand command = new SqlCommand("select max(id) from SocialGroup ", connection);
int lastId = (int)command.ExecuteScalar();

edited May 24 '11 at 11:37

Arjan Einbu

13,543
2
56
59

answered May 24 '11 at 11:27

maephisto

4,952
11
53
73

2

MAX(id) is probably not the right way. SCOPE_IDENTITY() as suggested elsewhere is better. – Arjan Einbu May 24 '11 at 11:36
2

-1 for possibly the worst answer I've seen to this common questions – gbn May 24 '11 at 11:39
6

-1 Also. Just in case you are unsure of the problem a concurrent transaction could perform an insert and thus your select returns the wrong result. – Martin Smith May 24 '11 at 11:56

SQL Server and C#: get last inserted id

9 Answers9

Linked