title: Using an SQL Data Adapter to update and insert rows doesn't update (only inserts)
body: <p>I am just playing around with this at the moment, ultimately I want to read a CSV file into memory and then insert the records into an SQL database. Existing records should be updated, records that are missing should be added. Records that don't exist in the new CSV shouldn't be deleted, however.</p>
  
  <p>I've been playing around the example from the MSDN library. I understand that the SqlDataAdapter can perform bulk updates quite quickly.</p>
  
  <p>I created a little mock app with a table called <code>TempTestTable</code> that has <code>TempTestTableId</code>, <code>Age</code>, and <code>Name</code> columns. I wrote this (based on the <a href="http://msdn.microsoft.com/en-us/library/33y2221y%28v=vs.110%29.aspx" rel="nofollow">MSDN Article</a>):</p>
  
  <pre><code>using (SqlConnection connection =
     new SqlConnection("data source=localhost;initial catalog=fishsticks;integrated security=True;MultipleActiveResultSets=True;"))
  {
      SqlDataAdapter dataAdpater = new SqlDataAdapter(
        "SELECT temptesttableid, Age, Name FROM TempTestTable",
        connection);
  
      dataAdpater.UpdateCommand = new SqlCommand(
         "UPDATE TempTestTable SET Age = @Age, Name = @Name " +
         "WHERE TempTestTableId = @TempTestTableId", connection);
  
      dataAdpater.InsertCommand = new SqlCommand(
         "INSERT INTO TempTestTable (Age, Name) " +
         "VALUES (@Age, @Name)", connection);
  
      SqlParameter parameter = dataAdpater.UpdateCommand.Parameters.Add(
         "@TempTestTableId", SqlDbType.Int);
  
      dataAdpater.InsertCommand.Parameters.Add(
        "@Age", SqlDbType.Int, 10, "Age");
  
      dataAdpater.InsertCommand.Parameters.Add(
        "@Name", SqlDbType.NVarChar, 50, "Name");
  
      dataAdpater.UpdateCommand.Parameters.Add(
        "@Age", SqlDbType.Int, 10, "Age");
  
      dataAdpater.UpdateCommand.Parameters.Add(
        "@Name", SqlDbType.NVarChar, 50, "Name");
  
      parameter.SourceColumn = "TempTestTableId";
      parameter.SourceVersion = DataRowVersion.Original;
  
      DataTable tempTestTable = new DataTable();
  
      tempTestTable.Columns.Add("Age");
      tempTestTable.Columns.Add("Name");
      tempTestTable.Columns.Add("TempTestTableId");
  
      var row1 = tempTestTable.NewRow();
  
      row1["Age"] = 10;
      row1["Name"] = "Smith";
      row1["TempTestTableId"] = 1;
  
      var row2 = tempTestTable.NewRow();
  
      row2["Age"] = 40;
      row2["Name"] = "Jones";
      row2["TempTestTableId"] = 2;
  
      tempTestTable.Rows.Add(row1);
      tempTestTable.Rows.Add(row2);
  
      dataAdpater.Update(tempTestTable);
  </code></pre>
  
  <p>There is already a record in the database with <code>TempTestTableId = 1</code>, so the idea was that in theory it would update that record, as well as insert a new record with ID 2. However, when I run the code, it inserts both items.</p>
  
  <p>Any ideas?</p>