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();
}
}
2
Answers
You can use "SCOPE_IDENTITY()" for get the last inserted or updated unique tables id
— Consider below example with sample table.
–Use this SQL in C# inline query.
–You can read output sql in to variable and use it as you expected.