CODEWITHSUNDEEP
c#sql-server
sir_thursday
sir_thursday

Reputation: 5419

Autoincrement with SQL Insert

There were some previous questions like this but I didn't really understand the answers. Here's my query

using (SqlCommand cmd = new SqlCommand(
    "INSERT INTO Results VALUES(@ResultID, @HasSucceeded, @ScenarioID, @Screenshot)",
    conn))
{
    cmd.Parameters.AddWithValue("@ResultID", Id);
    cmd.Parameters.AddWithValue("@HasSucceeded", HasSucceeded);
    cmd.Parameters.AddWithValue("@ScenarioID", Id);
    cmd.Parameters.AddWithValue("@Screenshot", screenshot);

    cmd.ExecuteNonQuery();
}

Now I am using Id as a placeholder to for @ResultID. What I really want to do is autoincrement @ResultID with this INSERT INTO query. How can I do this?

Upvotes: 0

Views: 565

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1064204

Make this an IDENTITY column (for example, IDENTITY(1,1)) and let the database manage it. Trying to manage it from outside is a threading nightmare. Then it becomes (noting that it is unusually complicated due to using Id for 2 columns, which sound like an error):

int newId = 0;

using (SqlCommand cmd = new SqlCommand(
    @"INSERT INTO Results (HasSucceeded, Screenshot)
      VALUES (@HasSucceeded, @Screenshot);
      DECLARE @ResultID int = SCOPE_IDENTITY();
      UPDATE Results SET ScenarioID = @ResultID
                     WHERE ResultID = @ResultID;
      SELECT @ResultID;", conn))
{
    cmd.Parameters.AddWithValue("@HasSucceeded", HasSucceeded);
    cmd.Parameters.AddWithValue("@Screenshot", screenshot);

    newId = (int)cmd.ExecuteScalar();
}

// ...Use newId here...

Upvotes: 3

Related Questions