title: CommandBuilder and SqlTransaction to insert/update a row
body: <p>I can get this to work, but I feel as though I'm not doing it properly.</p>
  
  <p>The first time this runs, it works as intended, and a new row is inserted where "thisField" contains "doesntExist"</p>
  
  <p>However, if I run it a subsequent time, I get a run-time error that I can't insert a duplicate key as it violate the primary key "thisField".</p>
  
  <pre><code>
  static void Main(string[] args)
  {           
      using(var sqlConn = new SqlConnection(connString) )
      {
          sqlConn.Open();
          var dt = new DataTable();
  
          var sqlda = new SqlDataAdapter("SELECT * FROM table WHERE thisField ='doesntExist'", sqlConn);
          sqlda.Fill(dt);
  
          DataRow dr = dt.NewRow();
  
          dr["thisField"] = "doesntExist"; //Primary key
  
          dt.Rows.Add(dr);
  
          //dt.AcceptChanges(); //I thought this may fix the problem. It didn't.
  
          var sqlTrans = sqlConn.BeginTransaction();
  
          try
          {
              sqlda.SelectCommand = new SqlCommand("SELECT * FROM table WITH (HOLDLOCK, ROWLOCK) WHERE thisField = 'doesntExist'", sqlConn, sqlTrans);
  
              SqlCommandBuilder sqlCb = new SqlCommandBuilder(sqlda);
  
              sqlda.InsertCommand = sqlCb.GetInsertCommand();
              sqlda.InsertCommand.Transaction = sqlTrans;
  
              sqlda.DeleteCommand = sqlCb.GetDeleteCommand();
              sqlda.DeleteCommand.Transaction = sqlTrans;
  
              sqlda.UpdateCommand = sqlCb.GetUpdateCommand();
              sqlda.UpdateCommand.Transaction = sqlTrans;
  
              sqlda.Update(dt);
  
              sqlTrans.Commit();
          }
          catch (Exception)
          {
              //...
          }
      }
  }</code></pre>
  
  <p>Even when I can get that working through trial and error of moving AcceptChanges around, or encapsulating changes within Begin/EndEdit, then I begin to experience a "Concurrency violation" in which it won't update the changes, but rather tell me it failed to update 0 of 1 affected rows.</p>
  
  <p>Is there something crazy obvious I'm missing?</p>