Auto generation of Order id

Question

I wanted to have a unique transaction id for each order placed on my system and it only increments once. this is the code that i am using. help me out to have the incrementation fixed.

 string transactionCode;
        con = new SqlConnection(@"Data Source=LAPTOP-KA7UGSG3;Initial Catalog=imsysdb;Integrated Security=True");
        cmd = new SqlCommand("SELECT TransactionCode from tblOrders", con);
        con.Open();
        dr = cmd.ExecuteReader();

        if (dr.Read())
        {
            int code = int.Parse(dr[0].ToString()) + 1;
            transactionCode = code.ToString("000");

        }
        else if (Convert.IsDBNull(dr))
        {
            transactionCode = ("001");
        }
        else
        {
            transactionCode = ("001");
        }

        lblTransactionCode.Text = transactionCode.ToString();

OUTPUT

|transaction code|

|001| |002| |002| |002|

Does this answer your question? [Auto increment primary key in SQL](https://stackoverflow.com/questions/10991894/auto-increment-primary-key-in-sql-server-management-studio-2012) — Jeroen van Langen, Mar 28 '21 at 09:53
You should always do such things in the database to avoid race conditions(other threads or instances of your program or even a different application could create them at the same time). You can use for example a stored-procedure. You should derive the transaction code from a column which auto imcremts it's value. If you dont need to persist that value you can also do it in the `SELECT` query. — Tim Schmelter, Mar 28 '21 at 09:55

score 1 · Answer 1 · answered Mar 28 '21 at 10:19

It looks like your current code wants something that you can pull successive values from without inserting rows anywhere. You don't mention what database you are using, but in SQL Server this concept is a "sequence", see CREATE SEQUENCE

However, when talking about things like "orders", the more common approach is to make the Id column an "identity" (see CREATE TABLE / IDENTITY, so that the server generates the Id when you INSERT. You can read the newly generated value with any of (in preference order, earlier is better):

the OUTPUT clause on the INSERT
SCOPE_IDENTITY() (usually fine, but limited to one row)
@@IDENTITY (many problems; don't use unless the others aren't available on your server)

score 0 · Answer 2 · answered Mar 28 '21 at 10:10

I can see that your id generating is not related to SQL, you are trying to generate it in C# for some reason, so try replacing this :

if (dr.Read())
{
    int code = int.Parse(dr[0].ToString()) + 1;
    transactionCode = code.ToString("000");

}
else if (Convert.IsDBNull(dr))
{
    transactionCode = ("001");
}
else
{
    transactionCode = ("001");
}

With this: Edited - code updated

    int code; //OR int code = 0;
    if (dr.Read())
    {
        code = int.Parse(dr[0].ToString()) + 1;
        transactionCode = code.ToString("000");
    }
    else if (Convert.IsDBNull(dr))
    {
        transactionCode = ("001");
    }
    else
    {
        transactionCode = ("001");
    }

If it does not work, Edit your post and add some sample data, please.

AlanK · Answer 3 · 2021-03-28T10:39:26.367

0

I second the suggestions that you use a SQL identity column. With that said, assuming you don't control schema...

The logic of your attempt here is flawed in a couple of ways...
First I'm pretty sure you want to be querying MAX(TransactionCode)
Second, you can easily have two clients querying very close to each other, they both read the current maximum TransactionCode and both increment it to get the same new code.
Therefore you want to remove the increment from your C# code and do it in SQL script if at all possible with something like this (NB see @Marc Gravell's comment below)...

INSERT INTO tblOrders (TransactionCode, field1, field2...)
VALUES (CONVERT(VARCHAR(10), SELECT CONVERT(INT, MAX(TransactionCode)) + 1) FROM tblOrders, newfield1data, ...)

edited Mar 28 '21 at 10:39

answered Mar 28 '21 at 10:13

AlanK

1,827
13
16

Even when done at the database, the `MAX` approach is a race condition – Marc Gravell Mar 28 '21 at 10:20
Right you are indeed. What's the best answer that also avoids changing the database? – AlanK Mar 28 '21 at 10:37
1

the best answer, if given the direction "don't change the database", is to turn around to whoever gave that direction and say "no, you're wrong; to get the correct results, the only sensible approach is to change the database; every other approach is just leaving a landmine in the system". Never be afraid of challenging requirements and pushing back - that is literally the most valuable thing we can do, often. – Marc Gravell Mar 28 '21 at 11:39

Auto generation of Order id

|transaction code|

3 Answers3