0

I have one webserver is running for two year (SQL server2017 and ASP.NET Web Forms)

but in these two days appeared repeated insert data.

searching data before inserting data to check data is not null but SQL server in repeated data.

I can't however sort out what is best practice in resolving the issue.

toolbox.cs#

  public string getID(string PId, string TestId)
    {
        string idCode = "";
        char FillUp = '0';
        int length = 4;
        int one = 0;

        if (PId.Length != 10)
        {
            throw new NullReferenceException("Error");

        }
        string newId = PId.Replace("-", "");
        if (TestId == null)
        {
            idCode = newId + "0001";
        }
        else
        {
            string checkID = TestId.Substring(11, 4);
            int trynumber;
            int.TryParse(checkID , out trynumber);

            int firstNumber = trynumber + 1;
            string number = firstNumber.ToString();

            string idNumber = number.PadLeft(length, FillUp);
            idCode = newId + idNumber;

        }

        return idCode;

    }

#addData.ashx#

   using (Entity EntityData= new Entity())
            {
       var checkID = EntityData.TEST_DATA.AsNoTracking()
                    .Where(o => o.NUMBER_NO == XXXX_NO)
                    .OrderByDescending(o => o.ID)
                    .FirstOrDefault();

                toolbox tool = new toolbox();

                if (checkID == null)
                {
                    ID = tool.getID(getPID, null);
                }
                else
                {
                    ID = tool.getID(getPID, checkID.ID);
                }


                TEST_DATA addDATA = new TEST_DATA();
                addDATA.ID = ID;
                addDATA.NUMBER_NO = XXXX_NO;

                addDATA.RECDATE = DateTime.Now;
                addDATA.MODIFY_TIME = DateTime.Now;

                EntityData.TEST_DATA.Add(addDATA);
                EntityData.SaveChanges();
}

enter image description here

Paul
  • 173
  • 1
  • 9
  • Is it possible that concurrent connection call the function `getID` at the same time? You have to make `getID` atomic with a synchronization object – Marco Beninca Sep 12 '22 at 08:10
  • 1
    Yes this is an unreliable way to generate a unique key. I suggest that you create a primary key or unique index on the table to stop this ever happening. In fact ifyou don't already have this, it's a design issue. – Nick.Mc Sep 12 '22 at 08:46
  • `getID` is inserted data call the function – Paul Sep 12 '22 at 09:09
  • A multi-user database requires Unique ID to be assigned in a stored procedure so the duplicates do not get generated. The stored procedure needs to lock the database while the unique ids are generated. See : https://learn.microsoft.com/en-us/sql/t-sql/functions/newid-transact-sql?view=sql-server-2017 – jdweng Sep 12 '22 at 09:22

1 Answers1

0

SQL server auto adds blank but C-Sharp gets data code is not an error

Thanks, @Nick.McDermaid
suggest I create a primary key or unique index on the table to stop this from ever happening

Paul
  • 173
  • 1
  • 9