向每个用户提供个人ID都有问题 [英] Having problem with providing individual id to every user
问题描述
实际上我正在使用c＃windows应用程序开发程序，我必须制作一个软件，每个客户都有独特的新的独特ID。
这里我给你的表设计 -
id cust_name age deprtmntname deptid
1 ---- cust1 ----- 25 ----- test1 ---------- 1
2 ---- cust2 ----- 20 ----- test2 ---------- 1
3 cust3 ------ 35 ---- test1 ---------- 2
========================= =================
问题是每当有超过10个用户开始输入条目然后某个时候唯一ID为departmentid给出了贬义的价值观。 PLZ建议我一些选项，以便每个用户都可以获得唯一的id以及独特的离开。可能是服务器有一些选项然后我使用的是SQL Server 2008 r2。
谢谢
我尝试了什么：
squery =SELECT isnull（max（slno），0）+1 FROM OPDPatients with（holdlock）其中slyear = @ year;
cmd1 =新的SqlCommand（squery，con）;
cmd1.Parameters.AddWithValue（@ year，LoginForm.dtyear.Year） ;
da = new SqlDataAdapter（cmd1）;
dt = new DataTable（）;
da.Fill（dt）;
Actually I am developing program in c# windows application and I have to make a software where every customer have new unique id with unique departmentid.
here I am giving you the table design-
id cust_name age deprtmntname deptid
1----cust1-----25-----test1----------1
2----cust2-----20-----test2----------1
3 cust3------35----test1----------2
==========================================
the problem is whenever more than 10 users start putting entry then sometime unique id of departmentid is giving dublicate values. plz suggest me some option so that every user can get unique id along with unique departmentid. Might be there is some option from server then I am using SQL server 2008 r2.
thank you
What I have tried:
squery = "SELECT isnull(max(slno),0)+1 FROM OPDPatients with (holdlock) where slyear=@year";
cmd1 = new SqlCommand(squery, con);
cmd1.Parameters.AddWithValue("@year", LoginForm.dtyear.Year);
da = new SqlDataAdapter(cmd1);
dt = new DataTable();
da.Fill(dt);
推荐答案
问题是你从数据库中获取最后一个使用过的ID，然后尝试使用它，而不是实际存在于数据库中。
正如您所发现的那样，在多用户环境中，您不能这样做，因为多台计算机可以同时执行此查询并获得相同的ID。
你唯一的解决方案是让数据库为您分配值到记录。您必须使用自动编号字段。您不要向数据库询问值。您只需保存一条至少包含最少信息量的新记录，以保持数据有效，无需设置ID值，数据库引擎将为您分配一个。然后，您从数据库中获取id值，它是IDENTITY值，并且您已获得ID值。你将在同一个查询中运行两个查询，一个INSERT和一个SELECT。
你可以找到一个例子此处 [ ^ ]。有关详情，请参阅 Google [ ^ ]。
The problem is that you're getting the last used ID from the database and then trying to use it without it actually existing in the database.
As you're found, in a multi-user environment, you cannot do that as more than one machine can execute this query at the same time and get the same ID.
Your only solution is to have the database assign the value to the record for you. You MUST use an auto numbered field. You don't ask the database for the value. You just save a new record with at least the minimum amount of information to keep the data valid, WITHOUT SETTING THE ID VALUE, and the database engine will assign one for you. You then get the id value back from the database, it's IDENTITY value, and you've got the ID value. You'll be running TWO queries, an INSERT and a SELECT, in the same query.
You can find an example here[^]. For more, see Google[^].
不要这样做。
使ID字段成为DB中的IDENTITY列，让SQL处理使值唯一。
SQL自动增加字段 [ ^ ]
除了解决方案1和2，如果您有id的特定格式，您可能想要探索向您的数据库询问该唯一ID [ ^ ]。
BTW，我注意到你把deprtmntname放在用户表中，为什么？您是否每次插入新用户时都复制deprtmntname数据。理所当然，deprtmntname和deptid应该在一个表中单独说dept。你应该删除deprtmntname，但在用户表中保留deptid，用户表的deptid将链接到dept表的deptid。
了解更多关于数据库规范化中的1NF，2NF，3NF和BCNF [ ^ ]
++++ + [第2轮] +++++
Apart from Solutions 1 and 2, if you have specific format for the id, you may want to explore Ask Your Database for that Unique ID[^].
BTW, I notice you put the "deprtmntname" in the user table, why? Aren't you duplicating the "deprtmntname" data every time you insert a new user. Rightfully, "deprtmntname" and "deptid" should be in one table say "dept" by itself. You should remove the "deprtmntname" from but leave the "deptid" in the user table, the "deptid" of the user table will link to the "deptid" of the dept table.
Learn more about 1NF, 2NF, 3NF and BCNF in Database Normalization[^]
+++++[round 2]+++++
让我再解释一下我在这里有一个名为customerdetails的表我必须为每个客户提供一个唯一的id，以及我必须根据departmentname提供给客户的另一个唯一ID。每个注册了注册号的用户都没有注册，我也有超过20个部门，我给你的例子是dept1，dept2，dept3，dept4。
let me explain you again I have a table named customerdetails here I have to provide a unique id to every individual customer and one more unique id I have to provide to customer on basis of departmentname. Every user registered with registration no and also with departmentid like I have more than 20 department I am giving you the example like dept1, dept2, dept3, dept4.
在关系中实现你的要求数据库设计，你有两个实体，用户和部门，每个应该有一个自己的表，如下所示：
To implement your requirement in relational database design, you have two entities here, the user and the dept, each should have one one table of its own like this:
user table: dept table:
userid | deptid | username | userotherdetails deptid | deptname |deptotherdea
V |
|____________________________________________|
当用户注册时，他将获得一个唯一的用户ID，他的deptid值应该来自dept表中的有效值。要查找用户的deptname，您将把用户表中的deptid链接回dept表。这样，dept表就像一个查找表。
userid和deptid都是各个表的主键。
如果你不理解我的解释，那么显然你必须阅读数据库设计简介 [ ^ ]。
需要注意的一点是数据库设计与excel电子表格不同。它们是遵循的设计原则，以便最大限度地减少重复，冗余数据，数据完整性，并便于随后对数据进行操作。
When a user registers, he is given a unique userid and his deptid value should be derived from a valid value from the dept table. To find the deptname of a user, you will link the deptid from the user table back to the dept table. In this way, the dept table acts like a lookup table.
Both the userid and deptid are primary keys for the respective tables.
If you do not understand my explanation, then clearly you have got to read up on Introduction to database design[^].
One thing to note is that database design is unlike excel spreadsheet. They are design principles to follows so as to minimize duplication, redundant data, data integrity, and to facilitate the manipulation of data subsequently.
这篇关于向每个用户提供个人ID都有问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
