问题描述
限时送ChatGPT账号..我在当前项目中使用 SQL Server 2008、visual studio 2008、linq、ado.这个项目是在网络和桌面上实现的.
I'm using SQL Server 2008, visual studio 2008, linq, ado in my current project. this project is implemented in web and desktop.
现在我的问题是网络数据库和桌面数据库之间的数据同步.
now my problem is with synchronization of data between web db and desktop db.
这里的同步如下:
插入客户端的新数据同步到服务器.客户端中的更新数据同步到服务器.这里让我向您展示我的表结构.
here let me show you my table structure.
假设我的表名是带有字段的customer"
let us suppose my table name is "customer" with fields
客户
...................
customerno，bigint，主键，not null，identity true(从 1 开始)
名字，...
姓氏，...
customer
...................
customerno, bigint, primary key, not null, identity true (starting with 1)
firstname, ...
lastname, ...
现在我将在客户端拥有相同的表，但主键标识将从 20000 开始.此主键标识取决于客户端系统的数量.让我们考虑以下场景
now i'll have the same table in client but the primary key identity will start with 20000. this primary key identity depends on the number of client systems. let consider the following scenario
server = customerno 从 1 开始，表中有 1 条记录
client1 = customerno 以 20000 开头，表中有 2 条记录
client2 = customerno 以 30000 开头，表中有 3 条记录
server = customerno starting with 1, with 1 record in table
client1 = customerno starting with 20000, with 2 records in table
client2 = customerno starting with 30000, with 3 records in table
现在当我在 client1 中按下同步时，使用以下代码
now when i press sync in client1, using the following code
cmd.CommandText = "set identity_insert " + tableName + " on";
cmd.ExecuteNonQuery();
foreach (string query in this.queries)
{
cmd.CommandText = query; // this is my insert query
cmd.ExecuteNonQuery();
}
cmd.CommandText = "set identity_insert " + tableName + " off";
cmd.ExecuteNonQuery();
由于 linq 不支持 identity_insert，我使用的是 ado
as linq doesnt support identity_insert, im using ado
在我的代码中，首先我将 identity_insert 设置为打开，插入数据，最后将 identity_insert 设置为关闭.
in my code, first im setting the identity_insert on, inserting the data and finally setting identity_insert off.
现在是我的问题，从客户端 1 同步数据后，服务器中插入两条记录，customerno为20001,20002.
now here my problem, after data is synced from client1, and two records are inserted in server with customerno as 20001,20002.
根据 http://msdn.microsoft/en-us/图书馆/ms188059.aspx
server 中 customerno 列的标识设置为最大值(在本例中为 20002).
the identity of customerno column in server is set to the max value (in this case it will be 20002).
所以现在当我在服务器中插入数据时，它插入的 customerno 为 20003.
so now when i insert data in server, it inserts with customerno as 20003.
这里我不希望服务器标识列取最大值.
here i dont want the server identity column to take the max value.
考虑到上述情况，在同步之前，我们在服务器中有 1 条记录，其 id 为 1，因此在同步后(具有一个或多个客户端)，我希望服务器中的下一个 id(customerno) 为 2,3,4..... 用于服务器中新插入的数据.
considering the above case, before sync, we have 1 record in server, and its id is 1, so after sync (with one or more clients), i want the next id(customerno) in server to be 2,3,4..... for newly inserted data in server.
这里任何客户端都可以在任何时间点与服务器同步数据.
here any client can sync data with server at any point of time.
推荐答案
您将一个字段用于两件事.唯一识别客户并找出信息来源.
You are using one field for two things. Uniquely identifying a customer and to figure out the source of the information.
您可以使用唯一标识符作为主键，并使用另一个字段来确定信息的来源.
You can use a uniqueidentifier as the primary key and use another field to determine the source of the information.
使用 newid() 作为主键的默认约束.复制时，您指定要使用的 GUID，它将代替 newid() 使用.
Use newid() as a default constraint for your primary key. When you are replicating you specify the GUID to use and that will be used instead of newid().
编辑 1
如果您在替换主键时遇到问题，您可以将 GUID 列添加为候选键.那么问题将是，如果您需要更新与客户表相关的表，则在服务器之间复制数据的逻辑将复杂得多.您必须首先插入所有客户，然后查询客户表以找出您应该在相关表中使用哪个 FK CustomerID.
If you have trouble replacing your primary key you can add the GUID column as a candidate key. The problem then will be that the logic for replicating data between servers will be a lot more complicated if you need to update tables that have a relation to your Customers table. You have to first insert all Customers and then query the Customers table to figure out what FK CustomerID you should use in related tables.
这篇关于identity_insert 和同步问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！