如何用具有相同主键的本地数据库表更新远程表？

我想从本地数据库表更新数据库的远程表。 但是当我为大师更新时，我发现了主键不匹配的问题。 那么是否有一种机制可以让我在本地使用相同的主键更新远程数据库表。如果我在本地删除记录，则远程主键不匹配。 虽然我有两种类型的表，没有主键，也有主键。

我在这里这样做了

本地 IF 表带有主键。

  public bool PublishDressingDetails()
        {
            int isUpdated = -1;
            try
            {
                DataTable DressingDetailsLocalDT = cDressing.GetAllDressingDetailsDTForExport();
                if (DressingDetailsLocalDT.Rows.Count > 0)
                {

                    //SqlDataAdapter DA = new SqlDataAdapter("select * from Toppings where ClientID=2", connWeb);
                    SqlDataAdapter DA = new SqlDataAdapter("select * from DressingDetails", connWeb);

                    DA.InsertCommand.Transaction = transaction;
                    DA.UpdateCommand.Transaction = transaction;
                    DA.DeleteCommand.Transaction = transaction;

                    SqlCommandBuilder SCB = new SqlCommandBuilder(DA);

                    DataSet DS = new DataSet();
                    DA.Fill(DS, "DressingDetails");
                    //DS.Tables[0].PrimaryKey = new DataColumn[] { DS.Tables[0].Columns["ClientID"], DS.Tables[0].Columns["ToppingID"] };
                    DS.Tables[0].PrimaryKey = new DataColumn[] { DS.Tables[0].Columns["DressingID"] };

                    object[] searchVal = new object[1];
                    //searchVal[0] = "2";

                    //DataRow drSrc;
                    foreach (DataRow drLocal in DressingDetailsLocalDT.Rows)
                    {
                        searchVal[0] = drLocal["DressingID"].ToString();
                        DataRow drSrc = DS.Tables[0].Rows.Find(searchVal);
                        if (drSrc == null)
                        {
                            drSrc = DS.Tables[0].NewRow();
                            //drSrc["ClientID"] = "2";
                            drSrc["DressingID"] = drLocal["DressingID"].ToString();

                            if (drLocal["Description"] != DBNull.Value)
                            {
                                drSrc["Description"] = drLocal["Description"].ToString();
                            }
                            else drSrc["Description"] = DBNull.Value;

                            if (drLocal["Position"] != DBNull.Value)
                            {
                                drSrc["Position"] = drLocal["Position"].ToString();
                            }
                            else drSrc["Position"] = DBNull.Value;


                            if (drLocal["IsDeleted"] != DBNull.Value)
                            {
                                drSrc["IsDeleted"] = drLocal["IsDeleted"].ToString();
                            }
                            else drSrc["IsDeleted"] = DBNull.Value;


                            if (drLocal["icon"] != DBNull.Value)
                                drSrc["icon"] = drLocal["icon"];
                            else
                                drSrc["icon"] = DBNull.Value;


                            DS.Tables[0].Rows.Add(drSrc);
                        }
                        else
                        {
                            //drSrc["ClientID"] = "2";
                            drSrc["DressingID"] = drLocal["DressingID"].ToString();
                            if (drLocal["Description"] != DBNull.Value)
                            {
                                drSrc["Description"] = drLocal["Description"].ToString();
                            }
                            else drSrc["Description"] = DBNull.Value;

                            if (drLocal["Position"] != DBNull.Value)
                            {
                                drSrc["Position"] = drLocal["Position"].ToString();
                            }
                            else drSrc["Position"] = DBNull.Value;

                            if (drLocal["IsDeleted"] != DBNull.Value)
                            {
                                drSrc["IsDeleted"] = drLocal["IsDeleted"].ToString();
                            }
                            else drSrc["IsDeleted"] = DBNull.Value;


                            if (drLocal["icon"] != DBNull.Value)
                                drSrc["icon"] = drLocal["icon"];
                            else
                                drSrc["icon"] = DBNull.Value;
                        }

                    }
                    isUpdated = DA.Update(DS, "DressingDetails");

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return (isUpdated != -1) ? true : false;
        }

没有主键。

  public bool PublishMealDealDetail()
        {
            bool isUpdated = true;
            try
            {
                DataTable dtLocal = libEPOS.cMealDeal.GetAllMealDealDetailsDTForExport();
                if (dtLocal.Rows.Count > 0)
                {
                    //truncate table on web
                    string sqlTrunc = "TRUNCATE TABLE MealDealDetail";
                    SqlCommand cmd = new SqlCommand(sqlTrunc, connWeb, transaction);
                    // connWeb.Open();
                    cmd.ExecuteNonQuery();
                    SqlBulkCopy bulkcopy = new SqlBulkCopy(connWeb);
                    bulkcopy.DestinationTableName = "MealDealDetail";
                    try
                    {
                        bulkcopy.WriteToServer(dtLocal);
                    }
                    catch (Exception e)
                    {
                        transaction.Rollback();
                        isUpdated = false;
                        Console.WriteLine(e.Message);
                    }

                }
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                isUpdated = false;
                throw ex;
            }

            return isUpdated;
        }

你可以利用System.Data.SqlClient.SqlBulkCopyOptions.KeepIdentity在更新表时保持从一个源到另一源的身份。

MSDN 在这里：http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopyoptions(v=vs.110).aspx http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopyoptions(v=vs.110).aspx

System.Data.SqlClient.SqlBulkCopy bulkcopy = new System.Data.SqlClient.SqlBulkCopy(connWeb,System.Data.SqlClient.SqlBulkCopyOptions.KeepIdentity);