Question posted in
The official documentation can be found here.

Asp.net – Auto-generated reference number repeats more than twice

I have been trying to create an automated reference number that can be used to identify each user, but in some aspect, there is a repetition in the number.

From my code, the reference number is supposed to be unique, but there are multiple of the same reference number. How can I create a unique reference number that cannot be repeated?

For example, if a reference number like 539873321 exists in the database table, it should generate a new one. But whenever I go to the page, a new reference number is created and in some cases it repeats the same number and I will have like 3 of the same reference number 539873321.

Please I want to create an auto-generated reference number that cannot be repeated, how can I do that please?

Here is my code to generate number automatically without repeating the same number:

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        AutoGenerateNumber();
    }
}

This code generates 9-digit automatic reference number:

private string GetAutoNumber()
{
    string numbers = "1234567890";
    string characters = numbers;
    int length = 9;
    string id = string.Empty;

    for (int i = 0; i < length; i++)
    {
        string character = string.Empty;

        do
        {
            int index = new Random().Next(0, characters.Length);
            character = characters.ToCharArray()[index].ToString();
        } while (id.IndexOf(character) != -1);

        id += character;
    }

    return id;
}

This code is where is search the database table to see if the reference number exists, then it should generate another reference number:

private void AutoGenerateNumber()
{
    try
    {
        using (SqlConnection con = new SqlConnection())
        {
            con.ConnectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
            SqlDataReader dr;

            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Parameters.Clear();

                // Here I tried to check the table to see if the 
                // reference number number already exists. And if it exists,
                // then it should automatically create a new one                         
                cmd.CommandText = "SELECT reference_no FROM IDTable WHERE reference_no = @reference_no";
                cmd.Parameters.AddWithValue("@Reference_no", ReferenceLabel.Text);
                cmd.Connection = con;

                con.Open();
                dr = cmd.ExecuteReader();
            }

            if (dr.HasRows)
            {
            }
            else
            {
                List<string> result = new List<string>();

                if (Session["Numbers"] != null)
                {
                    result = (List<string>)Session["Numbers"];
                }

                string number = GetAutoNumber();

                if (!result.Contains(number))
                {
                    result.Add(number);
                    Session["Numbers"] = result;
                    ReferenceLabel.Text = number;                          
                }
            }
        }
    }
    catch (SqlException ex)
    {
        string msg = "Error:";
        msg += ex.Message;
        throw new Exception(msg);
    }
}
Tags:

2

Answers


  1. 0

    First of all GetAutoNumber has no guarantee to be unique, it generates pseudo random numbers and it certainly can repeat (but i guess you knew that already).

    But you then you check if the number exists in database (though your code does not reflect that, but I guess value passed to query would be generated with GetAutoNumber.

    So, imagine if your code is executed in parallel by multiple threads (let’s say 2 for simplicity).

    Both threads has generated the same pseudo random number from GetAutoNumber. Both, at the same time check for its existence, which passes (this number does not exist yet). Both threads proceed further, eventually persisting the numbers, leading to duplication.

    You need to apply some locking mechanism, such as lock, SemaphoreSlim, or other 🙂

    The easiest would be to put under lock entire section "check if number exists in DB and if not, save it".

    UPDATE

    Example with lock

    private object _lock = new();

private void AutoGenerateNumber()
{
    try
    {
        lock(_lock)
        {
            // your code
        }
    }
    catch (SqlException ex)
    {
        string msg = "Error:";
        msg += ex.Message;
        throw new Exception(msg);
    }
}
    Login or Signup to reply.
  2. 0

    Don’t try to build this yourself. Just about all databases have some way to create auto-incrementing IDs. This essentially amounts to atomically incrementing a shared number. This is often the default option if you just want a unique number to use as a primary key.

    The other common alternative is UUID / GUID, this is a 128 bit random number. The large size makes properly constructed GUIDs unique in practice.

    There are advantages and disadvantages to each options. GUIDs can be constructed on the client, and are globaly unique, not just in your database. But are larger, and it’s random nature may make the databases job slightly harder.

    If you expose the number to the user you probably want it to be random to prevent guessing of reference numbers. So either a GUID, or use a random 64-bit number and add it to a table with a unique constraint, and just retry with a new number if adding fails due to the constraint.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search