Generate Unico Code in Sequence without Repeating MVC

Question

I want to generate a registration code for each registered client. As ID I use the GUID, however, it is for ID(as it is extensive), I need a small code, not just a digit, for example: cód: 0001 , It cannot repeat itself, and I need to show it on screen before the registration. that is, when I click to create new client, this code theoretically is to appear a field. before even persisting in the bank. was reading the question: How to generate numerical sequences in SQL without creating tables? but I couldn’t cooperate. Question 1 : Is it possible to do what I’m proposing? show a unique code, before persisting in the bank and not repeating that code? Question 2: If Question 1 is true , and if I click add new, and then cancel, I suppose I have created the cód: 0003, Will this city still be available? or will it jump from 0002 for 0004. If anyone knows any material on this subject that you can share with me, I would be grateful.

I tried to last the Andom, but the Radom, does not generate sequence (not that I have succeeded) and it does not make a query in the bank to verify if this generated number exists (at least I do not know how to do). Ps: I am using MVC 5 and MSSQL SERVER

Answer 1

As already answered in the mentioned question, you can use the SEQUENCE Sql Server to get sequential code without repetition.

You can create it in Sql Server with the following command:

CREATE SEQUENCE dbo.NomeSequence
    START WITH 1  
    INCREMENT BY 1 ;

To run this command using Entityframework Code-First, you need to create a Migration file. To create Migration follow the steps below:

Run this command in the Package Manager Console, in the layer of your application where the Entityframework Context:

Add-Migration RunSqlScript

It will create a Migrations folder, with a Runsqlscript.Cs file inside, this file has an up and a down method, in the up method put the script you want to run in the bd.

public partial class RunSqlScript : DbMigration
{
    public override void Up()
    {
        Sql("CREATE SEQUENCE dbo.NomeSequence START WITH 1 INCREMENT BY 1");
    }

    public override void Down()
    {
    }
}

Do this by running the Package Manager Console command

Update-Database

With this in ASP.NET, when the user opens the screen of new registration, you can run in Sqlserver the command NEXT VALUE FOR to get the next sequential number.

Example:

SELECT NEXT VALUE FOR dbo.NomeSequence

To get sequense in Entityframework create a method to run select and return, this method can be in your Context or in the Repository of some model.

Example:

public int GetNextSequenceValue()
{
    var rawQuery = Database.SqlQuery<int>("SELECT NEXT VALUE FOR dbo.NomeSequence;");
    var task = rawQuery.SingleAsync();
    int nextVal = task.Result;

    return nextVal;
}       

This way when the user opens the new registration screen, you call the Entity to pick up the sequence and then you can put it in the model code field, and you will have a sequential number reserved for it, even before entering the registration data in the bd.

Conclusion to your doubts:

Question 1: Is it possible to do what I am proposing?

Yes, it is possible to do, according to the example I sited.

Question 2: If I click add new, and then cancel, I suppose I created Cód: 0003, will that Cód still be available? or will jump from 0002 to 0004.

As you will get the code before persisting, if the user cancels the operation that sequence will be skipped, as in the example from 0002 to 0004. This needs to be so because there can be multiple users registering simultaneously, in which a user opens the new registration a sequential code already has to be reserved for him, so that the next user takes another code, thus maintaining the integrity of the sequence.