Generate an unique ID

Question

I want to generate unique ID's for clients and store them in MySql database. It's a C# winform application, when one client enter his data like name etc. and when clicking on savebutton the ID generator needs to give him an unique ID which won't be repeated later.

I made it work, but after about 30 inserts, the generator drops a repeated number and the MySql gives this error: "Duplicate entry '76' for key 'PRIMARY'".

This is not a big error or crash, but can be annoying for long time use.

That's my code:

int minID = 1;
int maxID = 1000;
int resID;                 

Random CustomID = new Random();
resID = CustomID.Next(minID,maxID);

How can I check the saved ID's and clear the repeating first, then generate another number that's unique and not in the table yet?

MySql info (database table:ugyfelinfo, column: ID)

auto_increment works well, but with my method I was able to show to saved ID to the customer when he clicked save button. Like this:

MessageBox.Show("New Costumer  ID:   " + resID + "  -  Saved in Database!")

But, now because I have auto_increment in mysql, how can I display the delivered ID for user via MB?

The queries are like this:

 string constring = "datasource=localhost;port=3306;username=root;password=root";
 string Query = "insert into clients_db.ugyfelinfo (Ugyfel,... ) " +
                " values('" + this.UgyfelTextBox.Text + "','" ..."') ;";
 MySqlConnection conDataBase = new MySqlConnection(constring);
                MySqlCommand cmdDataBase = new MySqlCommand(Query, conDataBase); etc.

And I tried something like this this:

string idquery = "SELECT from clients_db.ugyfelinfo (LAST_INSERT_ID();) ";

MessageBox.Show("New user ID:  "+idquery+" saved in database!");

do not give the ID yourself, make it auto increment on the sql table. it will deal with it itself. if you insist on doing it yourself send a query to see if that ID exist before executing the insert command but it sounds wrong — koksalb, Oct 05 '17 at 10:19
That will not work as unique id, a guid would actually work but I suggest (like @koksalb did) to just have an auto incrementing id — EpicKip, Oct 05 '17 at 10:19
Either generate the ID in the database, or use a GUID if you have to create it in the client. — Dirk, Oct 05 '17 at 10:20
Use AUTO_INCREMENT, it will simply increment your id on each new entry, or simply use GUID as the column data type. — Konrad, Oct 05 '17 at 10:22
Thank you all for the fast response and useful information. The auto_increment got the job done and works well. I Edited the question above, can you please help me with this MessageBox saga? @i'myourhuckleberry — drunken-sailor, Oct 05 '17 at 11:17
@drunken-sailor I highly recommend you to use Entity Framework if you're dealing with databases in C# :) If you are familiar with ORM - Object Relational Mapping, it simply maps your tables to C# objects so you don't even have to deal with queries yourself. Otherwise you could just execute another query like `SELECT id FROM tablename WHERE ...` but if you are using MySQL you can also use `LAST_INSERT_ID()` see: https://dev.mysql.com/doc/refman/5.7/en/getting-unique-id.html — Konrad, Oct 05 '17 at 11:31
@i'myourhuckleberry I will absolutely learn the ORM and Entity Framework, but for now In that project I need to deal with that queries :( I tried to make it with new query, but It won't work, and the last_insert_id() too, I cant quite get it how they works in this MassageBox case. Can you write a full example for that another query please? What after "where section", and how Can I use, and in which syntax the "Last_insert"? I tried to understand the description in link you sent to me, but I cant get it right :( — drunken-sailor, Oct 05 '17 at 11:58
@drunken-sailor I can't help much because I don't see your code and how you deal with queries in C# — Konrad, Oct 05 '17 at 12:04
@i'myourhuckleberry Edited one more time, is it more helpful now? — drunken-sailor, Oct 05 '17 at 12:11
@drunken-sailor https://pastebin.com/pSHLpC5h that might work, untested. — Konrad, Oct 05 '17 at 12:15
@i'myourhuckleberry the error message is "no database selected".:( I edited the code above, to see how I tried to solve it, but the syntax was't right, can you see something? — drunken-sailor, Oct 05 '17 at 12:33
You have to create your database first, preferably through some kind of web panel like phpMyAdmin, then you can just append it to the connection string, like `"datasource=localhost;port=3306;username=root;password=root;database=mydatabase"` There's a lot about it on the internet. — Konrad, Oct 05 '17 at 12:36
@i'myourhuckleberry oh god, it works finally. I made a whole new conn string, and made some changes in commands, and it works nicely now. Thank you for the help, and for your time. All the best to you. :) — drunken-sailor, Oct 05 '17 at 12:45
@drunken-sailor glad to hear you got it sorted out, thank you and all the best to you too! — Konrad, Oct 05 '17 at 13:12

score 3 · Answer 1 · answered Oct 05 '17 at 10:24

Probably the best solution is to use Guid.NewGuid() and save it into CHAR(16) column (see this post). Chance of duplicate entry is very close to zero and even if you have a bad luck and Guid.NewGuid() generates duplicate, you can re-run your insert with new value.

If you don't need your identifier to be globally unique or you need integer key, you have to transfer responsibility for ID generating to the database. You just insert new row without the id value and you query for the result. This post migh help you.

score 1 · Answer 2 · answered Oct 05 '17 at 10:22

1

If you have single database then maybe use database generate unique id is better.

The code you showing there is no warranty it wont get duplicated.

If you want client side unique code without checking the database have a look at using GUID.

answered Oct 05 '17 at 10:22

duongthaiha

855
6
17

Generate an unique ID

2 Answers2