Autogenerate alphanumeric id in c#

Question

I have a code for autogenerated id. It is working, but my problem is the result. Numbers LDLB001 to LDLB009 is fine but when it reach numbers 10 onwards, the results are LDLB0010...LDLB0011 and so on. The result i want is LDLB010 but i can't do it. Here's the code. Hope you can help me.

    private void autogen()
    {
        try
        {
            conn.Open();

            String count = "SELECT count(*) + 1 as a FROM tbl_user";

            MySqlDataAdapter sda = new MySqlDataAdapter(count, conn);
            DataTable dt = new DataTable();
            sda.Fill(dt);

            String strgen = dt.Rows[0]["a"].ToString();

            MySqlCommand comm = new MySqlCommand("SELECT * FROM tbl_user WHERE userid = '" + "LDLB00" + strgen + "'", conn);
            MySqlDataReader reader;
            reader = comm.ExecuteReader();
            if (reader.HasRows)
            {
                reader.Read();
                strgen = (int.Parse(strgen) + 1).ToString();

            }
            conn.Close();
            lblUserID.Text = "LDLB00" + strgen;

        }
        catch (Exception et)
        {
            MessageBox.Show(et.Message);
        }
    }

Your current approach is open to a race condition. If they both query at the same time they'll get the same ID. _This is not good._ — mjwills, Jul 23 '18 at 13:06
What happens when two users running @ the same time get the same result for `count(*) + 1` ? — Alex K., Jul 23 '18 at 13:06
If the prefix is always the same, why not just use `auto_increment` and 'fake it' by adding the prefix to the front of the number? _That way the database will guarantee no duplicates._ — mjwills, Jul 23 '18 at 13:06
I will try that @mjwills thank you for answering. God bless you! — JP Bustillo, Jul 23 '18 at 13:08
When you fixed the identity to auto_increment look into [String.PadLeft](https://learn.microsoft.com/de-de/dotnet/api/system.string.padleft?view=netframework-4.7.1) — Filburt, Jul 23 '18 at 13:10
to add to @mjwills 's comment Use MySQL's `LPAD` ( https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_lpad ) function so you always generate a fixed length string. — Raymond Nijland, Jul 23 '18 at 13:10
@AlexK. thanks for noticing, my bad, it duplicates. I'll consider mjwills answer. Thanks guys. — JP Bustillo, Jul 23 '18 at 13:10
Possible duplicate of [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) — mjwills, Jul 23 '18 at 13:16

JP Bustillo · Answer 1 · 2018-07-28T03:32:02.443

I have modified my code thanks for the help of @Endi Zhupani's and @Azadee's answer. My userID now does not duplicate and it depends on my id which is a primary key and an autoincrement. Thank you all people!

    private void autogen()
    {
        try
        {
            conn.Open();

            String count = "SELECT auto_increment as a from information_schema.tables where table_schema = 'ldlb_lib' and table_name = 'tbl_user'";

            MySqlDataAdapter sda = new MySqlDataAdapter(count, conn);
            DataTable dt = new DataTable();
            sda.Fill(dt);

            String strgen = Convert.ToInt32(dt.Rows[0]["a"].ToString()).ToString("D3");

            MySqlCommand comm = new MySqlCommand("SELECT * FROM tbl_user WHERE userid = '" + "LDLB" + strgen + "'", conn);
            MySqlDataReader reader;
            reader = comm.ExecuteReader();
            if (reader.HasRows)
            {
                reader.Read();
                strgen = (int.Parse(strgen) + 1).ToString();

            }
            conn.Close();
            lblUID.Text = "LDLB" + strgen;
        }
        catch (Exception et)
        {
            MessageBox.Show(et.Message);
        }
    }

score -1 · Accepted Answer · answered Jul 23 '18 at 13:12

-1

Try changing this: String strgen = dt.Rows[0]["a"].ToString(); to String strgen = dt.Rows[0]["a"].ToString("D3");

This will make sure that you always get a three digit number and pad with leading 0s. So the number 1 would be 001, 10 would be 010 and 100 would be 100.

Also, don't forget to change "LDLB00" to "LDLB" on this row:

MySqlCommand comm = new MySqlCommand("SELECT * FROM tbl_user WHERE userid = '" + "LDLB00" + strgen + "'", conn);

Hope this helps!

answered Jul 23 '18 at 13:12

Endi Zhupani

736
2
7
22

This looks to be the harder version of `String.PadLeft` function? Like @Filburg suggested – Raymond Nijland Jul 23 '18 at 13:14
Will not work `String strgen = dt.Rows[0]["a"].ToString("D3");` will not compile because `Object.ToString()` method does not take a parameter. – Raymond Nijland Jul 23 '18 at 13:17
Ah you need to cast `dt.Rows[0]["a"]` to an integer first. So you can do `Convert.ToInt32(dt.Rows[0]["a"].ToString()).ToString("D3")`. The `D3` specifies that you want the length to be 3 and it will be padded with 0s. – Endi Zhupani Jul 23 '18 at 13:22
@RaymondNijland Yeah `String.PadLeft` seems like a much better option – Endi Zhupani Jul 23 '18 at 13:27

AzaDee · Answer 3 · 2018-07-24T13:42:32.013

As stated by @mjwills, you could possibly run into the scenario where 2 or more users perform the same query at the same time and end up with the same id. Two possible solutions are to either let the database create the id by making the table auto-increment the primary key:

CREATE TABLE tbl_user (
    `id` int NOT NULL AUTO_INCREMENT,
    userid varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
);

Then, prefix the id value with your string.

string sql = "SELECT `id` AS `a` FROM tbl_user WHERE (some condition)"
...
string userid = dt.Rows[0]["a"].ToString("LDLB000")

This will format the integer to always have LDLB and 0 to 3 leading zeros. For instance, if a = 33, strgen = "LDLB033". And, if a = 5000, strgen = "LDLB5000".

The other solution is to use Guid to create a unique identifier:

string userid = Guid.NewGuid().ToString("N");

Autogenerate alphanumeric id in c#

3 Answers3