A unique row for each entry in the database table

Question

In my Asp.Net MVC 5 project, I first use Entity Framework code to work with an MS SQL database. Suppose this is a table:

public class Ticket { [Key] public int Id { get; set; } [Required] public string ReferenceCode { get; set; } //Rest of the table } 

In this table, whenever I add new code, I want the ReferenceCode column to be a unique and random AlphaNumeric letter (containing only letters and numbers) with a specific length. This will allow users to refer to a specific ticket, for example.

Here are some examples with a length of 10 characters: TK254X26W1 , W2S564Z111 , 1STT135PA5 ...

Now I can generate random strings with a given length. However, I am not sure how to guarantee their uniqueness. I do it like this:

 db.Tickets.Add(new Ticket() { ReferenceCode = GenerateRandomCode(10), //... }); 

To be precise, I want the GenerateRandomCode function or perhaps another method to make sure that the generated string was not used for another record.

I can use a for loop to test each generated code, but I don't think this is a good idea. Especially after a while, when the table will have thousands of records.

Answer 1

Here is my approach, which guarantees uniqueness and introduces some randomness.

• Use a sequence generator that is guaranteed to give a unique number . Since you are working with SQL Server, this may be the value of the IDENTITY column. Otherwise, you can increase the application level value in C # code to achieve this.
• Generate a random integer to lead to some randomness in the result. This can be done using Random.Next() and any seed, even the number generated in the previous step.
• Use the EncodeInt32AsString method to convert integers from the previous two steps to two lines (one is a unique string, one is a random string). The method returns a string consisting of only allowed characters specified in the method. The logic of this method is similar to how a number is converted between different bases (for example, changing a valid string to only 0-9 or only 0-9A-F to get a decimal / sixth representation). Therefore, the result is a "number" consisting of "numbers" in the allowedList .
• Combine the returned rows . Keep the entire unique string as it is (to guarantee uniqueness) and add as many characters from the random string to fill the total length to the desired length. If necessary, this concatenation can be a fantasy by entering characters from a random string at random points into a unique string.

Keeping the entire unique string, this guarantees the uniqueness of the final result. Using a random string, this introduces randomness. Accident cannot be guaranteed if the length of the target string is very close to the length of the unique string.

In my testing, calling EncodeInt32AsString for Int32.MaxValue returns a unique string with a length of 6 characters:

2147483647: ZIK0ZJ

Based on this, the length of the target line 12 will be ideal, although 10 is also reasonable.

EncodeInt32AsString Method

  /// <summary> /// Encodes the 'input' parameter into a string of characters defined by the allowed list (0-9, AZ) /// </summary> /// <param name="input">Integer that is to be encoded as a string</param> /// <param name="maxLength">If zero, the string is returned as-is. If non-zero, the string is truncated to this length</param> /// <returns></returns> static String EncodeInt32AsString(Int32 input, Int32 maxLength = 0) { // List of characters allowed in the target string Char[] allowedList = new Char[] { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z' }; Int32 allowedSize = allowedList.Length; StringBuilder result = new StringBuilder(input.ToString().Length); Int32 moduloResult; while (input > 0) { moduloResult = input % allowedSize; input /= allowedSize; result.Insert(0, allowedList[moduloResult]); } if (maxLength > result.Length) { result.Insert(0, new String(allowedList[0], maxLength - result.Length)); } if (maxLength > 0) return result.ToString().Substring(0, maxLength); else return result.ToString(); } 

GetRandomizedString Method

Now the previous method just takes care of the string encoding. To achieve the properties of uniqueness and randomness, you can use the following logic (or similar).

In the comments, Kevin pointed out the following risk with the implementation of the EncodeInt32AsString method:

The code must be modified so that it returns a string with a fixed length. Otherwise, you can never guarantee that the end result is unique. If this helps, draw one value generating ABCDE (Unique) + F8CV1 (Random) ... and then later, another value generating ABCDEF (unique) + 8CV1 (Random). Both values: ABCDEF8CV1

This is a very valid point, and it was covered in the next GetRandomizedString method, specifying the lengths for the unique and random strings. The EncodeInt32AsString method EncodeInt32AsString also been modified to subtract the return value by the specified length.

  // Returns a string that is the encoded representation of the input number, and a random value static String GetRandomizedString(Int32 input) { Int32 uniqueLength = 6; // Length of the unique string (based on the input) Int32 randomLength = 4; // Length of the random string (based on the RNG) String uniqueString; String randomString; StringBuilder resultString = new StringBuilder(uniqueLength + randomLength); // This might not be the best way of seeding the RNG, so feel free to replace it with better alternatives. // Here, the seed is based on the ratio of the current time and the input number. The ratio is flipped // around (ie it is either M/N or N/M) to ensure an integer is returned. // Casting an expression with Ticks (Long) to Int32 results in truncation, which is fine since this is // only a seed for an RNG Random randomizer = new Random( (Int32)( DateTime.Now.Ticks + (DateTime.Now.Ticks > input ? DateTime.Now.Ticks / (input + 1) : input / DateTime.Now.Ticks) ) ); // Get a random number and encode it as a string, limit its length to 'randomLength' randomString = EncodeInt32AsString(randomizer.Next(1, Int32.MaxValue), randomLength); // Encode the input number and limit its length to 'uniqueLength' uniqueString = EncodeInt32AsString(input, uniqueLength); // For debugging/display purposes alone: show the 2 constituent parts resultString.AppendFormat("{0}\t {1}\t ", uniqueString, randomString); // Take successive characters from the unique and random strings and // alternate them in the output for (Int32 i = 0; i < Math.Min(uniqueLength, randomLength); i++) { resultString.AppendFormat("{0}{1}", uniqueString[i], randomString[i]); } resultString.Append((uniqueLength < randomLength ? randomString : uniqueString).Substring(Math.Min(uniqueLength, randomLength))); return resultString.ToString(); } 

Output result

Calling the above method for a set of input values ​​results in:

  Input Int Unique String Random String Combined String ------------ ----------------- -------------- --------------------- -10 000000 CRJM 0C0R0J0M00 0 000000 33VT 03030V0T00 1 000001 DEQK 0D0E0Q0K01 2147 0001NN 6IU8 060I0U18NN 21474 000GKI VNOA 0V0N0OGAKI 214748 004LP8 REVP 0R0E4VLPP8 2147483 01A10B RPUM 0R1PAU1M0B 21474836 0CSA38 RNL5 0RCNSLA538 214748364 3JUSWC EP3U 3EJPU3SUWC 2147483647 ZIK0ZJ BM2X ZBIMK20XZJ 1 000001 QTAF 0Q0T0A0F01 2 000002 GTDT 0G0T0D0T02 3 000003 YMEA 0Y0M0E0A03 4 000004 P2EK 0P020E0K04 5 000005 17CT 01070C0T05 6 000006 WH12 0W0H010206 7 000007 SHP0 0S0H0P0007 8 000008 DDNM 0D0D0N0M08 9 000009 192O 0109020O09 10 00000A KOLD 0K0O0L0D0A 11 00000B YUIN 0Y0U0I0N0B 12 00000C D8IO 0D080I0O0C 13 00000D KGB7 0K0G0B070D 14 00000E HROI 0H0R0O0I0E 15 00000F AGBT 0A0G0B0T0F 

As you can see above, a unique string is predictable for consecutive numbers, because it represents the same number represented in another database. However, a random string introduces some entropy so that users do not guess subsequent numbers. Moreover, alternating the “numbers” of a unique string and a random string, it becomes a bit more difficult for users to observe any pattern.

In the above example, the length of the unique string is set to 6 (since this allows it to represent Int32.MaxValue ), but the length of the random string is 4, because the OP requires a total length of 10 characters.

Answer 2

You can use Guid to generate unique keys (but not so random when it comes to security).

Drawing from this question SO :

 Guid g = Guid.NewGuid(); string GuidString = Convert.ToBase64String(g.ToByteArray()); GuidString = GuidString.Replace("=",""); GuidString = GuidString.Replace("+",""); GuidString = GuidString.ToUpper(); 

will create a unique key that matches your ReferenceCode property needs, but longer (22 characters). Collapsing and using X characters no longer guarantee its uniqueness.

OZVV5TPP4U6XJTHACORZEQ

Answer 3

Do not read the solution without a path? You have two needs that I see:

• Randomness

  . You cannot have a “deterministic” function, because if someone can guess the algorithm, they can find all the elses ticket numbers.

• uniqueness. You cannot have any duplicate ticket numbers, which makes Random difficult (you'll have to consider conflicts and try again.)

But there is no reason why you cannot do it - you have a lot of bit space with 36 ^ 10. You can allocate 4 bytes for uniqueness and 6 bytes for randomness. Here is a sample code:

 public partial class Form1 : Form { private static Random random = new Random(); private static int largeCoprimeNumber = 502277; private static int largestPossibleValue = 1679616; // 36 ^ 4 private static char[] Base36Alphabet = new char[] { '0','1','2','3','4','5','6','7','8','9', 'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z' }; public static string GetTicket(int id) { int adjustedID = id * largeCoprimeNumber % largestPossibleValue; string ticket = IntToString(adjustedID); while (ticket.Length < 4) ticket = "0" + ticket; return ticket + new string(Enumerable.Repeat(Base36Alphabet, 6) .Select(s => s[random.Next(s.Length)]).ToArray()); } private static string IntToString(int value) { string result = string.Empty; int targetBase = Base36Alphabet.Length; do { result = Base36Alphabet[value % targetBase] + result; value = value / targetBase; } while (value > 0); return result; } 

Summary of what the code does. You pass in your identifier int, which is then hashed in such a way that it looks random, but is guaranteed to never repeat the number for the first 1.68 million entries.

Then it takes this hashed int value and turns it into a 4-digit code; it’s “part of uniqueness” - you are guaranteed another four-digit code at the beginning of the first 1.68 million identifiers (reciprocal magic).

Six more characters remain. Just fill them with random characters, which makes the entire 10-digit code terribly hard to guess.

This solves both problems. It is guaranteed to be unique to the first million + records. And this is not really “guessed” by the client, since even if they guessed the algorithm, they would have 2 billion different possibilities for any identifier that they wanted to crack.

Answer 4

You can achieve absolute uniqueness on a machine using the UuidCreateSequential method (which deals with Uuid) in rpcrt4.dll , as shown below. Check the link from microsoft to make sure it is unique. You will never get the same ID twice on your computer or on the host where you upload your site.

The output format from the following code is what Asp.Net MVC uses to create a unique identifier for the AspNetUsers table:

 using System; using System.Runtime.InteropServices; public class SqlGuidUtil { [DllImport("rpcrt4.dll", SetLastError = true)] static extern int UuidCreateSequential(out Guid guid); public static Guid NewSequentialId() { Guid guid; UuidCreateSequential(out guid); var s = guid.ToByteArray(); var t = new byte[16]; t[3] = s[0]; t[2] = s[1]; t[1] = s[2]; t[0] = s[3]; t[5] = s[4]; t[4] = s[5]; t[7] = s[6]; t[6] = s[7]; t[8] = s[8]; t[9] = s[9]; t[10] = s[10]; t[11] = s[11]; t[12] = s[12]; t[13] = s[13]; t[14] = s[14]; t[15] = s[15]; return new Guid(t); } } 

Using:

 Guid gid = SqlGuidUtil.NewSequentialId(); String sid = SqlGuidUtil.NewSequentialId().ToString(); 

Output Example:

637E3E78-23F5-E611-8278-506313F91120

This format is exactly the same as the AspNet Identity user identifier format.

You can also remove the dash (not a good idea), as shown below:

 String sid = SqlGuidUtil.NewSequentialId().ToString().Replace("-",""); 

Answer 5

Put a unique index in the database column and continue to generate until the database accepts it without a unique constraint. Collisions will be very rare.

Answer 6

We needed to implement something similar for another purpose in our previous projects. We just prepared some unique identifiers in a new table (let me call it table A), and then when we want to insert a new record into table B, we just added the first record from table A to the trigger.

Answer 7

enter your code, for example

 string referenceCode=Guid.NewGuid().ToString(); referenceCode=referenceCode.Replace('-', ''); db.Tickets.Add(new Ticket() { ReferenceCode = referenceCode; //... }); 

Answer 8

Try it. It works for me

 var buffer = new byte[5]; new Random().NextBytes(buffer); Console.WriteLine(string.Join("", buffer.Select(b => b.ToString("X2")))); 

Answer 9

Using your identifier to provide uniqueness and the System.Random class to get random, you can expect something like:

 private string GenerateRandomCode(int Key) { Random rnd = new Random(Key); char[] values = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789".ToArray(); string result = string.Empty; for(int i = 0; i < 10; i++) { result += values[rnd.Next(0, values.Length)]; } return result; } 

The key value will provide the same generated code, and the random class will not have enough time to worry about unity.

Answer 10

Try the following:

 Guid.NewGuid().ToString("N").Substring(0, 10) 

I used this by creating random rows for IDs in SQL tables from C # codebase. It depends on the randomness of C # Guid and every time you get a new alphanumeric string.

Answer 11

Using Guid I created a function to create a unique string. Of course, GUIDs may collide, so I am changing the line in the middle with the new Guid.

  static string GenerateRandomCode(){ string guid = Guid.NewGuid().ToString("N"); List<char> lst = new List<char>(); int count = 1; foreach(char c in guid){ if(count==11) break; if(count % 2 ==0){ lst.Add(Guid.NewGuid().ToString().ToCharArray()[1]); } else{ lst.Add(c); } count++; } return string.Join("",lst.ToArray()); } 

Answer 12

Maybe this will help you

DECLARE @userReportId BIGINT

SET @userReportId = FLOOR (RAND () * (10000000000000-1) + 1);