Get recent insert record id for update statement in ASP.NET with SQL Server-CodePudding

In my code first, I insert a record for a SQL table. then I want to update the same table after checking an API response. For that, I want to get the inserted record id and pass it to the update statement. I didn't find a way to get the id. Please give a solution for that. Thank you

using (SqlConnection sqlcon = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]))
{
    sqlcon.Open();
    string query = "INSERT INTO GOU VALUES(@OldPassword,@DateTime,@NewPassword,@IsSuccess,@IsActive)";
    SqlCommand sqlCommand = new SqlCommand(query, sqlcon);
    sqlCommand.Parameters.AddWithValue("@OldPassword", "oldPassword");
    sqlCommand.Parameters.AddWithValue("@DateTime", DateTime.Now);
    sqlCommand.Parameters.AddWithValue("@NewPassword", "");
    sqlCommand.Parameters.AddWithValue("@IsSuccess", 0);
    sqlCommand.Parameters.AddWithValue("@IsActive", 0);
    sqlCommand.ExecuteNonQuery();                                

}
bool Apirespone = getResponse();
if (Apirespone == true)
{
    using (SqlConnection sqlCon = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]))
    {
        sqlCon.Open();
        string query = "UPDATE GOU SET IsSuccess = @IsSuccess,IsActive = @IsActive where Id = @id";
        SqlCommand sqlCommand = new SqlCommand(query, sqlCon);
        sqlCommand.Parameters.AddWithValue("@IsSuccess", 1);
        sqlCommand.Parameters.AddWithValue("@IsActive", 1);
        sqlCommand.Parameters.AddWithValue("@Id", ????);//get recent added record id
        sqlCommand.ExecuteNonQuery();
    }

}

CodePudding user response：

You can use "SCOPE_IDENTITY()" for get the last inserted or updated unique tables id

using (SqlConnection sqlcon = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]))
{
    sqlcon.Open();
    string query = "INSERT INTO GOU VALUES(@OldPassword,@DateTime,@NewPassword,@IsSuccess,@IsActive) 
   select SCOPE_IDENTITY() as id";
    SqlCommand sqlCommand = new SqlCommand(query, sqlcon);
    sqlCommand.Parameters.AddWithValue("@OldPassword", "oldPassword");
    sqlCommand.Parameters.AddWithValue("@DateTime", DateTime.Now);
    sqlCommand.Parameters.AddWithValue("@NewPassword", "");
    sqlCommand.Parameters.AddWithValue("@IsSuccess", 0);
    sqlCommand.Parameters.AddWithValue("@IsActive", 0);
    sqlCommand.ExecuteNonQuery();                                
    int id = (==> get id from sqlCommand)
}

CodePudding user response：

-- Consider below example with sample table.

CREATE TABLE #myTemp(identity_column INT IDENTITY(1,1), myColumn CHAR(10));

--Use this SQL in C# inline query.

    INSERT #myTemp(myColumn) 
OUTPUT inserted.identity_column as id
VALUES('test');

--You can read output sql in to variable and use it as you expected.