DataAdapter：如果行已存在，则更新，否则插入

I have a Windows Mobile PPC with a local SQL CE database which I'm trying to replicate to a remote SQL Server database when connected. I can't use bulk replication or any other "automated" method as the platform is very obsolete and SQL CE is limited.

我有一个带有本地SQL CE数据库的Windows Mobile PPC，我试图在连接时将其复制到远程SQL Server数据库。我不能使用批量复制或任何其他“自动”方法，因为该平台已经过时且SQL CE受到限制。

I adapted a solution here but I'm kinda lost, as the procedure works well when the destination database is empty, but fail (primary key error) when it has pre-existing data. So the DataAdapter is likely only making INSERTS and can't do UPDATES.

我在这里修改了一个解决方案，但我有点迷失，因为当目标数据库为空时，该过程运行良好，但当它具有预先存在的数据时失败（主键错误）。所以DataAdapter很可能只制作INSERTS并且无法进行更新。

Here's my code:

这是我的代码：

private void StockSqlBuilder() 
{
DataSet SqlCeDataset = new DataSet("source");
DataSet SqlDataset = new DataSet("destination");

try
{
    _conn.Open();
    // Pull data from local database SQLCE
    using (SqlCeDataAdapter sourceDA = new SqlCeDataAdapter("SELECT * FROM stock", _conn))
    {
        MessageBox.Show("Pull Stock from CE");
        sourceDA.Fill(SqlCeDataset, "stock");
    }
    _conn.Close();

    _remoteConn.Open();
    // Connect to SQL Server database to push data
    using (SqlDataAdapter destinationDA = new SqlDataAdapter("SELECT * FROM stock", _remoteConn))
    {
        // Get table of remote SQL Server, then loop through all rows of SQLCE and add them to dataset
        SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(destinationDA);
        destinationDA.Fill(SqlDataset, "stock");

        foreach (DataRow row in SqlCeDataset.Tables["stock"].Rows)
        {
            SqlDataset.Tables[0].NewRow();
            SqlDataset.Tables[0].Rows.Add(row.ItemArray);
        }


        destinationDA.Update(SqlDataset, "stock");
    }
    _remoteConn.Close();
}
catch (SqlException Sqlex)
{
    MessageBox.Show(Sqlex.Message);
    this.returnStatus = false;
}
catch (SqlCeException SqlCEex)
{
    MessageBox.Show(SqlCEex.Message);
    this.returnStatus = false;
}
}

Also if you have other solutions for two-way SQL sync for my project, I'm all ears but I tried a lot of different things.

此外，如果你有我的项目的双向SQL同步的其他解决方案，我都是耳朵，但我尝试了很多不同的东西。

1 个解决方案

#1

Got good results with a merge query using a temporary table, and comparing the date for selective update:

使用临时表的合并查询获得了良好的结果，并比较了选择性更新的日期：

       string mergeQuery = @"--Synchronize the target table with refreshed data from source table
            MERGE dbo.stock AS TARGET
            USING dbo.stock_sync AS SOURCE 
            ON (TARGET.product_id = SOURCE.product_id) 
            --When records are matched, update the records if there is any change
            WHEN MATCHED AND SOURCE.date_stock > TARGET.date_stock THEN
            UPDATE SET TARGET.spot= SOURCE.spot, 
            TARGET.date_stock = SOURCE.date_stock,
            TARGET.loading_id = SOURCE.loading_id 
            --When no records are matched, insert the incoming records from source table to target table
            WHEN NOT MATCHED BY TARGET THEN 
            INSERT (spot, product_id, date_stock, loading_id) 
            VALUES (SOURCE.spot, SOURCE.product_id, SOURCE.date_stock, SOURCE.loading_id);

            SELECT * FROM dbo.stock;";

            // Merge the two tables by matching code_barre row
            SqlCommand mergeSql = new SqlCommand(mergeQuery, _remoteConn);
            int rowsAffected = mergeSql.ExecuteNonQuery();
            MessageBox.Show("Données affectées : " + rowsAffected);

            // Clean the temporary source table
            SqlCommand recleanSql = new SqlCommand("TRUNCATE TABLE dbo.stock_sync", _remoteConn);
            recleanSql.ExecuteNonQuery();