What is the best way to avoid duplication of data

Question

I have this code below to generate series number upon saving of the user. But my problem is if the user of the system save at the same time(many user) it has a possibility to have a duplicate series number.

  public string GenerateSeriesNumber(string code, Entities db, string Ctrl)
    {
        string year = DateTime.Now.Year.ToString().Substring(2);

        string prev_ctrls = "";
        var tbl = db.SeriesTable.Where(a => a.SeriesNo.Contains(Ctrl)).OrderByDescending(a => a.SeriesNo);
        foreach (var item in tbl.ToList())
        {
            string[] current_srs = item.SeriesNo.Split('-');
            if (prev_srs == "")
                prev_srs = item.SeriesNo;

            int aaa = Convert.ToInt32(prev_srs.Split('-')[3]);
            int bbb = Convert.ToInt32(current_srs[3]);
            if (aaa < bbb)
                prev_srs = item.SeriesNo;

        }
        string Series_No = "";

        if (tbl != null)
        {
            string[] LastSeries_No = prev_srs.Split('-');
            if (LastSeries_No.Length == 4)
            {
                LastSeries_No[3] = Int32.Parse(LastSeries_No[3]) + 1 + "";
                for (int i = LastSeries_No[3].Length; i < 7; i++)
                {
                    LastSeries_No[3] = "0" + LastSeries_No[3];
                }
                LastSeries_No[1] = code.ToUpper();
                Series_No = String.Join("-", LastSeries_No);
                return Series_No;
            }
        }
       
        return Series_No;
    }

I tried to execute this in my SQL (I am using SQL Server 2017) but one of them encountered an error and didn't save

ALTER TABLE dbo.SeriesTable
ADD CONSTRAINT UC_LT UNIQUE (SeriesNo);

One option would be to create a unique index and when an exception is thrown just generate another number. — user743414, Aug 07 '20 at 07:51
@user743414 I have tried it, when it encounter an error. Upon return to my Javascript I call again the function to save. But unfortunately one of them encountered an error as stated above. — FaceLess No One, Aug 07 '20 at 07:53
You probably already have duplicate data in your table - you should solve the duplicate rows and recreate the constraint or add a WITH NOCHECK . you could add a new_id() to the end of the key if the length is not a issue — Mitz, Aug 07 '20 at 09:57
@Mitz sorry for the late response. the only time that will encounter a duplicate data if they save the data at the same time. as mention above — FaceLess No One, Aug 11 '20 at 01:29

score 0 · Answer 1 · answered Aug 12 '20 at 10:19

In order to avoid duplication of data, you can use different methods:

Identity columns (not best for serial keys)
NEWID() - see here
a Sequence

In your case i'd use NEWID(), or just a sequence number and add dashes('-') between digits.

p.s. If there are other requirements that force you to make the serial number incremental or meaningful in some way, you should have included them in the question.

What is the best way to avoid duplication of data

1 Answers1