Getting the ID of last inserted row from SQL using C#

Question

I have this code in my application, and it's working fine. I didn't use SCOPE_IDENTITY as I heard its old method. Can anybody check if I am doing the right method for getting last inserted Record ID ? Will this work If I add a transaction ?

using (SqlCommand com = new SqlCommand("INSERT INTO App_Users (UserName, Description) OUTPUT INSERTED.UserID " +
    "VALUES (@pUserName, @pDescription); ", con))
{


    com.Parameters.AddWithValue("@pUserName" ,userNameTxt.Text ); 

    com.Parameters.AddWithValue("@pDescription" , userDescription.Text); 



    int lastID = Convert.ToInt32(com.ExecuteScalar());

    foreach (ListViewItem lit in ListView3.Items)
    {
        HiddenField gid = (HiddenField)lit.FindControl("ListGroupID");
        int gidVal = 0;
        if (gid != null && gid.Value != null && !Int32.TryParse(gid.Value, out gidVal)) gidVal = 0;
        if (gidVal != 0)
        {
            com.CommandText = "INSERT INTO App_UserGroups (UserID, GroupID) " +
                "VALUES ( " +   lastID  + ", " + gidVal + ")";
            com.ExecuteNonQuery();
        }
    }

Why `mysql` tag by the way? `SCOPE_IDENTITY` is not old. Who said that? Have you ever try to use it? — Soner Gönül, Mar 31 '15 at 11:05
if your performing this directly after an insert, you can use IDENT_CURRENT('TableName') and return that value — horHAY, Mar 31 '15 at 11:07
@horHAY But Its not limited to the scope right ? I will get the last inserted ID from Other users too . — Yesudass Moses, Mar 31 '15 at 11:11
@SonerGönül I tried but, I had to use a select statement along with the insert statement, which I feel a bad practice. — Yesudass Moses, Mar 31 '15 at 11:15
By the way, you should always use [parameterized queries](http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/). This kind of string concatenations are open for [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection) attacks. — Soner Gönül, Mar 31 '15 at 11:16
You need to execute Insert and get the SCOPE_IDENTITY in the same scope. It can be a transaction, Procedure, PL Block etc. Check the definition https://msdn.microsoft.com/en-us/library/ms190315.aspx . If you make 2 round trips to database to insert and then get identity, nothing can guarantee the correct identity, unless there is only one user using the system. — wonderbell, Mar 31 '15 at 11:54

score 0 · Answer 1 · answered Mar 31 '15 at 12:16

0

you can use scope_identity:

using (SqlCommand com = new SqlCommand("INSERT INTO App_Users (UserName, Description) " + " VALUES (@pUserName, @pDescription);" + "SELECT CAST(scope_identity() AS int)" , con))

answered Mar 31 '15 at 12:16

Sandeep

1,182
3
11
26

score 0 · Answer 2 · edited May 23 '17 at 11:50

I ran into the same question a few days ago too and i've found this SO post explaining every way of retrieving the inserted id from the table.

To answer your question, both OUTPUT_CLAUSE and IDENT_CURRENT can be used in this scenario even if you add a transaction but i would recomment IDENT_CURRENT because an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back.

Use it as @horHay suggested in the comments as :

using (SqlCommand com = new SqlCommand("INSERT INTO App_Users (UserName, Description)"+ "VALUES (@pUserName, @pDescription) SELECT IDENT_CURRENT('App_Users'); ", con))

I would not suggest SCOPE_IDENTITY or @@IDENTITY because it may return wrong values (null) if you're not using SQL Server 2008 R2 SP2 or higher ( source - last row from the page.) especially for your requirement (inserting the value in some other table).

Getting the ID of last inserted row from SQL using C#

2 Answers2