I want to develop a small winform software C# with SQL Server 2008 R2 which runs on 3 computers.
我想用一台在3台计算机上运行的SQL Server 2008 R2开发一个小型winform软件C#。
I have designed the "Customer_Master" table with a INDENTITY type primay key(CusId).
我使用INDENTITY类型的primay键(CusId)设计了“Customer_Master”表。
What I want is, lets say when I open a customer registration form then the program should display the next id value. I can get the next identity value by using SCOPE_IDENTITY(). But in this case, as I used a centralized system, if all the computers try to create a new customer at the same time it will show the same key at their registration form. But if they got register a new customer the registered id will be different from what was shown.
我想要的是,让我们说当我打开客户注册表单时,程序应显示下一个id值。我可以使用SCOPE_IDENTITY()获取下一个标识值。但在这种情况下,当我使用集中式系统时,如果所有计算机同时尝试创建新客户,它将在其注册表单中显示相同的密钥。但如果他们注册了新客户,则注册的ID将与显示的不同。
So I want to lock the next identity value register the Customer with the id which is shown in the form. How to do this. Is there any way to manage concurrent inserts. I am using C# and SQL Server R2 Express.
所以我想锁定下一个标识值,用表格中显示的id注册Customer。这个怎么做。有没有办法管理并发插入。我正在使用C#和SQL Server R2 Express。
Please help me solve this problem. Thanks in advance.
请帮我解决这个问题。提前致谢。
2 个解决方案
#1
1
Do you really need the ID before the record is inserted? If not, then SCOPE_IDENTITY will work fine. If the insert of the new record works, then SCOPE_IDENTITY will return the id of the record that the client inserted (not any other clients).
在插入记录之前,您真的需要ID吗?如果没有,那么SCOPE_IDENTITY将正常工作。如果新记录的插入有效,则SCOPE_IDENTITY将返回客户端插入的记录的id(而不是任何其他客户端)。
If you do need the ID beforehand, then you could insert a "stub" record as a placeholder until the client writes the "real" customer record. In this way, each client could essentially reserve the ID before writing the actual data.
如果您事先需要ID,那么您可以插入“存根”记录作为占位符,直到客户端写入“真实”客户记录。这样,每个客户端在写入实际数据之前基本上可以保留ID。
This is not a good idea, however, as it can lead to a lot of garbage data in your table.
但是,这不是一个好主意,因为它可能会导致表中的大量垃圾数据。
#2
2
Not with using IDENTITY, what you "want" is not possible with INT IDENTITY as the next sequence is determined as the INSERT is EXECUTED. You also cannot "guess" at what the next number is because there is no guarantee that the identity will be contiguous. I don't think this is a very wise decision but if you really want this then you would need to create a table that's whole purpose to sequentially increment and use that value as your PK and remove the IDENTITY specification from the Customer_Master table.
不使用IDENTITY,INT IDENTITY无法实现“想要”,因为下一个序列被确定为INSERT已执行。您也无法“猜测”下一个数字是什么,因为无法保证身份是连续的。我不认为这是一个非常明智的决定,但是如果你真的想要这个,那么你需要创建一个表,其目的是按顺序递增并将该值用作PK,并从Customer_Master表中删除IDENTITY规范。
#1
1
Do you really need the ID before the record is inserted? If not, then SCOPE_IDENTITY will work fine. If the insert of the new record works, then SCOPE_IDENTITY will return the id of the record that the client inserted (not any other clients).
在插入记录之前,您真的需要ID吗?如果没有,那么SCOPE_IDENTITY将正常工作。如果新记录的插入有效,则SCOPE_IDENTITY将返回客户端插入的记录的id(而不是任何其他客户端)。
If you do need the ID beforehand, then you could insert a "stub" record as a placeholder until the client writes the "real" customer record. In this way, each client could essentially reserve the ID before writing the actual data.
如果您事先需要ID,那么您可以插入“存根”记录作为占位符,直到客户端写入“真实”客户记录。这样,每个客户端在写入实际数据之前基本上可以保留ID。
This is not a good idea, however, as it can lead to a lot of garbage data in your table.
但是,这不是一个好主意,因为它可能会导致表中的大量垃圾数据。
#2
2
Not with using IDENTITY, what you "want" is not possible with INT IDENTITY as the next sequence is determined as the INSERT is EXECUTED. You also cannot "guess" at what the next number is because there is no guarantee that the identity will be contiguous. I don't think this is a very wise decision but if you really want this then you would need to create a table that's whole purpose to sequentially increment and use that value as your PK and remove the IDENTITY specification from the Customer_Master table.
不使用IDENTITY,INT IDENTITY无法实现“想要”,因为下一个序列被确定为INSERT已执行。您也无法“猜测”下一个数字是什么,因为无法保证身份是连续的。我不认为这是一个非常明智的决定,但是如果你真的想要这个,那么你需要创建一个表,其目的是按顺序递增并将该值用作PK,并从Customer_Master表中删除IDENTITY规范。