替代使用MAX + 1以创建一个用户友好的唯一的序列号 [英] Alternatives to using Max+1 to create a user friendly unique sequence numbers

问题描述

我有我的asp.net MVC Web应用程序内的存储库的方法，自动生成一个唯一的序列号被称为标签： -

I have the repository method inside my asp.net mvc web application, to automatically generate a unique sequence number called Tag:-

public void InsertOrUpdateServer(TMSServer server, string username)
        {

    if (server.TMSServerID == default(int))
            {
                // New entity
    int technologyypeID = GetTechnologyTypeID("Server");
    Technology technology = new Technology
       {
     IsDeleted = true,
     Tag = "S" + GetTagMaximumeNumber(technologyypeID).ToString(),
     StartDate = DateTime.Now,
     };
   Save();
//code goes here

凡GetTagMaximumeNumber（）函数是： -

Where the GetTagMaximumeNumber() function is :-

public int GetTagMaximumeNumber(int typeID) {
string s = tms.Technologies.Where(a => a.TypeID == typeID).Max(a2 => a2.Tag.Substring(1));

           if (s != null)
           {
             return Int32.Parse(s) + 1;

           }
           else {
               return 100;//if this is the first record created
           }}

以上将获得标签的最大数加1，然后尝试将其保存到数据库中。以上是运作良好，但我确信，生产服务器在多个用户将增加记录上，相同的标签号码将被生成并尝试添加已经存在的数据库中自动生成的标签号码时，将引发异常。
所以，我有以下问题：-1

The above will get the maximum number of the tag and add 1 then try to save it into the database. The above is working well, but I am sure that on production server where multiple users will be adding records, same tag numbers will be generated and exception will be raised when trying to add an automatically generated tag number that already exists on the database. So I have the following questions:-1

1. 什么是我可以跟着有一个序列号，始终是唯一的其他方法。

1. What are the other approaches which I can follow to have a sequence number that is always unique.

在我目前的做法在标签号码的差距是最小的。差距只有当用户删除记录和标签号码是不是最大时。那么，有没有办法仍然有这个在任何新的方法，这样的差距将是最小的？

In my current approach the gaps in the Tag number are minimal. The gap only occurs when user delete a record and its tag number was not the maximum. So is there a way to still have this in any new approach, so gaps will be minimal?

感谢您的帮助。

推荐答案

如果在SQL 2012的新的序列是不是一种选择（因为你不能升级），你可以创建一个存储的最后一个序列号的表。

If the new sequences in SQL 2012 are not an option (because you can't upgrade) you could create a table that stores the last sequence numbers.

在EF背景下重新presenting类会是什么样子。

In the EF context the representing class would look like

class TaggedSequence
{
    [Key]
    public int Id { get; set; }

    [Required]
    public int Last { get; set; }

    [Required]
    [Column(TypeName = "CHAR"), StringLength(1)]
    public string Tag { get; set; }

    [Timestamp]
    public byte[] Rowversion { get; set; }
}

和准系统程序使用它：

using (var context = new MyContext())
{
    try
    {
        var tag = "S";
        var sequence = context.TaggedSequence.Single(s => s.Tag == tag);
        sequence.Last += 1;
        Technology technology = new Technology { ... };
        technology.Id = tag + sequence.Last;
        context.Technologies.Add(technology);
        context.SaveChanges();
    }
    catch (DbUpdateConcurrencyException ex)
    {
        // Retry
    }
}

（你必须适应它到你的资源库对象）

(You'll have to fit it in into your repository object)

并发检查是否有prevent并发用户的形式绘制相同的号码。在 Rowversion 确保如果它的价值没有获取记录并更新它的改变更新只被允许。

The concurrency check is there to prevent concurrent users form drawing the same number. The Rowversion ensures that updates are only allowed if its value did not change between fetching the record and updating it.

这篇关于替代使用MAX + 1以创建一个用户友好的唯一的序列号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！