c# simultaneous calls for sql db

Question

In my C# project I have a method that ask if an object exists in the db and if not then creates it. Now, if two users asks the same question simultaneously they both get null so the flow will be to save to db which is impossible for two duplicates to do that, so will raise an sql exception. How can I deal with this issue please?

here is my code:

var date = DateTime.UtcNow.Date;


var todayCelebPageView = _celebPageViewsRepo.GetAll().SingleOrDefault(d => d.iCelebId == celebId && d.dDate == date);

if (todayCelebPageView != null)
{
    todayCelebPageView.iScore++;

    _celebPageViewsRepo.Save();
}
else
{
    todayCelebPageView = new MovliCelebPageView() {dDate = date, iCelebId = celebId, iScore = 1};
    _celebPageViewsRepo.Add(todayCelebPageView);
    _movliRepository.DbContext.Entry(todayCelebPageView).State = System.Data.EntityState.Added;
    _celebPageViewsRepo.Save();
}

Answer 1

Theres no easy answer to this really, it's a common problem with a number of solutions.

Some options might be:

1. Catch the correct SQL exception, and re-try accordingly
2. Create a queue for those database calls, and handle them one at a time
3. Some implementation of locking, either in the database (perhaps by wrapping it in a transaction) or in the code itself.

Something else to consider is what should happen from a business point of view when two attempts are made to create a record at the same time.

Should the person who created the record last win? Should the first person win and the second receive an error? Or should you write the first record and update it again with the second?

The answer to this will depend entirely on the specifics of what you are trying to do in your application.

Answer 2

Move the logic of the check and create to the procedure level, then it will be handled with transaction isolation:

IF NOT EXISTS (SELECT 'non-empty' FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.TABLE_NAME') AND type in (N'U'))
    CREATE TABLE dbo.TABLE_NAME

But you still have to wrap your method and handle exception according the Number property of SqlException:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(queryString, connection);
    try
    {
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
    catch (SqlException ex)
    {
        for (int i = 0; i < ex.Errors.Count; i++)
        {
            errorMessages.Append("Index #" + i + "\n" +
                "Message: " + ex.Errors[i].Message + "\n" +
                "LineNumber: " + ex.Errors[i].LineNumber + "\n" +
                "Source: " + ex.Errors[i].Source + "\n" +
                "Procedure: " + ex.Errors[i].Procedure + "\n");
        }
        Console.WriteLine(errorMessages.ToString());
    }
}

System Error Messages
Cause and Resolution of Database Engine Errors

Answer 3

You should wrap the test for existence and the insert in a transaction. In that way the second call to check for existence will block while the first is completing.