Valter Júnior
Valter Júnior

Reputation: 948

Read from table, infer counter and insert on database using entity framework 5 and mvc 4

I have this situation. I have a table called Request. This table has a column named Number. Every time I want to insert a new Request then I fetch the last request and based on its Request Number I infer a new one. For example, I have on my database Request1 with number 0001 then when I will insert a new one I fetch Request1 and based on its Number I infer my new Request Number which should be 0002.

The problem occurs when I want to create two Requests simultaneously. Process one fetch the Request1 and process two also gets Request1. When process one infers the new request number, which should be 0001, process two do the same. At the end I will create two new requests with the same Number.

Someone knows how to solve this?

Sorry about my english! ;)

Upvotes: 0

Views: 359

Answers (1)

There are several ways to solve your problem.

  • The field is always numeric, sequential, and each record the value must be higher than the last record?

If it is, you simply let the database do the work for you. Just set the field in question as the primary key of the table and how identity. In this case the database will provide the solution to internal competition, and of course to prevent ending up getting duplicate records. And the number is automatically filled by the DBMS (SQL Server, MySQL, Oracle ...)

If you are using EntityFramework and Code First, just go in the declaration of the entity (a C # class that defines the table in the database) and locate the field you need and add 2 attributes to him:

// Probably you have this kind of code (which fieldID should be the field name) 
 public Int32 fieldID {get; set;} 

// Just add these two attributes before the declaration of the field 
[Key] 
[DatabaseGenerated (DatabaseGeneratedOption.Identity)] 
public Int32 fieldID {get; set;} 

/* Example in C#, assuming you are using SQL Server and EFCF */
/* EF CF means Entity Framework Code First! */ 
  • You need to implement a logic to populate this field, which may not be as described above, or the field is not numeric or can not be done by the database and have to be direct by your application?

You can implement a lock on straightforward piece of code that can not have competition.

In the class that makes the manipulation of data and you are encountering the problem declare a variable of type Object, static and read-only like this:

private static readonly Object concurrencyLock = new Object ();

In the method that implements the logic that needs to be accessed uniquely enclose the code with a lock policy

lock(concurrencyLock) { 
    //Do stuff here without concurrency 
    /* When the current request into this part of the code, 
any other request will execute until the line above with "lock" statement. 
Until this current request exit this "lock" block of code, 
the other(s) will wait. As this one leave this block, one other join...
One at a time as you wish. */
}

There are others ways to do that too, but is a bit more complex or dosen't work well in a Web Application (like a Mutex, that would work fine if it's a Windows Service, ou Windows Form application)

Upvotes: 1

Related Questions