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

问题描述

我的 asp.net mvc web 应用程序中有存储库方法，可以自动生成一个唯一的序列号，称为 Tag:-

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.

p>

在 EF 上下文中，表示类看起来像

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; }
}

以及使用它的准系统程序:

And the barebone procedure to use it:

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)

并发检查是为了防止并发用户绘制相同的数字.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屋！