Geek asks and answers

Use a sequential field for each user in the entity structure

I have an object with a code field. For each user who uses this object, each object that the user inserts must have a code. Therefore, I wrote code that executes the following logic:
1. if the user set a code field, then use its code.
2. if the user has not set the code field, read the following serial code from db (starting with 1, by searching for the next serial code that does not yet exist) and use this code.

The problem is the following scenario:
Assuming that the user has the ability to add two objects with one click.
Assuming the following serial code should be "5".
In the first object, the user sets the code = 5, and in the second object, the user does not set the code.
Since I use the entity infrastructure, and at the end of the logic there is one commit / save of changes, I insert the first object (the one with code = 5) and for the second object that looks for db for the next next sequential code that does not exist yet. The following serial code, which does not yet exist in the database, is "5". So I set the second object with code "5".
In the end, I came up with two objects with code = 5, which is wrong.

I was thinking about ways to solve it.
One way is to make SaveChanges right after saving the first object, but it can make a lot of calls in db, and I'm not sure about this solution.
Another way is to search the database and related objects, but I really don't know how to do this.

Does anyone have a better idea?

+3
source share
1 answer

IDENTITY, SQL Server , , , , INSERT/UPDATE . , . , , , .

MyTable (ID int IDENTITY, FIRST_NAME varchar, LAST_NAME varchar). , sql, , , .

String sSQL = "";
String sFields = "";
String sValues = "";
String sCode = "";
// sCode = ... (get user entered code)
String sFirstName = "John";
String sLastName = "Doe";

if (sCode != "") 
{ //Add the user specified value and field for the identity column to the SQL
    sFields = "ID, ";
    sValues = sCode + ", ";
}
sFields += "FIRST_NAME, LAST_NAME";
sValues += "'" + sFirstName.Replace("'","''") + "', '" + sLastName.Replace("'","''") + "'";

sSQL = "INSERT INTO MyTable (" + sFields + ") VALUES (" + sValues + ")"
//execute the sql statement
0

More articles:


All Articles