Concurrency checks with Entity Framework and Stored Procedures

user_7592671 0
Viewed 295

I am using Entity Framework and manipulating data in a sqlserver database via stored procedures (per client request). Data is pulled from the database via stored procedures and the results of these stored procedures populates a SQLite db in the Winforms Application.

SQLite is used for additional querying and changing of data and then pushed back via update stored procedure to the sql server db when the user syncs

all stored procedures are on sql server (no in text / in line sql in the application)

I am faced with the scenario where multiple users can potentially attempt to update the same field, which poses 2 problems for me.

  1. If they call the same stored procedure at the same time (select or update). I am not sure what my options are here from a programming level, I don't have rights to make server changes.

  2. if the field they are trying to update has already been updated.

for problem 2 I am trying to build in a check by date stamping the modification. ie. when a user syncs sql server adds that sync date to a date modified column, if a another user tries to modify the same field i want to check the date modified on his sqlite db and compare that to date modified in sql server, if sql server's date modified is more recent, keep sql server values, if syncing user's modified date is more recent use his...

I have looked into Resolving optimistic concurrency with a condition where the client wins.

using (var context = new BloggingContext()) 
{ 
var blog = context.Blogs.Find(1); 
blog.Name = "The New ADO.NET Blog"; 

bool saveFailed; 
do 
{ 
    saveFailed = false; 
    try 
    { 
        context.SaveChanges(); 
    } 
    catch (DbUpdateConcurrencyException ex) 
    { 
        saveFailed = true; 

        // Update original values from the database 
        var entry = ex.Entries.Single(); 
        entry.OriginalValues.SetValues(entry.GetDatabaseValues()); 
    } 

} while (saveFailed);

}

but this seems to only work when you directly query the db with Entity Framework and not when you want to update via stored procedure.

what can I use to perform these types of checks?

entity-framework sqlite sql-server winforms c#
1 Answers

Ok, This is probably not the best solution, but it is what I was able to come up with, and although not tested extensively initial once over seems to be ok-ish.

I am not going to mark this as the answer, but its what i got working based on my question above.

  1. calling stored procedure at same time, created a class for the transactions

     public class TransactionUtils
{
public static TransactionScope CreateTransactionScope()
{

    var transactionOptions = new TransactionOptions();
    transactionOptions.IsolationLevel = IsolationLevel.ReadCommitted;
    transactionOptions.Timeout = TransactionManager.DefaultTimeout;
    return new TransactionScope(TransactionScopeOption.Required, transactionOptions);

}

    }

and then in code use it as follows:

var newTransactionScope = TransactionUtils.CreateTransactionScope();

           try
            {
                using (newTransactionScope)
                {
                    using (var dbContextTransaction = db_context.Database.BeginTransaction(/*System.Data.IsolationLevel.ReadCommitted*/))
                    {
                        try
                        {
                            db_context.Database.CommandTimeout = 3600;

                            db_context.Database.SqlQuery<UpdateData>("UpdateProc @Param1, @Param2, @Param3, @Param4, @Param5, @Param6, @DateModified",
                                new SqlParameter("Param1", test1),
                                new SqlParameter("Param2", test2),
                                new SqlParameter("Param3", test3),
                                new SqlParameter("Param4", test4),
                                new SqlParameter("Param6", test5),
                                new SqlParameter("DateModified", DateTime.Now)).ToList();

                            dbContextTransaction.Commit();
                        }
                        catch (TransactionAbortedException ex)
                        {
                            dbContextTransaction.Rollback();
                            throw;
                        }

As for issue 2 (concurrency) I could not find a way to use built in concurrency checks between data on SQL Server and the data that I want to update from SQLite (2 different contexts)

So I am storing Date modified in both sql server and sqlite. the sqlite date modified is updated when the user modifies a record, date modified on sql server is updated when a sync runs. Before syncing I query the sqlServer db for the record to be updated's date modified and compare it with the sqlite's date modified for that record in a if statement and then either run the update stored procedure for that record or not

user_75926710
Related