How can I synchronise two datatables and update the target in the database?

Question

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.

So far I have:

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]);
            }

        }

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.

Answer 1

I know it's not directly answering your question, but have you looked at the Microsoft Sync Framework?

It's designed to do this sort of thing and does most (if not all) of the donkey work for you.

Answer 2

This might be overkill but you could try the Sync Framework from Microsoft