title: How can I synchronise two datatables and update the target in the database?
body: <p>I am trying to synchronise two tables between two databases.  I thought that the best way to do this would be to use the DataTable.Merge method.  This seems to pick up the changes, but nothing ever gets committed to the database.</p>
  
  <p>So far I have:</p>
  
  <pre><code>string tableName = "[Table_1]";
          string sql = "select * from " + tableName;
  
          using (SqlConnection sourceConn = new SqlConnection(ConfigurationManager.ConnectionStrings["source"].ConnectionString))
          {
              SqlDataAdapter sourceAdapter = new SqlDataAdapter();
  
              sourceAdapter.SelectCommand = new SqlCommand(sql, sourceConn);
              sourceConn.Open();
              DataSet sourceDs = new DataSet();
              sourceAdapter.Fill(sourceDs);
  
              using (SqlConnection targetConn = new SqlConnection(ConfigurationManager.ConnectionStrings["target"].ConnectionString))
              {
                  SqlDataAdapter targetAdapter = new SqlDataAdapter();
  
                  targetAdapter.SelectCommand = new SqlCommand(sql, targetConn);
  
                  SqlCommandBuilder builder = new SqlCommandBuilder(targetAdapter);
  
                  targetAdapter.InsertCommand = builder.GetInsertCommand();
                  targetAdapter.UpdateCommand = builder.GetUpdateCommand();
                  targetAdapter.DeleteCommand = builder.GetDeleteCommand();
  
                  targetConn.Open();
                  DataSet targetDs = new DataSet();
                  targetAdapter.Fill(targetDs);
  
                  targetDs.Tables[0].TableName = tableName;
                  sourceDs.Tables[0].TableName = tableName;
                  targetDs.Tables[0].Merge(sourceDs.Tables[0]);
  
                  targetAdapter.Update(targetDs.Tables[0]);
              }
  
          }
  </code></pre>
  
  <p>At the present time, there is one row in the source that is not in the target.  This row is never transferred.  I have also tried it with an empty target, and nothing is transferred.</p>