Get the ID of a Newly Inserted Row Using SCOPE_IDENTITY()

SCOPE_IDENTITY() command will give you the last identity value that was generated in the current scope.

@@Identity will return the last identity value that was generated in the current session but in any scope.

If you database got trigger to insert a new row, @@Identity will give you the id that generated by trigger instead of ID of the last row you inserted.

In order to get the ID of the row that you inserted, which is just what SCOPE_IDENTITY() will give you.

Below is the SQL to get the last inserted ID in the current scope that need to execute together with the commands.

string sqlString = "INSERT INTO Table(Col1, Col2) VALUES (@Col1, @Col2); SELECT SCOPE_IDENTITY () As NewID";
SqlCommand objCmd = new SqlCommand(sqlString, MyConnection);
objCmd.Connection.Open();
objCmd.Parameters.Add(new SqlParameter("@Col1", Col1Value));
objCmd.Parameters.Add(new SqlParameter("@Col2", Col2Value);

SqlDataReader dataReader = objCmd.ExecuteReader();

if (dataReader.HasRows)
{
dataReader.Read();
newRowID = Convert.ToInt32(dataReader["NewID"]);
}

dataReader.Close();